November 9, 2009 at 2:26 pm
Hi!
I'm just doing some tests for client solutions using the MERGE statement to combine CRUD operations. BOL says MERGE always performs a full table scan and that's what my tests showed me, too. All traces showed a higher CPU and READ resource utilization when using MERGE instead of single row procedures or prepared statements.
Since I don't have production experiences with MERGE, does anybody use it for client CRUD operations? What's your performance experience?
Thanks!
Flo
November 9, 2009 at 3:13 pm
Sorry, forgot to say that: I know Adam's post 😉 but I'd like to hear some production experiences.
November 10, 2009 at 7:43 pm
Greetings Flo 🙂
We're not in production yet, but I want to ask you about your comparisons. I would expect MERGE to use more CPUs and reads than a single UPDATE batch or a single INSERT batch. The question is whether or not it would run faster than a single update AND a single insert. To do what a MERGE would do using a conventional INSERT and UPDATE would take at least two scans of the source table, joined or otherwise testing for the existence of a match in the target table. Are you saying that it takes longer to do a MERGE than to do a separate UPDATE followed by a separate INSERT?
Second question, are you considering using MERGE for individual rows, instead of mixed batches of rows to be inserted/updated?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 11, 2009 at 4:04 am
My experience is that if you have a transaction table that is significantly smaller than the master table and the master table is into the many millions of rows, then a MERGE can take more time to complete than running separate INSERT, UPDATE and DELETE statements, assuming that the UPDATE and DELETE can perform index seeks to get their data.
Where the transaction table is nearer to the size of the master table, then MERGE tends to be the fastest method, often by a considerable amount.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 11, 2009 at 4:26 am
Hi Bob
Bob Hovious 24601 (11/10/2009)
Are you saying that it takes longer to do a MERGE than to do a separate UPDATE followed by a separate INSERT?
Yep, looks like. But I have to do some deeper tests to provide a more reliable statement about that. However, as BOL says, MERGE always performs a full table scan. A usual INSERT and/or UPDATE operation performs an index seek (if available) which performs way faster - as also hinted by Adam.
Second question, are you considering using MERGE for individual rows, instead of mixed batches of rows to be inserted/updated?
Yep, too. :hehe:
The new user-defined table types and table-valued parameters introduced with SS2k8 seem to be really powerful features - apparently, even for client operations. I'm currently investigating ways to provide bulk operations from clients to the server.
If you are interested, I can share my results here, when I'm done.
Greets
Flo
November 11, 2009 at 4:36 am
EdVassie (11/11/2009)
My experience is ...
Oups... sorry Ed, didn't see your answer. (I've been out for lunch while answering Bob's post.)
Totally confirm your post with my current experiences. Nevertheless, I'll do some own tests 🙂
Thanks!
Flo
November 11, 2009 at 8:01 am
Thanks Flo, and Ed. Yes, please post your results as test. I'm very interested as we are looking at using MERGE as part of a nightly data load.
Also, could you post the link to Adam's article?
Thanks again.
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 11, 2009 at 8:05 am
Here you can find Adam's blog post:
Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE
Greets
Flo
November 12, 2009 at 3:01 pm
Florian Reischl (11/11/2009)
Yep, looks like. But I have to do some deeper tests to provide a more reliable statement about that. However, as BOL says, MERGE always performs a full table scan. A usual INSERT and/or UPDATE operation performs an index seek (if available) which performs way faster - as also hinted by Adam.
Flo, do you know what BOL page says that? Or do you have a link? I cannot seem to find that in my local BOL.
I'll have to go back and see if I can find some of my test scripts, but I swear I did not see scanning when working on my sample database on tables with 10M+ rows. Now, where did that script go?? 🙂
November 12, 2009 at 3:36 pm
Hi
Lamprey13 (11/12/2009)
Flo, do you know what BOL page says that? Or do you have a link? I cannot seem to find that in my local BOL.I'll have to go back and see if I can find some of my test scripts, but I swear I did not see scanning when working on my sample database on tables with 10M+ rows. Now, where did that script go?? 🙂
Just figured out (some hours ago) MERGE apparently doe not always performs a full table scan. My last tests used a fine index seek.
I did not yet update this information here, because I have to dive deeper into some tests to figure it out. Probably I did my first tests with too less data and SS2k8 decided a table scan as the best solution because all data are directly available.
Nevertheless, BOL says it performs a full table scan - what appears to me to be not correct at the moment.
Here the link to BOL: MERGE (Transact-SQL). In Arguments section see the third part of the TOP argument:
BOL
Because the MERGE statement performs a full table scan of both the source and target tables, I/O performance can be affected when using the TOP clause to modify a large table by creating multiple batches. In this scenario, it is important to ensure that all successive batches target new rows. For more information, see Optimizing MERGE Statement Performance.
I'll share my test results when I'm done. 🙂
Greets
Flo
November 12, 2009 at 3:57 pm
Hehe, thanks.. Yeah they changed the wording at some point. My Local BOL says:
TOP ( expression ) [ PERCENT ]
Specifies the number or percentage of rows that are affected after the source table and the target table are joined, and after rows that do not qualify for an INSERT, UPDATE, or DELETE action are removed. The full source table and the full target table are joined for each WHEN clause, and TOP is applied separately for each time.
But, I think I got better performance usign MERGE for an "upsert" than two separate commands. I'm usually using SSIS to move data from a different server so I do not persist that data then merge it when I can just upsert it from the pipeline.
November 15, 2009 at 1:49 pm
This may also be useful to know
http://weblogs.sqlteam.com/peterl/archive/2008/11/24/SQL-Server-2008-with-MERGE-and-triggers.aspx
N 56°04'39.16"
E 12°55'05.25"
November 16, 2009 at 1:19 pm
Peso-284236 (11/15/2009)
This may also be useful to knowhttp://weblogs.sqlteam.com/peterl/archive/2008/11/24/SQL-Server-2008-with-MERGE-and-triggers.aspx
I think the trigger issue has the potential to zap a lot of unsuspecting MERGE users.
I also think that TVPs will lead to some truly awful performance issues just like their table variable brethren. Actually I am counting on it - I hear a kaJJJIIINNNNGGGG in the background. 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 20, 2009 at 8:45 pm
November 20, 2009 at 11:38 pm
TheSQLGuru (11/16/2009)
Peso-284236 (11/15/2009)
This may also be useful to knowhttp://weblogs.sqlteam.com/peterl/archive/2008/11/24/SQL-Server-2008-with-MERGE-and-triggers.aspx
I think the trigger issue has the potential to zap a lot of unsuspecting MERGE users.
Seems a bit short sighted that the order of the insert-update-delete trigger cant be guaranteed.
Imagine if the trigger did some work that broke some integrity rules on a INSERT-UPDATE-DELETE order but not a DELETE-UPDATE-INSERT order. Hmmm , some experimentation is required.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply