CODEWITHSUNDEEP
sqlsql-server-2008
RobbieE
RobbieE

Reputation: 4350

Performance when ordering

For the following script:

DECLARE @StartDate DATETIME,
        @EndDate DATETIME,
        @SearchSt INT

SET @StartDate = '2014-02-01'
SET @EndDate = '2014-02-20'
SET @SearchSt = 2;

WITH DSAEvent
AS
(
 SELECT ROW_NUMBER() OVER
        (
         PARTITION BY e.TT_TAG_ID 
             ORDER BY e.TT_TAG_ID,
                      e.DSA_Event_Date,
                      e.DSA_Time_In,
                      e.DSA_Time_Out,
                      e.TAG_Inter_DSA_Seq_No
        ) AS rn,
        e.EventID, e.TT_TAG_ID, e.DSA_ID, e.DSA_Purpose_ID, e.DSA_Time_In,
        e.DSA_Time_Out, e.DSA_Duration, e.Sh_DSA_Event_Record_Type,
        e.TAG_Inter_DSA_Seq_No, 
        ISNULL((
                 SELECT CASE
                            WHEN e.DSA_Purpose_ID = 3 THEN ISNULL(T_No, 0)
                            ELSE -1
                        END
                   FROM DSAs
                  WHERE St_ID = @SearchSt
                    AND DSA_ID = e.DSA_ID
               ), -1) AS T_No
   FROM SH_DSA_EVENT_WORKING e
  WHERE e.Store_ID = @SearchSt
    AND e.DSA_Event_Date >= @StartDate
    AND e.DSA_Event_Date <= @EndDate
    AND e.Sh_ID IS NULL
)

 SELECT p.TT_TAG_ID, p.DSA_ID, p.DSA_Purpose_ID, p.DSA_Time_In,
        p.DSA_Time_Out, p.DSA_Duration, p.Sh_DSA_Event_Record_Type,
        p.TAG_Inter_DSA_Seq_No, p.Till_No,
        ABS(DATEDIFF(
                second,
                ISNULL(n.DSA_Time_In, '2000-01-01 00:00:00'),
                ISNULL(p.DSA_Time_Out, '2000-01-01 00:00:00'))
           ), 
        td.TAG_Asset_Type_ID, p.EventID
   FROM DSAEvent p
        LEFT JOIN DSAEvent n ON p.rn = n.rn - 1 AND p.TT_TAG_ID = n.TT_TAG_ID
        LEFT JOIN TAG_DETAIL td ON td.TT_TAG_ID = p.TT_TAG_ID
  WHERE ISNULL(td.St_ID, @SearchSt) = @SearchSt
  ORDER BY td.TT_TAG_ID, p.DSA_Time_In, p.DSA_Time_Out,
           p.TAG_Inter_DSA_Seq_No, p.rn

This seems to be taking forever to run. I've narrowed it down to the final ORDER clause.

If I comment out the ORDER clause, I get 741594 rows returned in 13 seconds. If I let the script run with the ORDER clause, it will run in excess of 13 hours.

What am I missing, or how to I figure out what's causing the delay?

Upvotes: 0

Views: 53

Answers (2)

n8wrl
n8wrl

Reputation: 19765

You have to simplify this for testing. Pull out joins and select criteria and measure. Add items back one at a time and re-measure to see where the problem is. Also, study execution plan

Upvotes: 0

StanislavL
StanislavL

Reputation: 57381

It seems the problem is ORDER BY td.TT_TAG_ID. If LEFT JOIN provides null value for the column how it could be ordered?

You can try to ORDER BY p.TT_TAG_ID

Upvotes: 1

Related Questions