October 7, 2010 at 6:23 am
Hi
I have an SP that works on a few thousand rows at a time, and as part of its work, does multiple inserts and updates.
The Merge statement would seem useful here as an 'upsert' tool, but does it perform better, worse or same as the old 'if exists update else insert' approach?
I've googled this extensively and found very conflicting answers.
Thoughts?
Brian
October 7, 2010 at 6:40 am
I have found from experience that Merge performs better than doing the steps individually,
from BOL
By using the MERGE statement, you can replace the individual DML statements with a single statement. This can improve query performance because the operations are performed within a single statement, therefore, minimizing the number of times the data in the source and target tables are processed. However, performance gains depend on having correct indexes, joins, and other considerations in place
But i guees the best answer would be to try both ways and test
October 7, 2010 at 6:41 am
Brian McGee-355525 (10/7/2010)
HiI have an SP that works on a few thousand rows at a time, and as part of its work, does multiple inserts and updates.
The Merge statement would seem useful here as an 'upsert' tool, but does it perform better, worse or same as the old 'if exists update else insert' approach?
I've googled this extensively and found very conflicting answers.
Thoughts?
Brian
There's conflicting answers because there are so many factors involved, namely if it can make good use of indexes or not, and the size of the upsert, just to name a few.
In our use case, we saw some good performance increase from MERGE, but my advice is to write it both ways and see what performs best for you in your use case.
October 7, 2010 at 7:01 am
Brian McGee-355525 (10/7/2010)
I have an SP that works on a few thousand rows at a time, and as part of its work, does multiple inserts and updates.The Merge statement would seem useful here as an 'upsert' tool, but does it perform better, worse or same as the old 'if exists update else insert' approach?
I've googled this extensively and found very conflicting answers.
I agree with previous posts.
merge statement is supposed to do the very same old logic on the background therefore it is supposed to perform better.
Having said that, in my experience - depending on particular scenarios - I've seen merge performing both better and worse than manual approach.
My suggestion is to code a merge version of the code and test it in production or near-production conditions.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply