問題描述

我們剛剛開始考慮從 SQL 2000 遷移到 SQL 2008，并注意到新的 CROSS APPLY 和 INNER APPLY 語法允許對表值參數(shù)化 UDF 或相關(guān)子查詢進(jìn)行連接"形式.

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.

顯然，能夠?qū)⑦壿嫹庋b在 UDF 中并能夠在不同的查詢中重用它會很好，但我確信該功能是有代價(jià)的.

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.

我已經(jīng)在網(wǎng)上環(huán)顧了很多，但我找不到任何性能指標(biāo)表明在使用基于 APPLY 的查詢時(shí)您會受到多大的性能影響，而如果您內(nèi)聯(lián)相同的查詢可能會得到什么查詢.

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.

我知道確切的影響將在很大程度上取決于特定的架構(gòu)和查詢，但我很好奇是否有人有任何調(diào)整現(xiàn)實(shí)世界系統(tǒng)的經(jīng)驗(yàn)來分享這方面的經(jīng)驗(yàn).

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.如果必須這樣做，強(qiáng)制逐行處理而不是循環(huán)處理很有用.

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

奇怪的是，在一個(gè)地方它更有效(使用探查器查看讀取時(shí))，因?yàn)閮?yōu)化器將 UDF 視為黑盒并根據(jù)需要應(yīng)用過濾器.

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 是一個(gè)內(nèi)聯(lián)表值函數(shù)，當(dāng)在 JOIN 中擴(kuò)展/取消嵌套時(shí)，情況會更糟，因?yàn)閮?yōu)化器將查詢視為一個(gè)整體并在不同的位置應(yīng)用過濾器.

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.

否則，我會謹(jǐn)慎使用它或在不經(jīng)常運(yùn)行的代碼上使用它......并驗(yàn)證它不會殺死非常糟糕.我接受命中以獲得可維護(hù)性.

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.

題外話:封裝在數(shù)據(jù)庫中只能走這么遠(yuǎn):在某些時(shí)候你會失去基于集合的優(yōu)勢.

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

這篇關(guān)于Microsoft SQL Server 2008 中 JOIN 與 APPLY 的相對效率的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！