OUTER APPLY(SELECT TOP 1) pattern is widespread, even baked into some frameworks like Entity Framework. But is it a good practice? This demo will show. Based on a real-world optimization problem and solved with great success.

The demo shows it can produce a very bad performing queries, as it produces “nested loops” which is not optimal for bigger number of rows/iterations. For more rows much more efficient is merge or hash join. Also order of join is set in code, while much better is to give optimizer freedom to choose the order. Using outer apply top 1  “anti-pattern” everywhere is a bad practice. Alternatives like grouping and windowing functions often perform much much better, as optimizer can choose other join types, it is not limited to just “nested loops”, and can choose different join order. Demo shows how giving optimizer freedom and avoiding this anti-pattern can result in 100x or 1000x faster performance. Example is from real-life situation, where I optimized a long running query from 7h down to around 2minutes.

Entire code from the demo is here:


2 Comments on “How NOT to use CROSS APPLY

  1. Thanks Vedran for this article.

    From my experience, I also encountered many situations where CROSS APPLY is not the best solution because as you said leads to nested loop (with outer references) which is not fit well for all scenarios.

    The second solution is also interesting with (dummy) CCI batch mode (in some degrees and depending on the version you’re using of course) with ROW_NUMBER() PARTITION BY and corresponding Windows Aggregate operator. Batch mode has good chance to be propagated from right to left of the execution plan as well.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.