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.
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:
There is only one index with the to be updated field INCTASKINSCHED
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.