| Author |
Topic |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2005-10-03 : 10:41:10
|
| I a program, one table need to be updated. There are two ways to do it, which way is fast? (about 20 reocrds)1) update Order set qty = qty +1 where OrderID = 123 update Order set qty = qty +1 where OrderID = 222 ... update Order set qty = qty +1 where OrderID = 201 (repeat 20 times with different OrderID)2) update Order set qty = qty +1 where OrderID in (123, 222, ...201) |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-10-03 : 10:44:29
|
2.Go with the flow & have fun! Else fight the flow  |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-03 : 10:44:49
|
| I think second is the best methodIt is simplification of first methodMadhivananFailing to plan is Planning to fail |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2005-10-03 : 10:53:26
|
| For 20+ rows first one is probably faster, assuming you have clustered pk on orderID. All updates will use clustered index seek. Solution #2 will most probably scan entire clustered index. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-10-03 : 11:30:28
|
somehow i'd have to disagree...it seems illogical to me that say 100 update statements are faster than one...where's kristen with his test?  Go with the flow & have fun! Else fight the flow |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2005-10-03 : 11:50:01
|
| Well, I just tried it here with a (4.5M row) table and a list of 39 clustered index values spread throughout the index. It generated a query plan that used a Clustered Index Seek with a SEEK:() predicate that has ORs in it. The values in the IN clause were reordered into index order in the SEEK:() predicate and the Seek operation had ORDERED FORWARD on it.Judging from the IO stats and execution time, it certainly looks like it only hit the pages that contain the keys in question. |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2005-10-03 : 11:53:12
|
quote:
somehow i'd have to disagree...it seems illogical to me that say 100 update statements are faster than one...
These are not the same update statements. Update with in (<list with 100 items>) is going to scan clustered index/table. The bigger is the table, the slower is update (even if we forget locking at the moment). |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2005-10-03 : 11:58:34
|
quote:
Originally posted by mmarovicUpdate with in (<list with 100 items>) is going to scan clustered index/table.
Incorrect. |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2005-10-03 : 12:01:09
|
| Interesting! That's great improvement over what it did last time I tested it. I wonder how long is this algorithm available. I believe it didn't work that way on mssql server 7.0. |
|
|
|
|
Which way is better? One by one or use "in.."?