视图运行缓慢，我必须使用 select into 临时表吗？

Question

所以我遇到了一个严重的麻烦，因为我的最后一个查询执行时间是 8 分钟才能获取 2500 个节点。它只是其他两种视图的组合。

第一个视图是：

SELECT
   RecTime, SQL AS str, ID, 
   ROW_NUMBER() OVER(ORDER BY RecTime,ID) AS rwnb 
FROM         
   (SELECT
       A.RecTime, X.SQL, X.ID
    FROM 
       dbo.EventView AS A 
    CROSS JOIN
       dbo.Incident AS X
    WHERE      
       (PATINDEX('%' + A.Col + '%', X.SQL) > 0)) AS INC

1 秒和 1600 个节点

第二个视图是：

SELECT     
   D.RecTime, D.Event, D.ID, CAST(CASE WHEN X.[Value] IS NULL THEN 0 ELSE X.[Value] END AS bit) AS Value
FROM         
   dbo.XDependencedEvents AS D 
INNER JOIN
    dbo.EventView AS X ON X.Col = D.Event 
                          AND D.RecTime BETWEEN X.RecTime AND X.ChangedDate

3 秒执行时间和 2100 个节点

，最终视图是

SELECT 
    X.[Rectime], X.[ID], X.[str], D.[Event], D.[Value],X.[rwnb] 
FROM 
    [XDependencedIncidents] AS X
INNER JOIN [XEventStates] AS D ON X.[Rectime] = D.[Rectime]
                                  AND X.[ID] = D.[ID]

8 分钟和 2500 个节点。

我什至使用 RowNumber AS rwnb 来加快视图的处理速度，但它仍然很慢。我必须对前 2 个视图使用 select into 临时表吗？或者我在这里做错了什么？

最终目的是每次“事件”发生变化时获取某些“事件”“组”的值

Answer 1

试试这个：

SELECT X.[Rectime] , X.[ID] , X.[str], D.[Event], D.[Value],X.[rwnb] 
FROM [XDependencedIncidents] AS X
INNER JOIN [XEventStates] AS D ON X.[ID] = D.[ID]
WHERE X.[Rectime] = D.[Rectime]

如果它也很慢。检查XEventStates 索引。

Answer 2

我奇怪的解决方案是使用 RowNumber 来修改第二个和最后一个视图，例如：

SELECT     D.RecTime, D.Event, D.ID
,CAST(CASE WHEN X.[Value] IS NULL THEN 0 ELSE X.[Value] END AS bit) AS Value
,ROW_NUMBER() OVER(ORDER BY D.RecTime,D.ID) AS rwnb 
FROM         dbo.XDependencedEvents AS D 
    INNER JOIN
    dbo.EventView AS X 
        ON X.Col = D.Event 
        AND D.RecTime BETWEEN X.RecTime AND X.ChangedDate

您

SELECT X.[Rectime] , X.[ID] , X.[str], D.[Event], D.[Value],X.[rwnb], D.[rwnb]
FROM [XDependencedIncidents] AS X
INNER JOIN [XEventStates] AS D ON X.[ID] = D.[ID]
AND X.[Rectime] = D.[Rectime]

对这种奇怪的优化方法有何看法？如果有的话，我真的想要一个更好的解决方案。

顺便说一句，是的，添加行号使得 8 分钟 -> 5秒。