SQL Server with Mr. Denny

Oct 21 2015   4:00PM GMT

The MERGE and large data sets

Denny Cherry Denny Cherry Profile: Denny Cherry

Tags:
SQL Server

Recently I was doing some work at a customer and they were having some performance problems with their ETL into their data warehouse (see I do know at least some BI). One of the steps which was taking a long time was a MERGE statement which was updating existing rows and inserting new rows.

The target table in this case has several hundred million rows, so it’s a pretty good sized table.

It was a rather simple MERGE statement so we tried changing it from MERGE to separate UPDATE and INSERT commands because I remembered someone complaining at a conference about how performance went down when they started using MERGE.

So we changed the stored procedure and let the new version run the next morning. And boy was it a good decision. We reduced the run times of that step by about 50%. We changed another few MERGE statements which use large tables and got other great improvements, in one case reducing the run time of the job step by 75%. A heck of an improvement.

While the MERGE statement was easier to manage, the goal is to make the system run fast. We did that in this case, very nicely.

So the lesson here is that if you have large data sets that you are working with, MERGE isn’t for you.

Denny

1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • Pmarflee
    Your assertion that MERGE is slow needs to be backed up with some hard evidence.  What was the reason for this? When is it acceptable to use MERGE? You should also provide an example of the code you were using. Maybe there was a problem with your code? Without more rigourous analysis, there's not much to be learned here.
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: