問題描述
我有一個(gè)相當(dāng)大的查詢,需要在幾個(gè)存儲(chǔ)過程中使用,我想將其轉(zhuǎn)換為 UDF 以使其更易于維護(hù)(視圖不起作用,這需要一堆參數(shù)),但是我曾經(jīng)與之交談過的每個(gè)人都告訴我 UDF 的速度非常慢.
I have a rather huge query that is needed in several stored procedures, and I'd like to shift it into a UDF to make it easier to maintain (A view won't work, this takes in a bunch of parameters), however everyone I've ever talked to has told me that UDF's are incredibly slow.
雖然我不知道究竟是什么讓它們變慢,但我會(huì)猜測(cè)它們是,但看到我沒有在連接中使用這個(gè) UDF,而是返回一個(gè)表變量,我認(rèn)為它不會(huì)那么糟糕.
While I don't know what exactly makes them slow, I'm will to guess that they are, but seeing as I'm not using this UDF within a join, but instead to return a table variable, I think it wouldn't be that bad.
所以我想問題是,我應(yīng)該不惜一切代價(jià)避免使用 UDF 嗎?任何人都可以指出具體的證據(jù)表明它們更慢嗎?
So I guess the question is, should I avoid UDFs at all cost? Can anyone point to concrete evidence stating that they are slower?
推薦答案
標(biāo)量 UDF 非常慢,內(nèi)聯(lián) UDF 實(shí)際上是宏,因此它們非常快:幾篇文章:
Scalar UDFs are very slow, inline UDFs are in fact macros, as such they are very fast: A few articles:
使用表值 UDF 重用您的代碼
許多嵌套的內(nèi)聯(lián) UDF 非常快
有關(guān)標(biāo)量 UDF 緩慢的更多鏈接:
More links on slowness of scalar UDFs:
帶有日期時(shí)間的 UDF 的 SQL Server 性能模式參數(shù)
并非所有 UDF 都對(duì)性能不利
這篇關(guān)于SQLServer:為什么要避免使用表值用戶定義函數(shù)?的文章就介紹到這了,希望我們推薦的答案對(duì)大家有所幫助,也希望大家多多支持html5模板網(wǎng)!