| Author |
Topic |
|
urbanmojo
Starting Member
5 Posts |
 Posted - 2011-07-13 : 22:14:26
|
| I have an update that has to update a dimensional table that only has about 5000 rows, like this:UPDATE dim_agent SET latest_flag = CASE WHEN (effective_to_date >= '2200-01-01 00:00:00' OR effective_to_date IS NULL) THEN 1 ELSE 0 END;in the last day it when from running instantly to running in 20 minutes. I had (perhaps foolishly) put a non clustered index on a non-primary key field, it seemed to slow down after that. I have removed the index and the query is still running slowly. I know the index will slow things down but that was extreme, and I'm still having the problem after dropping the index.How to fix? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-07-13 : 23:45:04
|
Rewrite the query into three parts?UPDATE dim_agent SET latest_flag = 0;UPDATE dim_agent SET latest_flag = 1 where effective_to_date >= '2200-01-01 00:00:00';UPDATE dim_agent SET latest_flag = 1 where effective_to_date IS NULL;
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
urbanmojo
Starting Member
5 Posts |
Posted - 2011-07-14 : 03:30:34
|
quote:
Originally posted by SwePeso
Rewrite the query into three parts?UPDATE dim_agent SET latest_flag = 0;UPDATE dim_agent SET latest_flag = 1 where effective_to_date >= '2200-01-01 00:00:00';UPDATE dim_agent SET latest_flag = 1 where effective_to_date IS NULL;
N 56°04'39.26"E 12°55'05.63"
I thought of that, but given it is taking 20 minutes or more to do a basic update on 5000 rows (and was working fine before) that tells me something more serious is going on. The UPDATE I wrote should work fine. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-07-14 : 03:34:49
|
Are you updating a key column?Do you have an update trigger on the table?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-07-14 : 03:41:50
|
how long does your original update query takes ?What else did you change ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
urbanmojo
Starting Member
5 Posts |
Posted - 2011-07-14 : 05:31:23
|
quote:
Originally posted by SwePeso
Are you updating a key column?Do you have an update trigger on the table?
N 56°04'39.26"E 12°55'05.63"
No it is not a key column and there is no trigger. Just did a DBCC reindex and updated statistics and it is still taking a very long time |
|
|
|
urbanmojo
Starting Member
5 Posts |
Posted - 2011-07-14 : 05:32:33
|
quote:
Originally posted by khtan
how long does your original update query takes ?What else did you change ?
KH[spoiler]Time is always against us[/spoiler]
Originally it took one second, now taking over 20 minutes. Not sure what else was changed, nothing I can think of. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-07-14 : 07:09:47
|
Can you post the full UPDATE statement?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
skybvi
Posting Yak Master
193 Posts |
Posted - 2011-07-22 : 15:40:14
|
| Did you check if htere is blocking occuringMaybe some other programs is using the data at tht time.Regards,SushantDBAWest Indies |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2011-07-28 : 06:28:30
|
| Go and find the backup you took of the database before you made the changes to it, restore it on a server with similar hardware (or the same sever if it will fit). Re-run the update and get the actual execution plan. Compare that to the execution plan of the update statement that's running slowly.Of course, if you didn't take the above mentioned backup, then you've just seen a good example of why a backup before any change is a good idea.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
|
UPDATE running very very slowly