Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nickfinity
Yak Posting Veteran
55 Posts |
 Posted - 2004-05-21 : 17:47:13
|
| Hello,I've used ltrim and rtrim a million times and never had a problem until now. I've got a table with roughly 4500 records in it. One of the columns is an nvarchar (255). I've let this query run for 5 minutes and it's still not finished.update table1 set col1=ltrim(rtrim(col1))The strange part is that if I break it up into two separate queries it works. For instance I can runupdate table1 set col1=ltrim(rtrim(col1)) where uid>2900update table1 set col1=ltrim(rtrim(col1)) where uid<=2900Anyone have any ideas? This table is only temporary so I don't have any indexes (other than the primary key which is uid). I ran the same query on another table in the database that has 45,000 records and it ran fine.Thanks for any help.Thanks,Nick |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-21 : 17:50:28
|
| ?? Have you ran update statistics on the table?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
nickfinity
Yak Posting Veteran
55 Posts |
Posted - 2004-05-21 : 17:57:40
|
| I just tried it, but it's still not running like normal. Maybe I'll wipe the table out and start over. The data is being imported from Excel. Could there be something coming in that is causing a problem? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-21 : 18:02:05
|
| Why don't you transform the data on import?Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-21 : 18:04:52
|
| Also, you might want to see if you are being blocked by running sp_who2. Blocking could seriously impact the query performance. Your query probably needs to get an exclusive lock on the table. Your other two queries wouldn't.Tara |
|
|
|
nickfinity
Yak Posting Veteran
55 Posts |
Posted - 2004-05-24 : 11:53:01
|
| Thanks for your help Tara. I ended up shutting down and restarting SQL Server and it ran fine after that. The next time this needs to be run I'll see if I get the same behavior. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-05-24 : 13:16:12
|
| I like blocking for the problem....Temp table, 4500 rows....stats won't do jack...It's gonna scan anyway...What's the process that you get the data in?And I don't like transforming data on the way in...too slow....did you bcp in? DTS in?From a sproc? xp_cmdshell?Brett8-) |
|
|
|
nickfinity
Yak Posting Veteran
55 Posts |
Posted - 2004-05-24 : 13:45:51
|
| I used DTS to bring it in. This whole situation is a little different. I'm not the one who is going to be importing these spreadsheets in every 3 or 4 months. We created a database and application for a company and they (supposedly) are taking care of the maintenance. The guy I deal with is not a techie, but he messes around with the database. I figure he can handle DTS because I think he's used it before, but I don't want to make it very difficult for him. I ended up taking out those update statements and doing things a little differently (hopefully better). I think it will work now. I'm just hoping he doesn't have any database trouble because I don't really feel like dealing with it. |
|
|
|
|
|
|
ltrim and rtrim really slow