Relative performance of JOIN vs APPLY in Microsoft SQL Server 2008

Question

We are just starting to look at the transition to SQL 2008 from SQL 2000 and we are celebrating the new CROSS APPLY and INNER APPLY syntax, which allows the form to “join” to parameterized parameterized UDF parameters or correlated subqueries. p>

Obviously, it would be nice to be able to encapsulate the logic in UDF and be able to reuse it in different requests, but I'm sure the functionality comes with a cost.

I looked a little on the Web, but I can’t find a single performance indicator that shows how much of your performance can affect you when using APPLY-based queries compared to what you could get if you entered the same request.

I know that the exact impact will largely depend on the particular circuit and queries, but I was curious if anyone had experience setting up real systems to share this.

Answer 1

I use APPLY in places. This is useful for forcing line by line instead of loop if you need to do this.

Oddly enough, in one place it is more efficient (when using the profiler to view reading), because the optimizer processed UDF as a black box and applied the filter the way I wanted.

udf , / JOIN , optmiser .

, ... , . , .

: : - .