none
Help in Tuning SQL Query

    질문

  • Can someone please help in tuning this query .

    My goal is to purge data in chunks and was thinking to loop through ID_COl value after putting it into #temptable

    DECLARE @PD datetime = DATEADD(day, -5, GETUTCDATE());
    DELETE GP
      FROM dbo.Table_A N
     INNER JOIN dbo.Table_BL GP
        ON N.NOTIF_ID = GP.NOTIF_ID
     WHERE N.FCT_AUD_IND = 'Y'
       AND N.CRE_TMST    < @PD
       AND EXISTS (   SELECT 1 ID_COl
                        FROM dbo.Table_A AS N1
                       WHERE N1.NOTIF_APPL_ID        = N.NOTIF_APPL_ID
                         AND N1.SOR_NOTIF_APPL_GP_ID = N.SOR_NOTIF_APPL_GP_ID
                         AND N1.NOTIF_DLT_IND  = 'Y');

    2018년 7월 12일 목요일 오후 10:52

답변

  • Can someone please help in tuning this query .

    My goal is to purge data in chunks and was thinking to loop through ID_COl value after putting it into #temptable

    DECLARE @PD datetime = DATEADD(day, -5, GETUTCDATE());
    DELETE GP
      FROM dbo.Table_A N
     INNER JOIN dbo.Table_BL GP
        ON N.NOTIF_ID = GP.NOTIF_ID
     WHERE N.FCT_AUD_IND = 'Y'
       AND N.CRE_TMST    < @PD
       AND EXISTS (   SELECT 1 ID_COl
                        FROM dbo.Table_A AS N1
                       WHERE N1.NOTIF_APPL_ID        = N.NOTIF_APPL_ID
                         AND N1.SOR_NOTIF_APPL_GP_ID = N.SOR_NOTIF_APPL_GP_ID
                         AND N1.NOTIF_DLT_IND  = 'Y');

    Hi Kibfais1,

    Welcome to Transact-SQL community forum.

    Per the delete statement above, please try this statement and see if it works better.

    DECLARE @PD datetime = DATEADD(day, -5, GETUTCDATE());
    
    DELETE GP
    FROM dbo.Table_BL GP
    WHERE EXISTS ( SELECT 1 
                        FROM dbo.Table_A AS N1
    					INNER JOIN dbo.Table_A N ON N1.NOTIF_APPL_ID = N.NOTIF_APPL_ID
                                                AND N1.SOR_NOTIF_APPL_GP_ID = N.SOR_NOTIF_APPL_GP_ID
                                                AND N1.NOTIF_DLT_IND  = 'Y'
    											AND N.FCT_AUD_IND = 'Y'
    											AND N.CRE_TMST    < @PD
    					 WHERE N.NOTIF_ID = GP.NOTIF_ID); 

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 7월 13일 금요일 오전 3:20
    중재자

모든 응답

  • Can someone please help in tuning this query .

    My goal is to purge data in chunks and was thinking to loop through ID_COl value after putting it into #temptable

    DECLARE @PD datetime = DATEADD(day, -5, GETUTCDATE());
    DELETE GP
      FROM dbo.Table_A N
     INNER JOIN dbo.Table_BL GP
        ON N.NOTIF_ID = GP.NOTIF_ID
     WHERE N.FCT_AUD_IND = 'Y'
       AND N.CRE_TMST    < @PD
       AND EXISTS (   SELECT 1 ID_COl
                        FROM dbo.Table_A AS N1
                       WHERE N1.NOTIF_APPL_ID        = N.NOTIF_APPL_ID
                         AND N1.SOR_NOTIF_APPL_GP_ID = N.SOR_NOTIF_APPL_GP_ID
                         AND N1.NOTIF_DLT_IND  = 'Y');

    Hi Kibfais1,

    Welcome to Transact-SQL community forum.

    Per the delete statement above, please try this statement and see if it works better.

    DECLARE @PD datetime = DATEADD(day, -5, GETUTCDATE());
    
    DELETE GP
    FROM dbo.Table_BL GP
    WHERE EXISTS ( SELECT 1 
                        FROM dbo.Table_A AS N1
    					INNER JOIN dbo.Table_A N ON N1.NOTIF_APPL_ID = N.NOTIF_APPL_ID
                                                AND N1.SOR_NOTIF_APPL_GP_ID = N.SOR_NOTIF_APPL_GP_ID
                                                AND N1.NOTIF_DLT_IND  = 'Y'
    											AND N.FCT_AUD_IND = 'Y'
    											AND N.CRE_TMST    < @PD
    					 WHERE N.NOTIF_ID = GP.NOTIF_ID); 

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 7월 13일 금요일 오전 3:20
    중재자
  • maybe something like this 
    DECLARE @Deleted_Rows INT;
    SET @Deleted_Rows = 1;
    DECLARE @PD datetime = DATEADD(day, -5, GETUTCDATE());
    
    WHILE (@Deleted_Rows > 0)
      BEGIN
    
       BEGIN TRANSACTION
    
       -- Delete some small number of rows at a time
      DELETE TOP (1000) GP FROM dbo.Table_A N 	
      INNER JOIN dbo.Table_BL GP
      ON N.NOTIF_ID = GP.NOTIF_ID
    	WHERE N.FCT_AUD_IND = 'Y'
    		  AND N.CRE_TMST    < @PD
    		  AND N.NOTIF_DLT_IND  = 'Y'
    
         SET @Deleted_Rows = @@ROWCOUNT;
    
       COMMIT TRANSACTION
       CHECKPOINT -- for the simple recovery model
    END

    2018년 7월 13일 금요일 오전 3:58
  • Hello ITGUY...,

    Your logic is nice but results of the original query is different  .

    2018년 7월 13일 금요일 오후 7:34