問題描述

在下面的 t-sql 語句中，dbo.FUNC 函數會被調用多少次?

In the following t-sql statement, how many times will the dbo.FUNC function get called?

SELECT
    column1,
    column2,
    dbo.FUNC(column3) AS column3
FROM table1
WHERE dbo.FUNC(column3) >= 5
ORDER BY dbo.FUNC(column3) DESC

它會在每行中多次單獨調用，還是優化器識別出它在單個語句中被多次使用，并且只調用一次?

Will it called multiple separate times per row, or does the optimizer recognize that it is being used multiple times in a single statement, and only call it once?

我該如何測試?我無法插入到函數內部的表中，因此遞增計數器不起作用...

How can I test this? I can't insert into a table inside of a function, so incrementing a counter wont work...

推薦答案

這不能保證.

您需要檢查執行計劃才能找到答案.一些例子.

You would need to check the execution plan to find out. Some examples.

CREATE FUNCTION dbo.FUNC1(@p1 int)
RETURNS int
AS
BEGIN
    RETURN @p1 + 1
END

GO

CREATE FUNCTION dbo.FUNC2(@p1 int)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
    RETURN @p1 + 1
END

GO
SELECT 
       OBJECTPROPERTYEX(OBJECT_ID('dbo.FUNC1'), 'IsDeterministic'),
       OBJECTPROPERTYEX(OBJECT_ID('dbo.FUNC2'), 'IsDeterministic') 
GO

FUNC2 創建 WITH SCHEMABINDING 并被視為確定性的.FUNC1 不是.

FUNC2 is created WITH SCHEMABINDING and is treated as deterministic. FUNC1 isn't.

SELECT
    dbo.FUNC1(number) AS FUNC1,
    dbo.FUNC2(number) AS FUNC2
FROM master..spt_values
WHERE dbo.FUNC1(number) >= 5 AND dbo.FUNC2(number) >= 5
ORDER BY dbo.FUNC1(number), dbo.FUNC2(number)

提供計劃

  |--Sort(ORDER BY:([Expr1003] ASC, [Expr1004] ASC))
       |--Compute Scalar(DEFINE:([Expr1003]=[test].[dbo].[FUNC1]([master].[dbo].[spt_values].[number])))
            |--Filter(WHERE:([test].[dbo].[FUNC1]([master].[dbo].[spt_values].[number])>=(5) AND [Expr1004]>=(5)))
                 |--Compute Scalar(DEFINE:([Expr1004]=[test].[dbo].[FUNC2]([master].[dbo].[spt_values].[number])))
                      |--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]))

FUNC1 被評估兩次(一次在過濾器中，一次在計算標量中輸出用于投影和排序的計算列)，FUNC2 只被評估一次.

FUNC1 is evaluated twice (once in the filter and once in a compute scalar outputting a calculated column used for both the projection and the ordering), FUNC2 is only evaluated once.

重寫為

SELECT
    FUNC1,
    FUNC2
FROM master..spt_values
CROSS APPLY (SELECT dbo.FUNC1(number), dbo.FUNC2(number)) C(FUNC1, FUNC2)
WHERE FUNC1 >= 5 AND FUNC2 >= 5
ORDER BY FUNC1, FUNC2

稍微改變計劃，兩者都只評估一次

Changes the plan slightly and both are only evaluated once

  |--Sort(ORDER BY:([Expr1003] ASC, [Expr1004] ASC))
       |--Filter(WHERE:([Expr1003]>=(5)))
            |--Compute Scalar(DEFINE:([Expr1003]=[test].[dbo].[FUNC1]([master].[dbo].[spt_values].[number])))
                 |--Filter(WHERE:([Expr1004]>=(5)))
                      |--Compute Scalar(DEFINE:([Expr1004]=[test].[dbo].[FUNC2]([master].[dbo].[spt_values].[number])))
                           |--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]))

現在對查詢稍作改動

SELECT
    FUNC1 + 10,
    FUNC2 + 10
FROM master..spt_values
CROSS APPLY (SELECT dbo.FUNC1(number), dbo.FUNC2(number)) C(FUNC1, FUNC2)
WHERE FUNC1 >= 5 AND FUNC2 >= 5
ORDER BY FUNC1, FUNC2

給出與原始結果相反的結果，即 FUNC2 計算兩次，而 FUNC1 只計算一次.

Gives the opposite of the original result in that FUNC2 is evaluated twice but FUNC1 only once.

  |--Compute Scalar(DEFINE:([Expr1005]=[Expr1003]+(10)))
       |--Sort(ORDER BY:([Expr1003] ASC, [Expr1004] ASC))
            |--Filter(WHERE:([Expr1003]>=(5)))
                 |--Compute Scalar(DEFINE:([Expr1003]=[test].[dbo].[FUNC1]([master].[dbo].[spt_values].[number])))
                      |--Filter(WHERE:([Expr1004]>=(5)))
                           |--Compute Scalar(DEFINE:([Expr1004]=[test].[dbo].[FUNC2]([master].[dbo].[spt_values].[number]), [Expr1006]=[test].[dbo].[FUNC2]([master].[dbo].[spt_values].[number])+(10)))
                                |--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]))

這篇關于當在一個語句中多次調用同一個 UDF 時，它會被調用多少次?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！