sql query performance - Kindly help to resolve using below execution plan

Anonymous
2022-11-16T08:39:36.227+00:00

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#AA942133'. Scan count 0, logical reads 52, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#AC7C69A5'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Ord'. Scan count 52, logical reads 2936, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#AA942133'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 13 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'OrdCust'. Scan count 9766, logical reads 31523, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'OrdAdj'. Scan count 12119, logical reads 40016, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Ord'. Scan count 12067, logical reads 38971, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'OrdItem'. Scan count 12067, logical reads 53428, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#AA942133'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Employee'. Scan count 1, logical reads 231, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 375 ms, elapsed time = 397 ms.
Table '#AB88456C'. Scan count 0, logical reads 10125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 26257, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 406 ms, elapsed time = 429 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'OrdAdj'. Scan count 5, logical reads 5068, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#AA942133'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Ord'. Scan count 52, logical reads 3008, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'OrdCust'. Scan count 1037, logical reads 3446, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Employee'. Scan count 5, logical reads 721, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 204 ms, elapsed time = 57 ms.
Table '#AB88456C'. Scan count 0, logical reads 1070, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 2482, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 204 ms, elapsed time = 61 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'OrdCust'. Scan count 65553, logical reads 211493, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Ord'. Scan count 65553, logical reads 211731, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'OrdItem'. Scan count 64107, logical reads 330217, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'OrdCpn'. Scan count 52, logical reads 1478, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#AA942133'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Employee'. Scan count 1, logical reads 242, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 5657 ms, elapsed time = 5680 ms.
Table '#AB88456C'. Scan count 0, logical reads 68564, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 233768, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 5875 ms, elapsed time = 5901 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 6671, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#AB88456C'. Scan count 1, logical reads 3294, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 3639 ms.

SQL Server Execution Times:
CPU time = 6517 ms, elapsed time = 10049 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Anonymous
    2022-11-20T12:57:11.627+00:00

    hi, let me know if i can give you the store procedure and if possible can you fine tune it

    0 comments No comments

  2. Dan Guzman 9,226 Reputation points
    2022-11-20T13:23:52.207+00:00

    Without seeing the stored procedure code, one thing you might try is to use temp tables instead of table variables. Temp tables can provide better performance than table variables for larger number of rows.

    Upload the actual execution plan XML to PasteThePlan and add the link here. If the proc code is too large to include in your question, attach the code as a text file. The CREATE TABLE scripts (including indexes) will also help us help you.

    0 comments No comments

  3. Anonymous
    2022-11-20T13:46:11.557+00:00

    ALTER PROCEDURE [dbo].[GetDODO]
    @sDate datetime,
    @eDate datetime,
    @isAggregate bit,
    @tempList IntegerTableType READONLY
    AS
    BEGIN
    SET NOCOUNT ON;
    SELECT CASE WHEN @IsAggregate = 1 THEN 0 ELSE O.ABVSTID END As ABVSTID,
    O.OrdStartTime AS OrdTime
    , OI.BZDT
    , OA.TimeApplied
    , CASE WHEN DATEDIFF(mi,O.OrdStartTime,OA.TimeApplied) > 0 THEN 1
    ELSE 0 END AS Bold
    , O.OrdNumber
    , O.OrdType
    , CASE WHEN OA.AdjType = 1 THEN 'Voids'
    ELSE 'Adjustments' END AS AdjType
    , CASE WHEN OA.AdjType = 1 THEN 'Void Item'
    WHEN OA.AdjType = 2 THEN 'Comp'
    WHEN OA.AdjType = 3 THEN 'Percent Off'
    WHEN OA.AdjType = 4 THEN 'Edit Price'
    WHEN OA.AdjType = 8 THEN 'Spill'
    WHEN OA.AdjType = 6 THEN 'Customer Discount'
    ELSE '' END AS AdjSubType
    , CASE WHEN OA.AdjType = 1 THEN 0
    WHEN OA.AdjType = 2 THEN 4
    WHEN OA.AdjType = 3 THEN 2
    WHEN OA.AdjType = 4 THEN 3
    WHEN OA.AdjType = 8 THEN 1
    WHEN OA.AdjType = 6 THEN 5
    ELSE 0 END AS Seq
    , CASE WHEN OA.WasPrepared <> 0 THEN 'X' ELSE '' END AS Made

    , OI.Qty AS Qty
    , ISNULL(OI.SizeRcptName,'')+' '+ISNULL(OI.StyleRcptName,'')+' '+ISNULL(OI.RcptName,'') AS Item
    , OC.SelPhone AS CustPhone
    , OA.Reason
    , OA.ActiveValue As Amt
    , ISNULL(E.FirstName,'')+' '+ISNULL(E.LastName,'')+' APV-'+ ISNULL(OA.ApprovalEmployee,'') AS ApprovalEmployee
    , CASE WHEN OA.AdjType = 1 THEN 'Void Item'
    WHEN OA.AdjType = 2 THEN 'Comp'
    WHEN OA.AdjType = 3 THEN CAST(OA.AdjFactor*100 AS nvarchar(25))+ '%'
    WHEN OA.AdjType = 4 THEN 'Edit Price'
    WHEN OA.AdjType = 8 THEN 'Spill'
    WHEN OA.AdjType = 6 THEN ' '
    ELSE '' END AS Name
    FROM ITEM OI WITH(NOLOCK)
    INNER JOIN @TempList T ON T.ID = OI.ABVSTID
    INNER JOIN Ord O ON OI.ABVSTID = O.ABVSTID AND O.BZDT = OI.BZDT AND O.OrdNumber = OI.OrdNumber AND O.HS = 0
    INNER JOIN OrdAdj OA ON OA.ABVSTID = OI.ABVSTID AND OA.BZDT = OI.BZDT AND OA.OrdNumber = OI.OrdNumber AND OA.OrdItemNumber = OI.OrdItemNumber AND OA.AdjScope = 2
    CROSS APPLY
    (
    SELECT COUNT(*) As VoidCnt
    FROM ADO WITH(NOLOCK)
    WHERE ABVSTID = OI.ABVSTID AND BZDT = OI.BZDT AND OrdNumber = OI.OrdNumber AND AdjScope = 1 AND AdjType = 1
    ) Voided
    LEFT OUTER JOIN CUSTOMER OC ON OC.ABVSTID = OI.ABVSTID AND OC.BZDT = OI.BZDT AND OC.OrdNumber = OI.OrdNumber
    LEFT OUTER JOIN Employee E ON E.ABVSTID = O.ABVSTID AND E.EmployeeKey=O.OrdStartEmplKey
    WHERE OI.BZDT BETWEEN @SDate AND @EDate AND Voided.VoidCnt = 0
    END

    0 comments No comments