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

Question

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;

Answer 1

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.

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.

Answer 2

Normally Functions behave worse than direct queries but it is possible that on this case, as for something predefined as a function the system stores a better plan. On this case it looks like for the Function the system is doing a Table Scan what sometimes on small tables that is the case for the AdventureWorks DB it may work better than searching by index.

Also, on your example there is only one call to the function. What decreases the performance for Functions is especially when are scalar functions (the example provided is for a table function) when you call it repetitively inside a query.