How can I work around SQL Server - Inline Table Value Function execution plan variation based on par
- by Ovidiu Pacurar
Here is the situation:
I have a table value function with a datetime parameter ,lest's say tdf(p_date) ,
that filters about two million rows selecting those with column date smaller than p_date and computes some aggregate values on other columns.
It works great but if p_date is a custom scalar value function (returning the end of day in my case) the execution plan is altered an the query goes from 1 sec to 1 minute execution time.
A proof of concept table - 1K products, 2M rows:
CREATE TABLE [dbo].[POC](
[Date] [datetime] NOT NULL,
[idProduct] [int] NOT NULL,
[Quantity] [int] NOT NULL
) ON [PRIMARY]
The inline table value function:
CREATE FUNCTION tdf (@p_date datetime)
RETURNS TABLE
AS
RETURN
(
SELECT idProduct, SUM(Quantity) AS TotalQuantity,
max(Date) as LastDate
FROM POC
WHERE (Date < @p_date)
GROUP BY idProduct
)
The scalar value function:
CREATE FUNCTION [dbo].[EndOfDay] (@date datetime)
RETURNS datetime
AS
BEGIN
DECLARE @res datetime
SET @res=dateadd(second, -1,
dateadd(day, 1,
dateadd(ms, -datepart(ms, @date),
dateadd(ss, -datepart(ss, @date),
dateadd(mi,- datepart(mi,@date),
dateadd(hh, -datepart(hh, @date), @date))))))
RETURN @res
END
Query 1 - Working great
SELECT * FROM [dbo].[tdf] (getdate())
The end of execution plan:
Stream Aggregate Cost 13% <--- Clustered Index Scan Cost 86%
Query 2 - Not so great
SELECT * FROM [dbo].[tdf] (dbo.EndOfDay(getdate()))
The end of execution plan:
Stream Aggregate Cost 4% <--- Filter Cost 12% <--- Clustered Index Scan Cost 86%
