Hello all, Thanks for your help yesterday, I learned about the MERGE statement and was able to implement it.
I'm just wondering which way is faster (this is for a table with ~150k rows and 60 columns) - I'm running SQL SERVER 2008 R2. [EDIT: Also thanks for the suggestion on ignoring historical data, with the "growing" table, and the criteria of > 120,000 ID I'm able to severely reduce the processing time this way]
A. Update all rows in target table where this is a match. (so don't look at the other 59 columns, just overwrite)
B. Put in a WHERE statement like this Col1 <> Col1, Col2 <> Col2 so that it would compare all of the rows and not update if there were no changes.
I have thought which way would be faster but can't come to a conclusion as I'm not really sure how this all works behind the scenes, any input would be appreciated. Thanks very much!
The <> can lead to scans, so that might be problematic.
However, the best answer, test. Try it out. Check the execution plan see if you're getting good clean index seeks or if you're seeing scans. Updating everything is more likely to lead to a scan too, so, testing is the key.
answered Nov 18 '11 at 11:36 AM
Grant Fritchey ♦♦