問題描述
在下面的 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模板網!