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

Question

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;

What do you mean talking about performance? Is it execution time or reads count or something? — Denis Rubashkin, Jan 08 '19 at 08:16
I'm talking about query cost. I expect results SELECT statement directly will be better than table value function — rony Tran, Jan 08 '19 at 08:18
I suppose it could be because of different cardinality estimation for the function and the direct query. Your query is run for the value '5' whereas the query inside your function for unknown value that's why different plans could be produced. Try to add a query hint OPTION(OPTIMIZE FOR ( @iBusinessEntityID = 5)) to see a more accurate comparison. — Denis Rubashkin, Jan 08 '19 at 08:33

score 8 · Accepted Answer · answered Jan 08 '19 at 09:40

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.

score 2 · Answer 2 · answered Jan 08 '19 at 08:25

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.

answered Jan 08 '19 at 08:25

Angel M.

1,360
8
17

>>>On this case it looks like for the Function the system is doing a Table Scan<<< Table scan of what? Of table variable created in function? And in order to FILL IN this table variable what the function does? – sepupic Jan 08 '19 at 09:09
1

Table scan means the system is not using indexes but reading the table and making the JOIN and FILTERS in memory. – Angel M. Jan 08 '19 at 09:11
1

@ Angel M. You misunderstood my question. I did not ask you what scan means, I asked you the scan of WHICH OBJECT do you see in the plan? And how it's related to original tables HumanResources.Employee and Person.Person. I want to say, both of them was SEEKED and not scanned in case of UDF – sepupic Jan 08 '19 at 09:16
1

To answer that I should reproduce exactly the same execution plan but I have not available here the AdventureWorks DB – Angel M. Jan 08 '19 at 09:22
1

I already reproduced it and can assure you, it performs index seek in both cases. The fact is that it's impossible to see it from the plan attached to this post. So the scan you see has no importance in this case – sepupic Jan 08 '19 at 09:23

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

2 Answers2