June 12, 2015 at 1:59 pm
I am trying to run an inline TVF as a parameterized SQL query.
When I run the following query in SSMS, it takes 2-3 seconds
select * from dbo.history('2/1/15','1/1/15','1/31/15',2,2021,default)
I was able to capture the following query through SQL profiler (parameterized, as generated by Entity framework) and run it in SSMS.
exec sp_executesql N'select * from dbo.history(@First,@DatedStart,@DatedEnd,@Number,@Year,default)',N'@First date,@DatedStart date,@DatedEnd date,@Year int,@Number float',@First='2015-02-01',@DatedStart='2015-01-01',@DatedEnd='2015-01-31',@Year=2021,@Number=2
Running the above query in SSMS takes 1:08 which is around 30x longer than the non parameterized version.
I have tried adding option(recompile) to the end of the parameterized query, but it did absolutely nothing as far as performance. This is clearly an indexing or parameter sniffing issue, but I have no idea how to resolve it.
When looking at the execution plan, it appears that the parameterized version mostly gets mostly hung up on an *Eager Spool (46%)* and then a *Clustered Index scan (30%)* which are not present in the execution plan without parameters.
Perhaps there is something I am missing, can someone please point me in the right direction as to how I can get this parameterized query to work properly?
June 12, 2015 at 2:26 pm
Would help to see the DDL for the TVF, the table(s) involved, and the indexes defined.
On the first query where the data values are hardcoded you are getting key lookups from Object3.Index3 and an index seek on Object3.Index2. The parameterized plan has a clustered index scan on Object3.Index2. The scan is returning 10,000,000 + rows where the lookup and seek return 47,000+ and 29,000 + each.
June 12, 2015 at 2:37 pm
phosplait (6/12/2015)
I am trying to run an inline TVF as a parameterized SQL query.
Just to be sure, does the function code contain the word BEGIN anywhere in it?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2015 at 2:40 pm
Jeff Moden (6/12/2015)
phosplait (6/12/2015)
I am trying to run an inline TVF as a parameterized SQL query.Just to be sure, does the function code contain the word BEGIN anywhere in it?
One of the things I was wondering, but there are a few other things to consider as well.
June 12, 2015 at 2:52 pm
@jeff, there is no begin in the function. It is an inline function that does a cross apply with another inline function onto a range of dates
@Lynn, I would have to obfuscate the DDL and functions somewhat to post them here so I'd rather not do that unless I have to but here is the outer function that I am trying to run in the query
CREATE FUNCTION [dbo].[history] (
@First DATE,
@DatedStart DATE = '6/1/2014',
@DatedEnd DATE = NULL,
@Number FLOAT,
@Year INT,
@Sub INT = 5000000
)
RETURNS TABLE
AS
RETURN (
WITH N0 AS (
SELECT 1 AS n
UNION ALL
SELECT 1
),
N1 AS (
SELECT 1 AS n
FROM N0 t1,
N0 t2
),
N2 AS (
SELECT 1 AS n
FROM N1 t1,
N1 t2
),
N3 AS (
SELECT 1 AS n
FROM N2 t1,
N2 t2
),
N4 AS (
SELECT 1 AS n
FROM N3 t1,
N3 t2
),
N5 AS (
SELECT 1 AS n
FROM N4 t1,
N4 t2
),
N6 AS (
SELECT 1 AS n
FROM N5 t1,
N5 t2
),
nums AS (
SELECT ROW_NUMBER() OVER (
ORDER BY (
SELECT 1
)
) AS num
FROM N6
)
SELECT DATEADD(day, num - 1, @Sub) AS thedate,
*
FROM nums
OUTER APPLY (
SELECT count(*) AS objects,
sum(remaining) AS sumremaining,
sum(size) AS totalsize
FROM dbo.SubQuery(DATEADD(day, num - 1, @First), @DatedStart, @DatedEnd, @Number, @Year, @Sub)
GROUP BY [Number],
[year]
) thetable
WHERE num <= DATEDIFF(day, @First, GETDATE()) + 1
)
June 12, 2015 at 2:58 pm
phosplait (6/12/2015)
@Jeff, there is no begin in the function. It is an inline function that does a cross apply with another inline function onto a range of dates@Lynn, I would have to obfuscate the DDL and functions somewhat to post them here so I'd rather not do that unless I have to but here is the outer function that I am trying to run in the query
CREATE FUNCTION [dbo].[history] (@First DATE,
@DatedStart DATE = '6/1/2014',
@DatedEnd DATE = NULL,
@Number FLOAT,
@Year INT,
@Sub INT = 5000000
)
RETURNS TABLE
AS
RETURN (
WITH N0 AS (
SELECT 1 AS n
UNION ALL
SELECT 1
),
N1 AS (
SELECT 1 AS n
FROM N0 t1,
N0 t2
),
N2 AS (
SELECT 1 AS n
FROM N1 t1,
N1 t2
),
N3 AS (
SELECT 1 AS n
FROM N2 t1,
N2 t2
),
N4 AS (
SELECT 1 AS n
FROM N3 t1,
N3 t2
),
N5 AS (
SELECT 1 AS n
FROM N4 t1,
N4 t2
),
N6 AS (
SELECT 1 AS n
FROM N5 t1,
N5 t2
),
nums AS (
SELECT ROW_NUMBER() OVER (
ORDER BY (
SELECT 1
)
) AS num
FROM N6
)
SELECT DATEADD(day, num - 1, @Sub) AS thedate,
*
FROM nums
OUTER APPLY (
SELECT count(*) AS objects,
sum(remaining) AS sumremaining,
sum(size) AS totalsize
FROM dbo.SubQuery(DATEADD(day, num - 1, @First), @DatedStart, @DatedEnd, @Number, @Year, @Sub)
GROUP BY [Number],
[year]
) thetable
WHERE num <= DATEDIFF(day, @First, GETDATE()) + 1
)
Unfortunately it doesn't really help as it looks like the piece that needs work is this: dbo.SubQuery(DATEADD(day, num - 1, @First), @DatedStart, @Date.
June 12, 2015 at 4:49 pm
phosplait (6/12/2015)
@Jeff, there is no begin in the function. It is an inline function that does a cross apply with another inline function onto a range of dates
Just had to be sure. A lot of good folks don't know the difference.
I can definitely see the difference between the two execution plans for Object3. I'm not sure which is worse.... the table scan or the eager spool. You might be able to get rid of the eager spool by adding WITH SCHEMABINDING to both functions but it also looks like a little parameter sniffing going on. You might try the trick of encapsulating the input variables in a "parms" CTE to try to overcome the sniffing, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2015 at 6:02 pm
phosplait (6/12/2015)
@Jeff, there is no begin in the function. It is an inline function that does a cross apply with another inline function onto a range of dates@Lynn, I would have to obfuscate the DDL and functions somewhat to post them here so I'd rather not do that unless I have to but here is the outer function that I am trying to run in the query
CREATE FUNCTION [dbo].[history] (@First DATE,
@DatedStart DATE = '6/1/2014',
@DatedEnd DATE = NULL,
@Number FLOAT,
@Year INT,
@Sub INT = 5000000
)
RETURNS TABLE
AS
RETURN (
WITH N0 AS (
SELECT 1 AS n
UNION ALL
SELECT 1
),
N1 AS (
SELECT 1 AS n
FROM N0 t1,
N0 t2
),
N2 AS (
SELECT 1 AS n
FROM N1 t1,
N1 t2
),
N3 AS (
SELECT 1 AS n
FROM N2 t1,
N2 t2
),
N4 AS (
SELECT 1 AS n
FROM N3 t1,
N3 t2
),
N5 AS (
SELECT 1 AS n
FROM N4 t1,
N4 t2
),
N6 AS (
SELECT 1 AS n
FROM N5 t1,
N5 t2
),
nums AS (
SELECT ROW_NUMBER() OVER (
ORDER BY (
SELECT 1
)
) AS num
FROM N6
)
SELECT DATEADD(day, num - 1, @Sub) AS thedate,
*
FROM nums
OUTER APPLY (
SELECT count(*) AS objects,
sum(remaining) AS sumremaining,
sum(size) AS totalsize
FROM dbo.SubQuery(DATEADD(day, num - 1, @First), @DatedStart, @DatedEnd, @Number, @Year, @Sub)
GROUP BY [Number],
[year]
) thetable
WHERE num <= DATEDIFF(day, @First, GETDATE()) + 1
)
On a side note:
As a matter of practice, when using a cte tally table like you are, you may want to replace your WHERE clause with a TOP clause to generate your numbers. With the WHERE clause there are some (rare) cases where the optimizer will generate all the rows behind the scenes.
You also don't need all those CTEs, N6 is generating 18,446,744,073,709,551,616 rows (that's a lot of days :-P). All you are doing in elongating your query plan and making it harder to read. Considering how you are dealing with days you could stop at N3 and cross join N3 in the nums; that will give you 65536 rows, enough to go back to 1836.
-- Itzik Ben-Gan 2001
June 15, 2015 at 8:50 am
Alan.B (6/12/2015)
On a side note:As a matter of practice, when using a cte tally table like you are, you may want to replace your WHERE clause with a TOP clause to generate your numbers. With the WHERE clause there are some (rare) cases where the optimizer will generate all the rows behind the scenes.
You also don't need all those CTEs, N6 is generating 18,446,744,073,709,551,616 rows (that's a lot of days :-P). All you are doing in elongating your query plan and making it harder to read. Considering how you are dealing with days you could stop at N3 and cross join N3 in the nums; that will give you 65536 rows, enough to go back to 1836.
Thanks for these tips Alan. I removed N4-N6 from the CTE, which did not directly have any impact. What did have an impact was selecting top(365) from nums. This forced the query to follow the same plan as the non parameterized version and reduced the time to 20 seconds with the same output. Still not quite 2 seconds, but definitely a step in the right direction. I suspect you're correct about the optimizer doing something strange with all the rows when it is run as a dynamic query.
You also mentioned moving the cross apply to nums and replacing the where clause with top, but I'm not sure how I would go about doing that and achieving the same result.
June 15, 2015 at 8:59 am
phosplait (6/15/2015)
Alan.B (6/12/2015)
On a side note:As a matter of practice, when using a cte tally table like you are, you may want to replace your WHERE clause with a TOP clause to generate your numbers. With the WHERE clause there are some (rare) cases where the optimizer will generate all the rows behind the scenes.
You also don't need all those CTEs, N6 is generating 18,446,744,073,709,551,616 rows (that's a lot of days :-P). All you are doing in elongating your query plan and making it harder to read. Considering how you are dealing with days you could stop at N3 and cross join N3 in the nums; that will give you 65536 rows, enough to go back to 1836.
Thanks for these tips Alan. I removed N4-N6 from the CTE, which did not directly have any impact. What did have an impact was selecting top(365) from nums. This forced the query to follow the same plan as the non parameterized version and reduced the time to 20 seconds with the same output. Still not quite 2 seconds, but definitely a step in the right direction. I suspect you're correct about the optimizer doing something strange with all the rows when it is run as a dynamic query.
You also mentioned moving the cross apply to nums and replacing the where clause with top, but I'm not sure how I would go about doing that and achieving the same result.
Something like this:
declare @StartDate date = '2015-07-01',
@EndDate date = '2015-07-31';
with e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select 1 from e1 a cross join e1 b),
e4(n) as (select 1 from e2 a cross join e2 b),
e6(n) as (select top(datediff(day,@StartDate,@EndDate) + 1) row_number() over (order by (select null)) n from e4 a cross join e2 b)
select
dateadd(day,n -1,@StartDate) SalesDate
from
e6;
June 15, 2015 at 9:04 am
Jeff Moden (6/12/2015)
phosplait (6/12/2015)
@Jeff, there is no begin in the function. It is an inline function that does a cross apply with another inline function onto a range of datesJust had to be sure. A lot of good folks don't know the difference.
What is the difference?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 15, 2015 at 9:59 am
Here are the execution plans with the where clause replaced by top
Still no luck getting this thing to execute in the same time as non-parameterized even though now they both appear to me to have the same execution plans.
June 15, 2015 at 10:12 am
You are going to have do some work here as well:
FROM dbo.SubQuery(DATEADD(day, num - 1, @First), @DatedStart, @DatedEnd, @Number, @Year, @Sub)
June 15, 2015 at 10:24 am
Lynn Pettis (6/15/2015)
You are going to have do some work here as well:FROM dbo.SubQuery(DATEADD(day, num - 1, @First), @DatedStart, @DatedEnd, @Number, @Year, @Sub)
I thought maybe there was something I missed in the plans, but here is the function as well. It is more straightforward to me than the outer function, so if I had to guess I would say that is where the problem lies.
CREATE FUNCTION [dbo].[SubQuery] (
@OnDate DATE,
@EarliestDated DATE,
@LatestDated DATE,
@Number FLOAT,
@Year INT,
@Substitution INT
)
RETURNS TABLE
AS
RETURN (
SELECT Object.*,
Sums.ModifiedSize AS Amt,
Year(Object.Date) as Year
FROM (
SELECT Transactions.Object,
sum(ModifiedPar) AS ModifiedSize
FROM (
SELECT FilteredS.Object,
CASE TypeIndicator
WHEN 'P'
THEN isnull(Amt, @Substitution) * - 1
WHEN 'S'
THEN isnull(Amt, @Substitution)
ELSE 0
END AS ModifiedSize
FROM (
SELECT *
FROM Object
WHERE DatedDate >= @EarliestDated
AND (
@LatestDated IS NULL
OR DatedDate <= @LatestDated
)
AND (
@Number IS NULL
OR Object.Number = @Number
)
AND (
@Year IS NULL
OR year(Object.Date) = @Year
)
AND FinalSize IS NOT NULL
) FilteredS
LEFT JOIN (
SELECT *
FROM transactions
WHERE TypeIndicator != 'D'
AND (
@OnDate IS NULL
OR TransDate <= @OnDate
)
) Transactions ON FilteredS.Object = Transactions.Object
) AS transactions
GROUP BY tansactions.Object
) Sums
LEFT JOIN Object ON Object.Object = Sums.Object
)
June 15, 2015 at 11:02 am
phosplait (6/15/2015)
Lynn Pettis (6/15/2015)
You are going to have do some work here as well:FROM dbo.SubQuery(DATEADD(day, num - 1, @First), @DatedStart, @DatedEnd, @Number, @Year, @Sub)
I thought maybe there was something I missed in the plans, but here is the function as well. It is more straightforward to me than the outer function, so if I had to guess I would say that is where the problem lies.
CREATE FUNCTION [dbo].[SubQuery] (
@OnDate DATE,
@EarliestDated DATE,
@LatestDated DATE,
@Number FLOAT,
@Year INT,
@Substitution INT
)
RETURNS TABLE
AS
RETURN (
SELECT Object.*,
Sums.ModifiedSize AS Amt,
Year(Object.Date) as Year
FROM (
SELECT Trades.Object,
sum(ModifiedPar) AS ModifiedSize
FROM (
SELECT FilteredS.Object,
CASE TypeIndicator
WHEN 'P'
THEN isnull(Amt, @Substitution) * - 1
WHEN 'S'
THEN isnull(Amt, @Substitution)
ELSE 0
END AS ModifiedSize
FROM (
SELECT *
FROM Object
WHERE DatedDate >= @EarliestDated
AND (
@LatestDated IS NULL
OR DatedDate <= @LatestDated
)
AND (
@Number IS NULL
OR Object.Number = @Number
)
AND (
@Year IS NULL
OR year(Object.Date) = @Year
)
AND FinalSize IS NOT NULL
) FilteredS
LEFT JOIN (
SELECT *
FROM transactions
WHERE TypeIndicator != 'D'
AND (
@OnDate IS NULL
OR TransDate <= @OnDate
)
) Transactions ON FilteredS.Object = Transactions.Object
) AS transactions
GROUP BY tansactions.Object
) Sums
LEFT JOIN Object ON Object.Object = Sums.Object
)
First, this is a catch all query. Hopefully someone else will provide the link to a great blog article on these.
Second, some of the where clause items are not SARGable and will result in clustered index scans (table scans).
Third, I can't find any table or derived table aliases for Trades in the query.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply