The MERGE and large data sets

Published On: 2015-10-21By:

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


Contact the Author | Contact DCAC

One response to “The MERGE and large data sets”

  1. Pmarflee says:

    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.

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link