July 4, 2008 at 4:18 am
I would have expected that there would be two basic factors affecting the performance of an UPDATE command: The complexity of the criteria and the size of the resultant set that needs to be updated.
I need to update a specific field in our stock table to be either blank or 'N' if the item comes from one of the specified suppliers. I do this overnight when no users are on the system. I took what I thought was the simplistic approach of breaking this into two steps. Firstly I set all records to blank, and then I set the specified records to 'N'. The table contains roughly 600,000 records of which around 120,000 will end up with a value of 'N'. Our system is SQL2000 Enterprise.
The first update is:
update scheme.stockm
set packaging = ''
The second update is:
update scheme.stockm
set packaging ='N'
where warehouse in ('02', '03', '04')
and supplier in ('C0001', 'B0010', 'P0001', 'P0002', 'P0013', 'I0005', 'U0001')
As the first update affects 5 times the number of records, one could perhaps expect that to take 5 times as long, but I would have thought that the added complexity of the second update would have led to a significant overhead itself thus reducing the time differential of the 2 updates.
I am quite surprised to discover that on average the second update only takes around 10 to 15 seconds to run, but the first update is averaging between 30 and 40 minutes.
Is my belief that 'simple' usually leads to a more efficient execution path not correct? Should I add to the complexity of the first update in order to improve it's performance, e.g. 'where supplier NOT in .....', or am I overlooking some significant point?
July 4, 2008 at 4:40 am
If your update query decides to scan the whole table, then it's not going to take any longer with or without the complex criteria.
The difference in the time is due to the sheer number of records being updated... each change needs to be written to the database (ignore caching for the purpose of this argument) and the transaction log, so the more you change, the longer it will take.
If I were you, I would do the update in a single hit (using a case statement).
Alternatively, add the criteria WHERE packaging <> '' to the first query (to avoid changing rows that are already blank), and similar criteria to the second update to avoid changing those that are already set to 'N'.
July 4, 2008 at 12:27 pm
Hi
update scheme.stockm
set packaging = ''
In your first query inatead of updating, rename this packaging to DUMMY and just add one column and rename this column to packaging . This is simple to do it.
Thanks -- Vj
July 4, 2008 at 1:00 pm
Please verify the triggers on the table.
If there is, please write disable all triggers statement before update query and enable after update query.
Any how on bulk update, triggers are not working properly in sql server 2000.
If you want do trigger functionality also , please add that statements after update statement
July 7, 2008 at 1:20 am
Can you clarify in what way you believe triggers are not working properly in SQl 2K? A correctly written trigger will handle multi-row updates quite happily, but triggers are sometimes not written to correctly handle this.
I suspect the original problem is simply the number of rows being updated by the two statements is not the same. Some stats (set statistics io on etc) and execution plans , plus how many rows affected by each query would help, as would details of indexes etc on the table involved.
Mike John
July 22, 2008 at 5:52 am
ALL: Apologies for the delay in replying but I have been away.
Ian, adding a WHERE clause does indeed dramatically reduce the time taken (after the first time it has been run) and has therefore achieved the effect that I required of reducing the time to under a minute in total. It does not, however, satisfy my curiosity as to the disproportionate time differences.
VJ, I cannot mess with the table structure as this is a part of an integrated ERP system and the software might get a bit upset with me!
Srinivasa, there is indeed a trigger on this table, but I have never experienced such poor performance previously when doing large updates. Thankyou for the prompt to look more closely at the trigger though, and I have now added an IF COLUMNS to the beginning of it which should hopefully cause a minor improvement to the system generally.
I have also had my doubts about the reliability of triggers, particularly the DELETE trigger, but have no evidence.
Mike, as I stated originally, the first command was taking around 30 to 40 minutes to update around 600,000 records. The second command took a mere 10 to 15 seconds to update 120,000 records. To my way of thinking this is disproportionate and inexplicable bearing in mind the simplicity of the first command in comparison to the second.
Many thanks to all for your contributions and wisdoms.
Phil
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply