sql-server - 为什么表值函数的性能比select直接语句好？

Question

我使用AdventureWorks2012并做了测试。我的问题是:为什么 SELECT 语句直接性能低于表值函数。我只将SELECT语句放入表值函数中，表现完全相反。

CREATE FUNCTION [dbo].[atest1]
(
    @iBusinessEntityID  INT
)
RETURNS @t TABLE
(
    [BusinessEntityID]  INT
  , [NationalIDNumber]  NVARCHAR(15)
  , [JobTitle]          NVARCHAR(50)
)
AS
    BEGIN
        INSERT INTO @t
               SELECT 
                   [e].[BusinessEntityID]
                 , [e].[NationalIDNumber]
                 , [e].[JobTitle]
               FROM [HumanResources].[Employee] [e]
               INNER JOIN [Person].[Person] [p]
                    ON [p].[BusinessEntityID] = [e].[BusinessEntityID]
               WHERE [e].[BusinessEntityID] = @iBusinessEntityID;
        RETURN;
    END;

--TEST PERFORMANCE
SELECT 
    *
FROM [dbo].[atest1](5);
GO
SELECT 
    [e].[BusinessEntityID]
  , [e].[NationalIDNumber]
  , [e].[JobTitle]
FROM [HumanResources].[Employee] [e]
INNER JOIN [Person].[Person] [p]
     ON [p].[BusinessEntityID] = [e].[BusinessEntityID]
WHERE [e].[BusinessEntityID] = 5;

Answer 1

这里的问题是，SSMS 中的估计计划经常显示错误的百分比，在 UDF 的情况下，它几乎总是会出错.

成本百分比是该操作与其他操作相比的估计成本，但在 UDF 的情况下，SSMS 不会检查UDF的内部结构。

我在我的服务器上创建了您的 UDF 并向其添加了 GUID 文本，因此我可以轻松返回此 UDF 的计划:

CREATE FUNCTION [dbo].[atest1] (@iBusinessEntityID int)
RETURNS @t TABLE(BusinessEntityID int,NationalIDNumber nvarchar(15),JobTitle nvarchar(50)) AS
BEGIN
INSERT INTO @t /*3C6A985B-748B-44D4-9F76-1A0866342728*/ -- HERE IS MY GUID
SELECT e.BusinessEntityID, e.NationalIDNumber, e.JobTitle
FROM HumanResources.Employee e INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = @iBusinessEntityID
RETURN
END

现在我执行这个函数并以这种方式检索它的计划:

select p.query_plan
from sys.dm_exec_cached_plans cp
     cross apply sys.dm_exec_sql_text(cp.plan_handle) t
     cross apply sys.dm_exec_query_plan(cp.plan_handle) p
where cp.objtype = 'Proc'
      and t.text like '%3C6A985B-748B-44D4-9F76-1A0866342728%'

我检查了这个计划，它和你“直接声明”的计划一模一样。它的 SELECT 部分是相同的，但是表变量中还有 INSERT 以及主计划中的 scan 。所以你可以清楚地看到你的UDF的成本不能再低了，它等于“直接语句”成本加上INSERT成本加上表变量scan成本。

在这种情况下，表很小并且只有一个 UDF 调用，因此您无法注意到执行时间的差异，但是如果您创建一个循环，多次执行“直接语句”并多次调用 UDF，则您会您可能会看到执行时间差异，并且“直接语句”会更快。但 SSMS 无论如何都会坚持 UDF 的成本较低。