Replace OUTER APPLY

Question

I want to replace some OUTER APPLYs in my SQL because they seem to be a little bit slow and eating resources () on a poor VPS. I have no idea what to use instead? LEFT OUTER JOIN (??)

Here's my code

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

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions.

Answer 1

You can add a ROW_NUMBER to your subquery (and remove the TOP 1). Then you can use a LEFT JOIN.

Something like this:

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;