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.

A `LEFT JOIN` won't be able to replace the above; you can't correlate a `LEFT JOIN` (you have to use an `ON`). You also have a `TOP` with an `ORDER BY`; that has a "smell" to it. This means that the data engine is free to return what ever arbitrary row(s) it wants, and the row(s) could be different every time you run said query. If you are using `TOP` you need to ensure the query has an `ORDER BY` so that you get consistent and reliable results. If your query is slow, the you should be looking to improve its performance. You do, for example, have indexes to help the data engine, right? — Thom A, Jul 11 '22 at 09:05
If you are having performance issues you need to investigate them, not assume that a particular construct is the cause of them. SQL is a declarative language, so you are describing the result you want, not telling the engine how to get it — Dale K, Jul 11 '22 at 09:38
"query processor ran out of internal resources" is actually an unusual error. It's a sign of a bug in the compiler, or possibly due to a very deeply nested view or function call, or possibly a heavily partitioned table. Which exact version of SQL Server `select @@VERSION` — Charlieface, Jul 11 '22 at 09:44
In addition to the comments above - you are referencing the Event table twice. It isn't needed here since all you are looking for is the latest (assuming it is the latest) history. I am also assuming that you want additional columns from the Event table in the outer query but that isn't clear. If you don't need other columns you could convert the inner query to use a row_number and just filter that for the actual results. — Jeff, Jul 11 '22 at 19:42

score 1 · Accepted Answer · answered Jul 11 '22 at 09:12

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;

Replace OUTER APPLY

1 Answers1