Microsoft SQL Server 2008中JOIN与APPLY的相对效率 [英] Relative Efficiency of JOIN vs APPLY in Microsoft SQL Server 2008

问题描述

我们只是开始考虑从SQL 2000升级到SQL 2008，并且注意到新的CROSS APPLY和INNER APPLY语法允许针对表值参数化UDF或相关子查询进行联接".

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.

很明显，能够将逻辑封装在UDF中并能够在不同的查询中重用它是很好的，但是我敢肯定，功能是有代价的.

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.

我已经在网上四处查看，但是找不到任何性能指标来表明如果使用基于APPLY的查询，则如果对内联相同的查询可能会获得多少性能影响查询.

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.

推荐答案

我在某些地方使用APPLY.如果必须这样做，强制逐行处理而不是循环是很有用的.

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

奇怪的是，在一个地方，它的效率更高(使用探查器查看读取数据时)，因为优化器将UDF视为黑匣子，并根据需要应用了过滤器.

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.

udf是一个内联表值函数，在JOIN中展开/取消嵌套时，此函数更糟，因为优化器将查询整体视为一个查询，并将过滤器应用于其他位置.

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.

这篇关于Microsoft SQL Server 2008中JOIN与APPLY的相对效率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！