none
SQL Server 2012 Std T-SQL Merge Statment very slow

    Question

  • Hi,

    Target table has 2 M records. I was testing Merge SQL Statement against a source table which had only 1 records (just to test if the statement was proper). To my surprise, SQL Server tool more that 3 minutes to execute this statement even though the source had only 1 record. Further more, it took almost similar time even when the source table was empty. I did not find issue with Execution plan as it was using Nested Loop over the source table to index seek on the target table. Why does it taking so much time even on an empty source table?

    -Sanjeev

    Thursday, February 14, 2013 4:00 PM

Answers

  • In my opinion, seek vs scan doesn't always matter.  It's more about how many records are being touched.  I've seen just as many records being read in seeks as in scans sometimes.  Also, despite the seek is there a key lookup going on?  There are many other things in the execution plan that can give clues to why it's taking so long.

    I generally avoid MERGE - especially for large tables.  It seems more often that not since it's performing insert / update / delete in one batch it's more likely to qualify more records to be modified which in turn disqualifies the use of an index so it scans instead.  It's similar to performing an OR with a finite amount of parameters instead of using a UNION.  It's also hard to troubleshoot.


    Derek SQLPlanet.com


    • Edited by SQLDerek Friday, February 15, 2013 2:33 AM
    • Marked as answer by Iric WenModerator Monday, February 25, 2013 9:20 AM
    Friday, February 15, 2013 2:33 AM

All replies

  • No way to tell without repro script, or at least the execution plan and the STATISTICS IO and STATISTICS TIME for your query.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, February 14, 2013 4:13 PM
  • Read below article for better understanding and Tips...

    Optimizing MERGE Statement Performance:

    http://technet.microsoft.com/en-us/library/cc879317(v=sql.105).aspx 

    Thanks,

    Saurabh


    http://www.linkedin.com/profile/view?id=36482856&trk=tab_pro http://www.experts-exchange.com/M_6313078.html

    Thursday, February 14, 2013 5:19 PM
  • So what is the statement you used? Did you properly join the source to the target table? Is the column on which you joined the two indexed?


    Gert-Jan

    Thursday, February 14, 2013 8:06 PM
  • In my opinion, seek vs scan doesn't always matter.  It's more about how many records are being touched.  I've seen just as many records being read in seeks as in scans sometimes.  Also, despite the seek is there a key lookup going on?  There are many other things in the execution plan that can give clues to why it's taking so long.

    I generally avoid MERGE - especially for large tables.  It seems more often that not since it's performing insert / update / delete in one batch it's more likely to qualify more records to be modified which in turn disqualifies the use of an index so it scans instead.  It's similar to performing an OR with a finite amount of parameters instead of using a UNION.  It's also hard to troubleshoot.


    Derek SQLPlanet.com


    • Edited by SQLDerek Friday, February 15, 2013 2:33 AM
    • Marked as answer by Iric WenModerator Monday, February 25, 2013 9:20 AM
    Friday, February 15, 2013 2:33 AM