Replace OUTER APPLY

Question

我想在我的SQL中替换一些OUTER APPLYs，因为它们似乎有点慢，并且在糟糕的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

查询处理器耗尽了内部资源，无法生成查询计划。这是一个罕见的事件，只有在极其复杂的查询或引用大量表或分区的查询时才会出现。

Answer 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;