订购时的性能

Question

对于以下脚本：

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

这似乎需要很长时间才能完成。我把范围缩小到了最后定单条款。

如果注释ORDER子句，则在13秒内返回741594行。如果我让脚本与ORDER子句一起运行，它将运行超过13个小时。

我漏掉了什么，或者我怎么找出是什么导致了延迟？

Answer 1

为了测试，你必须简化这个程序。拔出连接并选择标准和度量。每次添加一个项目，然后重新度量，看看问题出在哪里。同时，研究执行计划。

Answer 2

问题似乎是ORDER BY td.TT_TAG_ID。如果左联接为列提供空值，那么如何排序？

你可以试着用p.TT_TAG_ID点菜