Usage of CROSS APPLY

Question

Usage of CROSS APPLY

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

If you are using the output of an APPLY block as the input for another then you are likely to find a performance hit after x "levels of nesting". x will depend on what you're doing but with the simplest of cases I've usually found it to be 6 or 7.

http://www.sqlservercentral.com/articles/T-SQL/97545/%5B/url%5D

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

Do not expect this use of Apply to reduce the number of calculations the optimizer puts in the plan. It might, but it often does not.

However, those computations are incredibly cheap in relation to the other things SQL Server has to do when performing a query, so I would not worry about it.

The main benefits of using Apply for this are increased simplicity of the query and easier future maintenance. And that's worth a billion times more than the tiny millisecond performance difference that this might or might not have.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/