locked
Performance Tuning RRS feed

  • Question

  • Can Some one help me in tuning below code pls,

    it is taking too long to execute,, my table contains 2 million rows. 

    MERGE [dbo].[Plan] pl
    			USING Plans p
    				ON pl.PlanId = p.PlanId
    			WHEN MATCHED AND p.Tag IN (1,2)
    				THEN UPDATE
    					SET pl.Live = CASE WHEN @LivePlan IS NOT NULL AND p.Tag = 1 THEN 1 ELSE 0 END
    					,pl.SignOffId_MT = ISNULL(pl.SignOffId_MT,@SignOffId)
    					,pl.SignOffId_Fi = ISNULL(pl.SignOffId_Fi,@LiveSignOffId_Fi)
    					,pl.SignOffId_Cl = ISNULL(pl.SignOffId_Cl,@LiveSignOffId_Cl)
    			WHEN MATCHED AND pl.SignOffId_Client IS NULL
    				THEN DELETE;
    				
    				
    				

    Thanks In Advance

    Thursday, March 12, 2015 2:42 PM

Answers

  • Hi, for best performance with a MERGE, its a good idea to have both source and target tables indexed on the columns used to determine a MATCH. Other suggestions here:

    https://msdn.microsoft.com/en-us/library/cc879317%28v=sql.105%29.aspx

    Hope this helps

    Pasquale


    Questo post è fornito "così com'è". Non conferisce garanzie o diritti di alcun tipo. Ricorda di usare la funzione "segna come risposta" per i post che ti hanno aiutato a risolvere il problema e "deseleziona come risposta" quando le risposte segnate non sono effettivamente utili. Questo è particolarmente utile per altri utenti che leggono il thread, alla ricerca di soluzioni a problemi similari. ENG: This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Charlie Liao Monday, March 16, 2015 7:15 AM
    • Marked as answer by pituachMVP Monday, March 23, 2015 10:16 PM
    Thursday, March 12, 2015 3:31 PM

All replies

  • Perhaps you break the statement in to two as below. I am thinking When Matched AND p.Tag in (1,2) is slowing you down. Try it and see the result.

    			MERGE [dbo].[Plan] pl
    			USING Plans p
    				ON pl.PlanId = p.PlanId
    			WHEN MATCHED AND p.Tag =1
    				THEN UPDATE
    					SET pl.Live = CASE WHEN @LivePlan IS NOT NULL AND p.Tag = 1 THEN 1 ELSE 0 END
    					,pl.SignOffId_MT = ISNULL(pl.SignOffId_MT,@SignOffId)
    					,pl.SignOffId_Fi = ISNULL(pl.SignOffId_Fi,@LiveSignOffId_Fi)
    					,pl.SignOffId_Cl = ISNULL(pl.SignOffId_Cl,@LiveSignOffId_Cl)
    			WHEN MATCHED AND pl.SignOffId_Client IS NULL
    				THEN DELETE;
    
    
    				MERGE [dbo].[Plan] pl
    			USING Plans p
    				ON pl.PlanId = p.PlanId
    			WHEN MATCHED AND p.Tag = 2
    				THEN UPDATE
    					SET pl.Live = CASE WHEN @LivePlan IS NOT NULL AND p.Tag = 1 THEN 1 ELSE 0 END
    					,pl.SignOffId_MT = ISNULL(pl.SignOffId_MT,@SignOffId)
    					,pl.SignOffId_Fi = ISNULL(pl.SignOffId_Fi,@LiveSignOffId_Fi)
    					,pl.SignOffId_Cl = ISNULL(pl.SignOffId_Cl,@LiveSignOffId_Cl)
    			WHEN MATCHED AND pl.SignOffId_Client IS NULL
    				THEN DELETE;
    


    ebro

    Thursday, March 12, 2015 3:11 PM
  • What indexes do you have on your two tables?

    Have you considered splitting it into two statements? A DELETE followed by an UPDATE?

    DELETE Plans p

    JOIN Plan pl ON pl.PlanId=p.PlanId

    WHERE pl.SignOffId IS NULL;

    UPDATE Plans p

    SET pl.Live = CASE WHEN @LivePlan IS NOT NULL AND p.Tag = 1 THEN 1 ELSE 0 END
         ,pl.SignOffId_MT = ISNULL(pl.SignOffId_MT,@SignOffId)
         ,pl.SignOffId_Fi = ISNULL(pl.SignOffId_Fi,@LiveSignOffId_Fi)
         ,pl.SignOffId_Cl = ISNULL(pl.SignOffId_Cl,@LiveSignOffId_Cl)

    FROM Plans p

    JOIN Plan pl ON pl.PlanId=p.PlanId

    WHERE p.Tag IN (1,2)

    Thursday, March 12, 2015 3:14 PM
  • Hi, for best performance with a MERGE, its a good idea to have both source and target tables indexed on the columns used to determine a MATCH. Other suggestions here:

    https://msdn.microsoft.com/en-us/library/cc879317%28v=sql.105%29.aspx

    Hope this helps

    Pasquale


    Questo post è fornito "così com'è". Non conferisce garanzie o diritti di alcun tipo. Ricorda di usare la funzione "segna come risposta" per i post che ti hanno aiutato a risolvere il problema e "deseleziona come risposta" quando le risposte segnate non sono effettivamente utili. Questo è particolarmente utile per altri utenti che leggono il thread, alla ricerca di soluzioni a problemi similari. ENG: This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Charlie Liao Monday, March 16, 2015 7:15 AM
    • Marked as answer by pituachMVP Monday, March 23, 2015 10:16 PM
    Thursday, March 12, 2015 3:31 PM
  • unfortunately, No one can help you tuning query / database without getting the DDL. all we can do is guess and post general comments.

    At this time I will mark paSQuaLe ceglie response as the answer.

    If this issue is still relevant and you did not got the answer, pleas post DDL+DML (some sample data will help us to check the DDL).

    Thanks


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Monday, March 23, 2015 10:16 PM