| Author |
Topic |
|
bcanonica
Starting Member
35 Posts |
 Posted - 2009-12-16 : 15:25:18
|
I have an update statement that is running to update about 65000 records and it just keeps running without completing. Any ideas?update address set ImportID = null where ImportID is not null |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-12-16 : 15:29:50
|
Why do you need the 'WHERE' clause if you just want to update everything to nulls..Justupdate address set ImportID = null should do... no? |
 |
|
|
bcanonica
Starting Member
35 Posts |
Posted - 2009-12-16 : 15:38:19
|
| Tried that still seems to just keep running with no end. Anything other ideas? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
bcanonica
Starting Member
35 Posts |
Posted - 2009-12-16 : 15:47:23
|
| Used sp_who2 killed all processes that might be blocking me. Could it be an index issue. I seem to rememeber reading back that indexes could hurt update and insert performance. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
bcanonica
Starting Member
35 Posts |
Posted - 2009-12-16 : 15:56:36
|
| I have 5 indexes on the table. This a very large table about 1.3 million records. I have two non-clustered/non-unique indexes on the AddressID and ImportID together. Could that be a problem? Looks like two of the same indexes. Wierd thing is the message has a popup right away saying the 65000 rows have been effected, but then the query just keeps running. Any ideas why that is? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-12-16 : 16:03:11
|
| Can you also check if there are there any triggers on this table too? |
|
|
|
bcanonica
Starting Member
35 Posts |
Posted - 2009-12-16 : 16:18:44
|
| Disabled the indexes still nothing. Sorry meant 1.3 million rows is large in relation to this database. I am going to try restarting sql server and maybe the query will magically work again. |
|
|
|
bcanonica
Starting Member
35 Posts |
Posted - 2009-12-16 : 16:30:51
|
| Great call vijay one of the triggers was causing the issue. Much thanks to both you and Tara for all your help with this issue. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-12-16 : 16:35:37
|
| Np..You're welcome. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-12-16 : 16:52:57
|
Thanks Tara...Just one of those days I guess... |
|
|
|
|
Update statment fails