Microsoft SQL Server 2008 中 JOIN 与 APPLY 的相对效率

问题描述

我们刚刚开始考虑从 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 的相对效率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！