x

Merge Statement Performance

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!

Sheep

more ▼

asked Nov 18, 2011 at 07:06 AM in Default

TelepathicSheep2 gravatar image

TelepathicSheep2
140 14 15 17

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

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.
more ▼

answered Nov 18, 2011 at 11:36 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
100k 19 21 74

Thanks - I'll try it out.
Nov 18, 2011 at 11:51 AM TelepathicSheep2
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x249
x48

asked: Nov 18, 2011 at 07:06 AM

Seen: 984 times

Last Updated: Nov 18, 2011 at 07:11 AM