OUTER APPLY performance

Question

Hi,

I have an OUTER APPLY in my query.

When I run the query without including the OUTER APPLY columns in the main SELECT the performance is good.

For example, in following query I am not including any of the columns from X.

SELECT U.UserID,
U.FirstName
FROM Users U
OUTER APPLY (SELECT TOP 1
Salary,
Currency,
Interest
FROM Salary S
WHERE S.UserID = U.UserID
AND S.PaymentDate = U.StartDate) X

When I include even a single column from the OUTER APPLY the performance slows down.

SELECT U.UserID,
U.FirstName,
X.Salary
FROM Users U
OUTER APPLY (SELECT TOP 1
Salary,
Currency,
Interest
FROM Salary S
WHERE S.UserID = U.UserID
AND S.PaymentDate = U.StartDate) X

Does anybody have an idea why this might be happening?

Thanks,

Ben

Answer 1

It slows down because it's actually having to do the work... the first query will be ignoring the calculation since it knows it has no impact on the output

When you say "slows down", is it a significant slowdown?

is that meant to be an "AND s.PaymentDate..."? Or is there some code missing? Are you expecting more than 1 record per user/payment date combination?

Answer 2

As Ryan says, in the first query, SQL Server simply ignores the OUTER APPLY because it has no logical effect on the query whatsoever.

But as soon as you add a column, SQL Server has to logically run that subquery for every row in the Users table. No surprise that it is taking longer time. How much it slows down depends. It certainly helps if there is an index on (UserID, PaymentDate) on the Salary table, it may not be sufficient. SQL Server sometimes outsmarts itself when TOP 1 is present.

Answer 3

Hi @Benjamin Shaw ,

Agreed with other experts, your first query

SELECT U.UserID,  
U.FirstName  
FROM Users U  
OUTER APPLY (SELECT TOP 1  
Salary,  
Currency,  
Interest  
FROM Salary S  
WHERE S.UserID = U.UserID  
WHERE S.PaymentDate = U.StartDate) X  

is equal to below query since no column is from the OUTER APPLY part.

    SELECT U.UserID,  
    U.FirstName  
    FROM Users U  

The specific form of OUTER APPLY ( SELECT TOP ... ) is most likely to result in a correlated nested loops join in current versions of SQL Server, because the optimizer does not contain logic to transform this pattern to an equivalent JOIN.

Please refer below execution plan of my example:

In the second query, I added one column and it had extra nested loops, compute and table scan. So in the second query, the query cost was much larger.

In addition, in order to improve your query, you could have a try to create indexes and update statistics.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.