question

BenjaminShaw-2231 avatar image
0 Votes"
BenjaminShaw-2231 asked BenjaminShaw-2231 commented

OUTER APPLY performance

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

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RyanAbbey-0701 avatar image
0 Votes"
RyanAbbey-0701 answered BenjaminShaw-2231 commented

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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

is that meant to be an "AND s.PaymentDate...

Yes it was meant to be an AND. I have changed the code on above.

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @BenjaminShaw-2231,

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:

115168-outerapply.png

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.


outerapply.png (23.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.