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;
The problem here is that the estimated plan in SSMS
often shows wrong percentage, in case of UDF
s 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.