On SQL Server I want to update a 0/1 interger value for a specific set of records. For some reason this takes very long (let it run for 2 hours, still not finished). I can't figure out what is wrong or what to tweak.
The query:
update workorder set INCTASKSINSCHED=0 where siteid = 'RAILINFRA' AND HISTORYFLAG=0 AND ISTASK=0;
There are 282320 workorder records, only 103565 are for the RAILINFRA site. So it should be very, very fast.
In SQL Management studio I have this as an estimated execution plan. You can see a huge cost at the workorder_ndx1 index. I'm not sure why that is…
I tried to count the number of records, this works very fast:
SELECT COUNT(*) FROM workorder WHERE siteid ='RAILINFRA' AND HISTORYFLAG=0 AND ISTASK=0;
1 row: 5430
Execution time 0sec
The select statement took just under 1 sec, and has a different execution plan:
SELECT * FROM workorder WHERE siteid ='RAILINFRA' AND HISTORYFLAG=0 AND ISTASK=0;
Gives the 5430 rows
This is the information of the indices used:
workorder_ndx1 (clustered)
- SITEID
- WONUM
workorder_ndx24 (non-clustered)
- HISTORYFLAG
- ISTASK
- WONUM
There is only one index with the to be updated field INCTASKINSCHED
workorder_ndx38 (non-clustered)
- WONUM
- INCTASKINSCHED
- SITEID
I do have the XML's of the executionplans (slqplan-files), let me know if you need more information from them.
I suspect there is somekind of a loop that it uses an index that needs updating, but not sure why that should be the workorder_ndx1 index, because it doesn't include the INCTASKINSCHED field.
Best Answer
It turns out there was a trigger on the samen table that also updates it, that forces an update for the index used. It works near instant with the trigger disabled.