NOT MATCHED and MATCHED parts of a SQL MERGE statement are not optimized - by Josh Patterson

Status : 

 


17
0
Sign in
to vote
ID 635778 Comments
Status Active Workarounds
Type Bug Repros 6
Opened 1/12/2011 2:19:53 PM
Access Restriction Public

Description

The "MATCHED" and "NOT MATCHED" parts of a SQL MERGE statement are not optimized when criteria is based on constant or variable values, causing the statement to run for unnecessarily long periods of time and consume excess resources even though no action can actually be performed due to the criteria. This causes the merge statement to perform great degrees of additional work as is demonstrated in the attached SQL file.

As a workaround for this, we have had to remove parts of the merge statement and perform them separately within IF blocks thereby negating the luster of the MERGE statement by needing to join to the source multiple times (insert, update and delete).

Simply put: when the criteria "WHEN NOT MATCHED AND 88 = 22" portion of the statement would completely prevent any action  then do not attempt to perform those actions.
Sign in to post a comment.
Posted by Ronaldo R Conde on 7/10/2015 at 2:44 PM
I have the same behavior on SQL 2012.
The workaround using recompile is working for now, but my concern is it stops to work and impact my current code in place.
Due this issue a MERGE command stopped to work and generated a huge impact on my company operation.

Is there any fix for it on SQL 2012?

Regards
Ronaldo
Posted by Microsoft on 2/2/2011 at 11:34 AM
Thanks for your feedback. The problem comes from the fact that the query optimizer may have difficulties to optimize the predicates of this form: <parameter>=constant. The RECOMPILE option may help in the general case, but to be sure to remove unnecessary predicates from your MERGE statement, I would recommend to use either a “IF” as you mentioned, or Dynamic SQL.
Using Dynamic SQL, you can write the merge statement only once and add the additional statement only when your condition is satisfied (when @Mode=0). Doing this you are sure to have the expected behavior with redundancy in your query.
Let me know if it works for you.
Best regards,

Jean-Sébastien
Posted by Josh Patterson on 1/25/2011 at 11:08 AM
Revision:

Creating the procedures with "WITH RECOMPILE" did not change the performance of the procedure. The MERGE statement still seems to scan the data unnecessarily.

We have created a separate merge statement within each of the procedures which is within an IF block – this cuts the duration of the procedure calls down to a few hundredths of the ones with the variable in the "WHEN NOT MATCHED AND @Var = 1" statement.

The only problem with this approach is that we have to include all of our joins in the procedure multiple times and SQL Server must scan the data multiple times. Merge statements are supposed to remove the need to scan data multiple times and reduce the need for redundant join logic.

Thoughts?
Posted by Josh Patterson on 1/25/2011 at 11:06 AM
Creating the procedures with "WITH RECOMPILE" did not change the performance of the procedure. The MERGE statement still seems to scan the data unnecessarily.

We have created a separate merge statement within each of the procedures which is within an IF block – this cuts the procedure calls down to a few hundredths of the calls with the variable in the MERGE statement.

The only problem with this approach is that we have to include all of our joins in the procedure multiple times and SQL Server must scan the data multiple times. Merge statements are supposed to remove the need to scan data multiple times and reduce the need for redundant join logic.

Thoughts?
Posted by Microsoft on 1/19/2011 at 4:53 PM
Dear Josh,
Thanks for taking time to post this suggestion.
The problem you mentioned comes from the fact that when you run a stored procedure for the first time, the query optimizer builds a plan which does not take into account that variable.
So, as a workaround I would suggest to add OPTION(RECOMPILE), it will ensure that the procedure is recompiled and so will force to use a new plan.
However, your suggestion is very valuable and concerns not only the MERGE statement but all cached plans. We will look into this problem in the upcoming releases.
Best regards,
Jean-Sebastien, SQL Server