sql-serveruser-defined-functionsinline-functionstable-functions

Why is the performance of table value function better than select direct statement?


a busy cat

I use AdventureWorks2012 and do a test. and my question: Why is SELECT statement directly performance lower than table values function. I only put SELECT statemnt into table value function and Completely opposite performance.

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;

Solution

  • The problem here is that the estimated plan in SSMS often shows wrong percentage, in case of UDFs it almost always does it wrong.

    The cost percentage is the estimated cost of the operation compared to the other operations, but in case of UDF SSMS does not examin the internals of UDF.

    I created your UDF on my server and add to it text a GUID, so I could easily return the plan for this 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
    

    And now I execute this function and retrieve its plan this way:

    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%'
    

    I examined this plan and it's exactly the SAME as the plan of your "direct statement". It's the same in its SELECT part, but then there is also INSERT in the table variable an its scan in the main plan. So you can clearly see that your UDF's cost cannot bee lower, it is equal to "direct statement" cost plus INSERT cost plus table variable scan cost.

    enter image description here

    In this case the tables are small and there is only one UDF call, so you cannot note the difference in execution time, but if you make a cycle where execute your "direct statement" more times and call UDF more times you'll probably see execution time difference, and "direct statement" will be faster. But SSMS will insist on UDF's lower cost anyway.