none
SQL Query is very slow RRS feed

  • Question

  • Hi,

    Below mention query is very slow i am not be able to optimize it, please help!

    DECLARE @L2_Emp_Key BIGINT = 12,
            @IsAgent INT = 0
    
    DECLARE @StartDate         AS DATE = NULL,
            @EndDate           AS DATE = NULL,
            @CurDate           AS DATE = NULL,
            @LoopStartDate     AS DATE,
            @LoopEndDate       AS DATE
                   
    SELECT @StartDate = STARTDATE,
           @ENDDATE     = ENDDATE
    FROM   V_CURRENTPAYROLLDATES
    WHERE  IsAgent      = @IsAgent
    
    	
    SET @CurDate = CONVERT(DATE, GETDATE())        
    DECLARE @ExceptionClearDays INT = NULL
    SET @ExceptionClearDays = (
            SELECT ewc.ExceptionClearDays
            FROM   ExceptionWindowConfig ewc
        )
    
    SET @LoopStartDate = DATEADD(dd, -@ExceptionClearDays, @CurDate)        
    SET @LoopEndDate = DATEADD(dd, -3, @CurDate)      
    	
    IF EXISTS (
           SELECT 1
           FROM   MasterPayrollDates
           WHERE  MasterPayrollDates.EndDate = DATEADD(dd, -1, @StartDate)
                  AND MasterPayrollDates.IsActive = 1
                  AND IsAgent = @IsAgent
       )
    BEGIN
        IF (DATEDIFF(DD, @StartDate, @CurDate) < 3)
            SET @LoopStartDate = DATEADD(dd, -3, @CurDate)
        
        IF (DATEDIFF(DD, @StartDate, @CurDate) < 3)
            SET @LoopEndDate = (
                    SELECT MasterPayrollDates.EndDate
                    FROM   MasterPayrollDates
                    WHERE  MasterPayrollDates.EndDate = DATEADD(dd, -1, @StartDate)
                           AND MasterPayrollDates.IsActive = 1
                           AND IsAgent = @IsAgent
                )
    END      
     
    SELECT COUNT(CASE WHEN vm.l1 = @L2_Emp_Key THEN 1 END) AS [l1EXDATECNT],
           COUNT(CASE WHEN vm.l2 = @L2_Emp_Key THEN 1 END) AS l2,
           vm.AttDate
    FROM   V_ExceptionTextView vm
    WHERE  vm.AttDate BETWEEN @LoopStartDate AND @LoopEndDate
           AND @IsAgent = 0
           AND (
                   (
                       vm.ATT_KEY IN (SELECT ATT_KEY
                                      FROM   AttEmpCorrData
                                      WHERE  IsCleared <> 1
                                             AND IsActive = 1)
                   )
                   OR (
                          vm.ATT_KEY NOT IN (SELECT ATT_KEY
                                             FROM   Attdata_Payout)
                      )
               )
    GROUP BY
           vm.ATTDATE

    result will be this

    Saturday, December 31, 2016 11:21 AM

Answers

  • You may optimize it with the execution plan:

    What are the indexes in support of this query?



    Kalman Toth Database & OLAP/AI Architect SQL Server 2016 Database Design
    New Book : Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016



    Saturday, December 31, 2016 11:40 AM
    Moderator
  • Hi Ankur,

    As suggested above, checking the execution plan to find out where costs a lot is good for you.  

    You could change the part “IF EXISTS (SELECT 1) … BEGIN…END” to following code to reduce the number of table scans.

    IF (DATEDIFF(DD, @StartDate, @CurDate) < 3)
    BEGIN
    	SET @LoopEndDate = (
    		SELECT MasterPayrollDates.EndDate
    		FROM   MasterPayrollDates
    		WHERE  MasterPayrollDates.EndDate = DATEADD(dd, -1, @StartDate)
    				AND MasterPayrollDates.IsActive = 1
    				AND IsAgent = @IsAgent
    	)
    END
    IF (@LoopEndDate IS NULL)
    BEGIN
    	SET @LoopEndDate = DATEADD(dd, -3, @CurDate)  
    END
    ELSE
    BEGIN
    	IF (DATEDIFF(DD, @StartDate, @CurDate) < 3)
    	BEGIN
    		SET @LoopStartDate = DATEADD(dd, -3, @CurDate)
    	END
    END
    

    Then, if the “V_ExceptionTextView” is a view, you could consider adding index on it. For more things, please have a look at this following link and this article.

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

    This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Best Regards,

    Albert Zhang


    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.

    • Marked as answer by Ankur 56600 Thursday, January 5, 2017 9:49 AM
    Monday, January 2, 2017 6:49 AM
  • Can you show the execution plan of the query? Always state what version you are using

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Ankur 56600 Thursday, January 5, 2017 9:49 AM
    Monday, January 2, 2017 8:24 AM
    Answerer
  • Hi,

    As others had said, the execution plan would help us to help you. However as one of my first rule of thumb that I follow when tunning a query, I avoid the OR operators at all cost, I see you have one on the final select, I suggest you change that by a UNION instead and see what happen. Please review the example bellow


    SELECT COUNT(CASE WHEN vm.l1 = @L2_Emp_Key THEN 1 END) AS [l1EXDATECNT],
           COUNT(CASE WHEN vm.l2 = @L2_Emp_Key THEN 1 END) AS l2,
           vm.AttDate
    FROM   V_ExceptionTextView vm
    WHERE  vm.AttDate BETWEEN @LoopStartDate AND @LoopEndDate
           AND @IsAgent = 0
           AND (
                   (
                       vm.ATT_KEY IN (SELECT ATT_KEY
                                      FROM   AttEmpCorrData
                                      WHERE  IsCleared <> 1
                                             AND IsActive = 1)
                   )
               )
    GROUP BY
           vm.ATTDATE
    UNION
    SELECT COUNT(CASE WHEN vm.l1 = @L2_Emp_Key THEN 1 END) AS [l1EXDATECNT],
           COUNT(CASE WHEN vm.l2 = @L2_Emp_Key THEN 1 END) AS l2,
           vm.AttDate
    FROM   V_ExceptionTextView vm
    WHERE  vm.AttDate BETWEEN @LoopStartDate AND @LoopEndDate
           AND @IsAgent = 0
           AND (
                   (
                          vm.ATT_KEY NOT IN (SELECT ATT_KEY
                                             FROM   Attdata_Payout)
                      )
               )
    GROUP BY
           vm.ATTDATE

    • Edited by Michael Villegas Tuesday, January 3, 2017 1:17 PM add code
    • Marked as answer by Ankur 56600 Thursday, January 5, 2017 9:49 AM
    Monday, January 2, 2017 7:44 PM
  • Here are a couple thoughts on refactoring

    IF (@IsAgent = 0)
    BEGIN
    
    ;WITH CTE_V(ATT_KEY, [l1EXDATECNT], l2, ATTDATE)
    AS
    (
    SELECT (CASE WHEN vm.l1 = @L2_Emp_Key THEN 1 END) AS [l1EXDATECNT],
           (CASE WHEN vm.l2 = @L2_Emp_Key THEN 1 END) AS l2,
           vm.AttDate
    FROM   V_ExceptionTextView vm
    WHERE  vm.AttDate BETWEEN @LoopStartDate AND @LoopEndDate
    AND @L2_Emp_Key IN (vm.l1, vm.l2)
    )
    SELECT COUNT(l1EXDATECNT) AS [l1EXDATECNT],
           COUNT(l2) AS l2,
           vm.AttDate
    FROM   CTE_V vm
    WHERE 
    	EXISTS (SELECT 1 
    	FROM   AttEmpCorrData cd
        WHERE  IsCleared <> 1
                AND IsActive = 1 c 
    			AND vm.ATT_KEY - c.ATT_KEY)
    	OR NOT EXISTS (SELECT 1
    		FROM Attdata_Payout p
    		WHERE vm.ATT_KEY = p.ATT_KEY)
    GROUP BY
           vm.ATTDATE
    END

    • Edited by Wild.Bill Tuesday, January 3, 2017 12:01 AM
    • Marked as answer by Ankur 56600 Thursday, January 5, 2017 9:49 AM
    Tuesday, January 3, 2017 12:01 AM

All replies

  • You may optimize it with the execution plan:

    What are the indexes in support of this query?



    Kalman Toth Database & OLAP/AI Architect SQL Server 2016 Database Design
    New Book : Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016



    Saturday, December 31, 2016 11:40 AM
    Moderator
  • Hi Ankur,

    As suggested above, checking the execution plan to find out where costs a lot is good for you.  

    You could change the part “IF EXISTS (SELECT 1) … BEGIN…END” to following code to reduce the number of table scans.

    IF (DATEDIFF(DD, @StartDate, @CurDate) < 3)
    BEGIN
    	SET @LoopEndDate = (
    		SELECT MasterPayrollDates.EndDate
    		FROM   MasterPayrollDates
    		WHERE  MasterPayrollDates.EndDate = DATEADD(dd, -1, @StartDate)
    				AND MasterPayrollDates.IsActive = 1
    				AND IsAgent = @IsAgent
    	)
    END
    IF (@LoopEndDate IS NULL)
    BEGIN
    	SET @LoopEndDate = DATEADD(dd, -3, @CurDate)  
    END
    ELSE
    BEGIN
    	IF (DATEDIFF(DD, @StartDate, @CurDate) < 3)
    	BEGIN
    		SET @LoopStartDate = DATEADD(dd, -3, @CurDate)
    	END
    END
    

    Then, if the “V_ExceptionTextView” is a view, you could consider adding index on it. For more things, please have a look at this following link and this article.

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

    This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Best Regards,

    Albert Zhang


    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.

    • Marked as answer by Ankur 56600 Thursday, January 5, 2017 9:49 AM
    Monday, January 2, 2017 6:49 AM
  • Can you show the execution plan of the query? Always state what version you are using

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Ankur 56600 Thursday, January 5, 2017 9:49 AM
    Monday, January 2, 2017 8:24 AM
    Answerer
  • Hi,

    As others had said, the execution plan would help us to help you. However as one of my first rule of thumb that I follow when tunning a query, I avoid the OR operators at all cost, I see you have one on the final select, I suggest you change that by a UNION instead and see what happen. Please review the example bellow


    SELECT COUNT(CASE WHEN vm.l1 = @L2_Emp_Key THEN 1 END) AS [l1EXDATECNT],
           COUNT(CASE WHEN vm.l2 = @L2_Emp_Key THEN 1 END) AS l2,
           vm.AttDate
    FROM   V_ExceptionTextView vm
    WHERE  vm.AttDate BETWEEN @LoopStartDate AND @LoopEndDate
           AND @IsAgent = 0
           AND (
                   (
                       vm.ATT_KEY IN (SELECT ATT_KEY
                                      FROM   AttEmpCorrData
                                      WHERE  IsCleared <> 1
                                             AND IsActive = 1)
                   )
               )
    GROUP BY
           vm.ATTDATE
    UNION
    SELECT COUNT(CASE WHEN vm.l1 = @L2_Emp_Key THEN 1 END) AS [l1EXDATECNT],
           COUNT(CASE WHEN vm.l2 = @L2_Emp_Key THEN 1 END) AS l2,
           vm.AttDate
    FROM   V_ExceptionTextView vm
    WHERE  vm.AttDate BETWEEN @LoopStartDate AND @LoopEndDate
           AND @IsAgent = 0
           AND (
                   (
                          vm.ATT_KEY NOT IN (SELECT ATT_KEY
                                             FROM   Attdata_Payout)
                      )
               )
    GROUP BY
           vm.ATTDATE

    • Edited by Michael Villegas Tuesday, January 3, 2017 1:17 PM add code
    • Marked as answer by Ankur 56600 Thursday, January 5, 2017 9:49 AM
    Monday, January 2, 2017 7:44 PM
  • Here are a couple thoughts on refactoring

    IF (@IsAgent = 0)
    BEGIN
    
    ;WITH CTE_V(ATT_KEY, [l1EXDATECNT], l2, ATTDATE)
    AS
    (
    SELECT (CASE WHEN vm.l1 = @L2_Emp_Key THEN 1 END) AS [l1EXDATECNT],
           (CASE WHEN vm.l2 = @L2_Emp_Key THEN 1 END) AS l2,
           vm.AttDate
    FROM   V_ExceptionTextView vm
    WHERE  vm.AttDate BETWEEN @LoopStartDate AND @LoopEndDate
    AND @L2_Emp_Key IN (vm.l1, vm.l2)
    )
    SELECT COUNT(l1EXDATECNT) AS [l1EXDATECNT],
           COUNT(l2) AS l2,
           vm.AttDate
    FROM   CTE_V vm
    WHERE 
    	EXISTS (SELECT 1 
    	FROM   AttEmpCorrData cd
        WHERE  IsCleared <> 1
                AND IsActive = 1 c 
    			AND vm.ATT_KEY - c.ATT_KEY)
    	OR NOT EXISTS (SELECT 1
    		FROM Attdata_Payout p
    		WHERE vm.ATT_KEY = p.ATT_KEY)
    GROUP BY
           vm.ATTDATE
    END

    • Edited by Wild.Bill Tuesday, January 3, 2017 12:01 AM
    • Marked as answer by Ankur 56600 Thursday, January 5, 2017 9:49 AM
    Tuesday, January 3, 2017 12:01 AM
  • Thank a ton, based on all of above suggestions i have optimize SQL Query implement index on view which i am using and the result of all these consideration is my Query is now takes much less time. Thanks a lot!
    Thursday, January 5, 2017 9:52 AM