問題描述

這個(gè)問題源于SQLServer:為什么避免使用表- 有價(jià)值的用戶定義函數(shù)?.我開始在一些評(píng)論中提出問題，對(duì)我評(píng)論的回復(fù)偏離了主題.

This question grew out of SQLServer: Why avoid Table-Valued User Defined Functions?. I began asking questions in some of the comments, and the replies to my comments moved off topic.

這樣您就不必閱讀整個(gè)討論:我從未聽說過用戶定義函數(shù) (UDF) 很慢，或者應(yīng)該避免使用.上面引用的問題中發(fā)布了一些鏈接，以說明它們很慢.我還是沒明白，求個(gè)例子.貼了個(gè)例子，性能差別很大.

So that you don't have to read the entire discussion: I had never heard it said that user defined functions (UDF) were slow, or to be avoided. Some links were posted in the question referenced above to illustrate that they were slow. I still didn't get it, and asked for an example. An example was posted, and the performance difference was huge.

我不可能是唯一沒有意識(shí)到可能存在如此大的性能差異的人.我覺得這個(gè)事實(shí)應(yīng)該分成一個(gè)新的問題和答案，以提高被發(fā)現(xiàn)的機(jī)會(huì).這就是問題".請(qǐng)不要關(guān)閉，因?yàn)槲蚁虢o回答者時(shí)間來發(fā)布答案.

I can't be the only person who did not realize there could be such a large performance difference. I felt this fact should be separated into a new question and answer, to improve its chances of being found. This here is the "question". Please don't close yet, as I'd like to give the answerer time to post the answer.

當(dāng)然，其他人也應(yīng)該發(fā)布答案或示例.我特別感謝任何能幫助我理解為什么性能差異如此巨大的東西.

Of course, others should also post answers or examples, as well. I'd especially appreciate anything that would help me understand why the performance difference is so huge.

另請(qǐng)注意，我不是在談?wù)撛?WHERE 子句中使用 UDF.我知道這會(huì)如何阻止優(yōu)化器完成其工作.當(dāng)原始 UDF 是 SELECT 列列表的一部分時(shí)，我對(duì)性能差異特別感興趣.

Note also that I'm not talking about the use of a UDF in a WHERE clause. I'm aware of how this can prevent the optimizer from doing its job. I'm specifically interested in differences in performance when the original UDF was part of the SELECT column list.

推薦答案

為了進(jìn)行基準(zhǔn)測(cè)試，讓我們創(chuàng)建一個(gè) 100 萬行的表:

For benchmarking let's create a table with 1M rows:

CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY)
GO
DECLARE @i INT;
SET @i = 1;
INSERT INTO dbo.Numbers(n) SELECT 1;
WHILE @i<1024000 BEGIN
  INSERT INTO dbo.Numbers(n)
    SELECT n + @i FROM dbo.Numbers;
  SET @i = @i * 2;
END;
GO

運(yùn)行簡(jiǎn)單的內(nèi)聯(lián)添加:

SELECT COUNT(*) FROM(
SELECT n,n+1 AS ValuePlusOne
FROM  dbo.Numbers
) AS t WHERE ValuePlusOne>0

   CPU time = 15 ms, elapsed time = 122 ms.

(1 row(s) affected)
Table 'Numbers'. Scan count 1, logical reads 3521, physical reads 3, read-ahead reads 3498, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 406 ms,  elapsed time = 951 ms.

創(chuàng)建一個(gè)標(biāo)量 UDF，將一個(gè)整數(shù)加一，并運(yùn)行 100 萬次:

Create a scalar UDF that just adds one to an integer, and run it 1M times:

CREATE FUNCTION dbo.[AddOne] 
(
        @value int
)
RETURNS int
AS
BEGIN
        DECLARE @Result int
        SELECT @Result = @value + 1
        RETURN @Result
END
GO

SELECT COUNT(*) FROM(
SELECT n,dbo.AddOne(n) AS ValuePlusOne
FROM  dbo.Numbers
) AS t WHERE ValuePlusOne>0

   CPU time = 15 ms, elapsed time = 122 ms.

(1 row(s) affected)
Table 'Numbers'. Scan count 1, logical reads 3521, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 108313 ms,  elapsed time = 295072 ms.

創(chuàng)建一個(gè)內(nèi)聯(lián) UDF，它和添加一樣快，并運(yùn)行 100 萬次:

Create an inline UDF, which is just as fast as just adding, and run that 1M times:

CREATE FUNCTION dbo.[AddOneInline] 
(
        @value int
)
RETURNS TABLE
AS
RETURN(SELECT @value + 1 AS ValuePlusOne)
GO

SELECT COUNT(*) FROM(
SELECT ValuePlusOne
FROM  dbo.Numbers
CROSS APPLY dbo.[AddOneInline](n)
) AS t WHERE ValuePlusOne>0

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 35 ms.

(1 row(s) affected)
Table 'Numbers'. Scan count 1, logical reads 3521, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 391 ms,  elapsed time = 403 ms.

標(biāo)量 UDF 與內(nèi)聯(lián) UDF 的性能差異很明顯.

The difference in performance of a scalar UDF vs. an inline one is obvious.

這篇關(guān)于內(nèi)聯(lián)表值 UDF 能否勝過 SELECT 列列表中的等效標(biāo)量 UDF?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！