更换外部应用

一尘不染

更换外部应用

sql

我想在我的 SQL 中替换一些 OUTER APPLY，因为它们似乎有点慢并且在糟糕的 VPS 上吃资源（）。我不知道用什么代替？左外连接 (??)

这是我的代码

SELECT e.Id,
    Decision.Comment,
    Decision.DATE,
    Decision.IsRejected,
    Decision.CommentedBy
FROM core.Event e
OUTER APPLY (
    SELECT TOP 1 ESH.Event_StatusHistory_Comment [Comment],
        ESH.Event_StatusHistory_Date [Date],
        ESH.Event_StatusHistory_IsRejected [IsRejected],
        U.[Name] [CommentedBy]
    FROM core.[Event] e2
    JOIN core.Event_StatusHistory ESH
        ON ESH.EventId = e2.Id
    JOIN core.[User] U
        ON ESH.Event_StatusHistory_UserId = U.Id
    WHERE e2.ID = e.Id
    ) Decision

查询处理器用尽了内部资源，无法生成查询计划。这是一个罕见的事件，仅适用于极其复杂的查询或引用大量表或分区的查询。

阅读 96

2022-07-21

共1个答案

一尘不染

您可以将 ROW_NUMBER 添加到子查询（并删除 TOP 1）。然后，您可以使用 LEFT JOIN。

像这样的东西：

SELECT e.Id,
    Decision.Comment,
    Decision.DATE,
    Decision.IsRejected,
    Decision.CommentedBy
FROM core.Event e
LEFT JOIN (
    SELECT ESH.Event_StatusHistory_Comment [Comment],
        ESH.Event_StatusHistory_Date [Date],
        ESH.Event_StatusHistory_IsRejected [IsRejected],
        U.[Name] [CommentedBy],
        ROW_NUMBER() OVER (PARTITON BY  e2.ID  ORDER BY ESH.Event_StatusHistory_Date) as RN
    FROM core.[Event] e2
    JOIN core.Event_StatusHistory ESH
        ON ESH.EventId = e2.Id
    JOIN core.[User] U
        ON ESH.Event_StatusHistory_UserId = U.Id    
    ) Decision
        ON e.id = Decision.id 
        AND Decision.RN = 1;

2022-07-21