Relative Efficiency of JOIN vs APPLY in Microsoft SQL Server 2008

Question

We're just starting to look at moving to SQL 2008 from SQL 2000 and are noting the new CROSS APPLY and INNER APPLY syntax that allows a form of 'joining' against either table-valued parametrized UDFs or correlated subqueries.

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

I've looked around on the Net quite a bit but I can't find any performance metrics that indicate how much of a performance hit you would take when using APPLY-based queries over what you might get if you inlined the same query.

I know the exact impacts will depend heavily on the specific schema and queries, but I was curious if anyone has any experience from tuning real-world systems to share on this.

Answer 1

I use APPLY in places. It's useful to force row by row processing instead of a loop, if you have to do it.

Oddly, in one place it ws more efficient (when using profiler to view reads) because the optimiser treated the UDF as a black box and applied the filter as I wanted.

The udf is an inline table valued function that when expanded/unnested in a JOIN was worse because the optmiser looked at the query as a whole and applied the filter ina different place.

Otherwise, I use it sparingly or on code that does not run often... and verify that it does not kill really bad. I accept the hit to gain maintainability.

Off topic: encapsulation only goes so far in databases: at some point you lose the set based advantage.