3

We are using the following query to merge new data between tables.

-- Merge descriptions from Sync_CheckPointDescriptions table with temporary table #temp
    MERGE CheckPointDescriptions AS TARGET
    USING (
        SELECT dbo.Sync_CheckPointsPerOrder.CheckPointNL As [Text], dbo.CheckPointsPerOrder.CheckPointID, '2CECA4E4-0322-4DD6-B7ED-5C41D34B6136' AS LanguageID
        FROM dbo.Sync_CheckPointsPerOrder 
        INNER JOIN dbo.CheckPointsPerOrder 
        ON dbo.Sync_CheckPointsPerOrder.CheckPointID = dbo.CheckPointsPerOrder.OldCheckPointID
        WHERE datalength(dbo.Sync_CheckPointsPerOrder.CheckPointNL) > 0 AND (OrderStatus <> 'HISTORIE' OR OrderStatus IS NULL)
        UNION
        SELECT dbo.Sync_CheckPointsPerOrder.CheckPointEN As [Text], dbo.CheckPointsPerOrder.CheckPointID, 'EB9B8B2C-B956-44AB-A843-E645DB1FF487' AS LanguageID
        FROM dbo.Sync_CheckPointsPerOrder 
        INNER JOIN dbo.CheckPointsPerOrder 
        ON dbo.Sync_CheckPointsPerOrder.CheckPointID = dbo.CheckPointsPerOrder.OldCheckPointID
        WHERE datalength(dbo.Sync_CheckPointsPerOrder.CheckPointEN) > 0 AND (OrderStatus <> 'HISTORIE' OR OrderStatus IS NULL)
        UNION
        SELECT dbo.Sync_CheckPointsPerOrder.CheckPointPL As [Text], dbo.CheckPointsPerOrder.CheckPointID, 'F2973A81-A3C6-418B-ABC2-163F4541BE94' AS LanguageID
        FROM dbo.Sync_CheckPointsPerOrder 
        INNER JOIN dbo.CheckPointsPerOrder 
        ON dbo.Sync_CheckPointsPerOrder.CheckPointID = dbo.CheckPointsPerOrder.OldCheckPointID
        WHERE datalength(dbo.Sync_CheckPointsPerOrder.CheckPointPL) > 0 AND (OrderStatus <> 'HISTORIE' OR OrderStatus IS NULL)
    ) AS SOURCE 
    ON (
        TARGET.[Text] = SOURCE.[Text] AND  
        TARGET.CheckPointID = SOURCE.CheckPointID AND 
        TARGET.LanguageID = SOURCE.LanguageID
    ) 
    ----When no records are matched, insert
    ----the incoming records from source
    ----table to target table
    WHEN NOT MATCHED BY TARGET THEN 
    INSERT ([Text],
            CheckPointID, 
            LanguageID) 
    VALUES (SOURCE.[Text],
            SOURCE.CheckPointID,
            SOURCE.LanguageID);
  1. Is there a way to make this more efficient? The statement is taking 7 seconds to finish. The target table has 2.5 million records and the source table has 70.000 records.

  2. Is this statement locking the table for the full 7 seconds?

  3. After adding the new records I have to run sp_updatestats or else the my application will timeout after 30 seconds. Is there a way to prevent this?

Execution plan XML

  • 6
    Change the unions to union alls, for starters. And no it isn't completely locking for the full duration because you're not using a hold lock hint (many people make the assumption that a merge statement is fully isolated on its own, but it is actually multiple underlying operations). Please read this before getting too attached to merge: mssqltips.com/sqlservertip/3074/… – Aaron Bertrand Sep 10 '15 at 12:23
  • 1
    Can you post execution plan ? You can use SQL sentry's free plan explorer and pastebin to upload plan XML ? – Kin Shah Sep 10 '15 at 12:24
  • Hi Kin just added the execution plan XML. @Aaron will add the union alls and read your link. – Guido Neele Sep 10 '15 at 12:31
  • @GuidoNeele seems some hex value is messed out in Plan XML. Can you paste it RAW in pastebin. – Kin Shah Sep 10 '15 at 14:12
  • 1
    To help with point 3, we'd need to understand what query the application is running, which has nothing to do with the MERGE statement above. You can't capture a plan obviously if the thing times out, but maybe you could temporarily increase the timeouts so you can better troubleshoot it. Right now we have about enough information to suggest as the only fix: update stats after such a data change. But in all honesty, no end user application query should even approach 30 seconds, so it's highly likely that it can be tuned with a rewrite or index changes. We just don't have enough info to help. – Aaron Bertrand Sep 10 '15 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.