September 19, 2012 at 9:40 am
Hi,
SQL Server has the MERGE statement and some people advise to use it instead of IF EXISTS with INSERT / UPDATE...
Is it worth upgrading from IF EXISTS .. to MERGE?
CREATE PROCEDURE usp_Upsert_teste1_1
@ID INT,
@Desc VARCHAR(100)
AS
IF EXISTS (SELECT TOP 1 1 FROM teste1 WHERE ID = @ID)
UPDATE teste1 SET Description = @Desc WHERE ID = @ID
ELSE
INSERT INTO teste1 (ID, Description) VALUES (@ID, @Desc)
GO
CREATE PROCEDURE usp_Upsert_teste1_2
@ID INT,
@Desc VARCHAR(100)
AS
MERGE teste1 AS target
USING (SELECT @ID, @Desc) AS source (ID, Dsc)
ON target.ID = source.ID
WHEN MATCHED THEN
UPDATE SET target.Description = source.Dsc
WHEN NOT MATCHED THEN
INSERT (ID, Description) VALUES (source.ID, source.Dsc);
The execution plan is slower on the 1st sp because of the IF... The INSERT or UPDATE are as fast (according to execution plan) as the MERGE.
Thanks,
Pedro
September 19, 2012 at 9:53 am
If you need to maintain backwards compatibility with versions of SQL Server prior to 2008, then don't use Merge. Otherwise, it does have advantages.
The main advantage, from my perspective, is that you can do the action in one statement. That means less code to maintain, and it's obvious that it's an upsert instead of possibly two different actions. And, since a Merge statement can use either an Output Into or be wrapped in an Insert Select with a simpler Output clause, you can do your logging in one place instead of two (if you need to do audit logging, that is).
It also has advantages over Update From, if you use that, because it will throw an error instead of doing the wrong thing, if two rows could end up updating the same row in the destination.
If 99% of the time, an Update will happen, then you're better off with:
Update
if @@rowcount = 0
Insert
More efficient that way.
Beyond those points, it's really up to you. I like Merge, but it took a while to get used to the syntax.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 19, 2012 at 10:17 am
And another thing to mention for MERGE is that SQL Server kind of splits the data into up to three "streams" and executes INSERT, UPDATE and DELETE (if required).
So, for example, if you have a single trigger for INSERT, UPDATE, DELETE it will still be executed separately three times. You have no control over what will happen first, as order of execution is not guaranteed...
Another small caveat for using INSTEAD OF triggers, in case if you want use MERGE, they should not be present at all or they should be implemented for all three operations.
September 19, 2012 at 10:27 am
One thing which I have noticed with MERGE is that it does not natively provide separate Updated/Inserted/Deleted counts.
You need to code an OUTPUT clause and use $Action to get these. Without that you just get a total 'rows affected' count in @@RowCount.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 19, 2012 at 11:25 am
Phil Parkin (9/19/2012)
One thing which I have noticed with MERGE is that it does not natively provide separate Updated/Inserted/Deleted counts.You need to code an OUTPUT clause and use $Action to get these. Without that you just get a total 'rows affected' count in @@RowCount.
Yep. And that can be a good thing if you want the count for an atomic upsert, or a bad thing if you need to know which had which counts. Still, can be done within the statement, instead of 3 counts (one for each action). And, if you use Pivot on the Output, you can even get all three counts in a single row, which is kind of cool.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 20, 2012 at 12:41 am
GSquared (9/19/2012)
Phil Parkin (9/19/2012)
One thing which I have noticed with MERGE is that it does not natively provide separate Updated/Inserted/Deleted counts.You need to code an OUTPUT clause and use $Action to get these. Without that you just get a total 'rows affected' count in @@RowCount.
Yep. And that can be a good thing if you want the count for an atomic upsert, or a bad thing if you need to know which had which counts. Still, can be done within the statement, instead of 3 counts (one for each action). And, if you use Pivot on the Output, you can even get all three counts in a single row, which is kind of cool.
Unless I'm mistaken, it would be even cooler if you could assign the 3 PIVOTed columns directly to local variables instead of being forced to INSERT them into a 3 column table in the outer wrapper of the composable DML.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 20, 2012 at 6:07 am
dwain.c (9/20/2012)
GSquared (9/19/2012)
Phil Parkin (9/19/2012)
One thing which I have noticed with MERGE is that it does not natively provide separate Updated/Inserted/Deleted counts.You need to code an OUTPUT clause and use $Action to get these. Without that you just get a total 'rows affected' count in @@RowCount.
Yep. And that can be a good thing if you want the count for an atomic upsert, or a bad thing if you need to know which had which counts. Still, can be done within the statement, instead of 3 counts (one for each action). And, if you use Pivot on the Output, you can even get all three counts in a single row, which is kind of cool.
Unless I'm mistaken, it would be even cooler if you could assign the 3 PIVOTed columns directly to local variables instead of being forced to INSERT them into a 3 column table in the outer wrapper of the composable DML.
Yeah, would be very cool if Output worked more like Select, and could assign values to variables using inline sub-queries and things like that. Maybe a future version will.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 21, 2012 at 8:34 am
For concurrency reasons it is MUCH preferred to use MERGE I think. I have seen a number of clients over the years get bad data in their system doing multiple steps for what you are trying to do.
Do be careful if you have triggers on your tables though, or if you need to test @@ROWCOUNT. Some unexpected things can arise.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 21, 2012 at 11:46 am
TheSQLGuru (9/21/2012)
For concurrency reasons it is MUCH preferred to use MERGE I think. I have seen a number of clients over the years get bad data in their system doing multiple steps for what you are trying to do.Do be careful if you have triggers on your tables though, or if you need to test @@ROWCOUNT. Some unexpected things can arise.
Yeah, if you do the multi-step version, transaction control is critical. That's for sure!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 23, 2012 at 1:14 am
GSquared (9/21/2012)
Yeah, if you do the multi-step version, transaction control is critical. That's for sure!
Aside from ensuring the changes are atomic, there is another issue that no pattern mentioned so far addresses (MERGE has this problem in the WHERE NOT MATCHED clause). Just because there is no row at the time the existence check is performed, that is no guarantee that a concurrent process will not add one before we try to insert. This is a case where only a SERIALIZABLE hint will do.
http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx
September 23, 2012 at 3:09 am
PiMané (9/19/2012)
The execution plan is slower on the 1st sp because of the IF... The INSERT or UPDATE are as fast (according to execution plan) as the MERGE.
Please don't compare estimated costs in execution plans. The estimated cost is just the optimizer's estimate based on an internal model useful for deciding between plan alternatives for the same statement. Estimated costs for different statements are comparing apples with unicorns. Test alternatives measuring whatever performance metric is important to you.
September 23, 2012 at 3:15 am
PiMané (9/19/2012)
SQL Server has the MERGE statement and some people advise to use it instead of IF EXISTS with INSERT / UPDATE...Is it worth upgrading from IF EXISTS .. to MERGE?
Like everything, there is no always-right answer. You need to understand the advantages and disadvantages of each method, and make a decision based on which is right for your particular circumstances. The advantages of MERGE have been pretty well discussed already, so in the interests of balance here are some disadvantages:
- MERGE has a higher compilation cost than INSERT/UPDATE/DELETE. This can be an important consideration if you are performing many single-row changes.
- The useful check that each target row in only updated once has some overhead. This may be the dominant factor in some query plans. For example, the physical plan operators involved in the check cannot use parallelism. In a large modification query, stopping and restarting parallelism at the check operators can make MERGE many times slower than an equivalent, but separate, INSERT/UPDATE/DELETE approach (carefully written to avoid multiple updates).
- There have been (and continue to be) many bugs reported with MERGE. It is a complex operator, so perhaps this is expected. Nevertheless, no-one likes incorrect error messages, incorrect results or foreign key violations caused by MERGE. Some examples (fixed and unfixed) from Connect below:
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=476577
https://connect.microsoft.com/SQLServer/feedback/details/752088/
http://connect.microsoft.com/SQLServer/feedback/details/539084
September 23, 2012 at 4:17 am
Estimated costs for different statements are comparing apples with unicorns
I'm pretty sure that my unicorn will run faster than your apple:-D
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 23, 2012 at 6:24 am
Phil Parkin (9/23/2012)
Estimated costs for different statements are comparing apples with unicorns
I'm pretty sure that my unicorn will run faster than your apple:-D
Ha, true. There again, the apple does have the advantage of actually existing...
September 23, 2012 at 10:13 am
SQL Kiwi (9/23/2012)
Phil Parkin (9/23/2012)
Estimated costs for different statements are comparing apples with unicorns
I'm pretty sure that my unicorn will run faster than your apple:-D
Ha, true. There again, the apple does have the advantage of actually existing...
you doubt the invisible pink unicorn, shame on you.
To the thread, merge is new and shiny and like very thing shiny needs to be tested. however once you have tested and decide the new shiny thing works really well for you why not use it. i think the main point is test test and test some more.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply