October 3, 2012 at 9:00 am
Hello,
I have a problem with a stored procedure returning a result set from a query containing multiple CTEs. When running the query directly in an SSMS query window, the query takes about 4 seconds, which is perfectly acceptable. When using a stored procedure which contains nothing other than the query, as I write this it has been running for 23 minutes and counting...
Here is the query:
DECLARE
@LatestYearint = 2012
,@LineOfBusinesssmallint = 3
,@SixPlusOneCurrencysmallint = 197
,@Syndicatesmallint = 2
,@EndDateIDint = 20120630
DECLARE @YearStartint
,@YearEndPreviousint
;
SELECT @YearStart = DateID FROM dbo.t_Dim_Date WHERE CalendarYear = @LatestYear AND MonthNumber = 1 AND DayOfMonthNumber = 1
;SELECT @YearEndPrevious = DateID FROM dbo.t_Dim_Date WHERE CalendarYear = @LatestYear - 1 AND IsAYearEnd = 1
;
WITH OpenAtStart AS
(
SELECT DISTINCT
OriginalClaimID
FROM
dbo.t_Dim_Claim
WHERE
ValidFromID <= @YearStart
AND ValidToID >= @YearStart
AND ClaimStatusShort = 'O'
)
,OpenAtEnd AS
(
SELECT DISTINCT
OriginalClaimID
FROM
dbo.t_Dim_Claim
WHERE
ValidFromID <= @EndDateID
AND ValidToID >= @EndDateID
AND ClaimStatusShort = 'O'
)
,Results AS
(
SELECT --Includes claims with no movements
DY.YearOfAccount
,SUM(FM.SettlementCurrencySynOutstandings / FER.ExchangeRate) AS GrossMovements
FROM
OpenAtStart AS OS
INNER JOIN
OpenAtEnd AS OE
ON
OS.OriginalClaimID = OE.OriginalClaimID
INNER JOIN
olap.Movements AS FM
ON
OE.OriginalClaimID = FM.OriginalClaimID
INNER JOIN
dbo.t_Dim_YearOfAccount AS DY
ON
FM.YearOfAccountID = DY.YearOfAccountID
INNER JOIN
dbo.t_Fact_ExchangeRate AS FER
ON
FM.SettlementCurrencyID = FER.CurrencyFromID
INNER JOIN
dbo.t_Dim_ExchangeRateType AS ERT
ON
FER.ExchangeRateTypeID = ERT.ExchangeRateTypeID
INNER JOIN
dbo.t_Dim_Currency AS DC
ON
FER.CurrencyToID = DC.CurrencyID
INNER JOIN
dbo.t_Dim_Date AS DD
ON
FER.ExchangeRateDateID = DD.DateID
INNER JOIN
dbo.t_Dim_Risk AS DR
ON
FM.OriginalRiskID = DR.OriginalRiskID
INNER JOIN
dbo.t_Dim_SolvencyII AS DS
ON
DR.SolvencyIIID = DS.SolvencyIIID
WHERE
FM.ProcessMonthID <= @YearEndPrevious
AND ERT.ExchangeRateType = 'Lloyd''s Quarterly Average Rates'
AND DC.CurrencyCode = 'GBP'
AND DD.DateID = @EndDateID
AND DR.CurrentRecord = 1
AND DS.SolvencyIIClassID = @LineOfBusiness
AND FM.SixPlusOneCurrencyID = @SixPlusOneCurrency
AND FM.SyndicateID = @Syndicate
GROUP BY
DY.YearOfAccount
)
SELECT
CASE
WHEN DY.YearOfAccount = @LatestYear THEN 'B'
ELSE 'T'
END AS YearSet
,CASE
WHEN DY.YearOfAccount <= (@LatestYear - 10) THEN CAST((@LatestYear-10) AS varchar(5)) + ' and prior'
ELSE CAST(DY.YearOfAccount AS varchar(5))
END AS YearOfAccount
,COALESCE(SUM(R.GrossMovements),0) AS GrossRBNS
FROM
dbo.t_Dim_YearOfAccount AS DY
LEFT JOIN
Results AS R
ON
DY.YearOfAccount = R.YearOfAccount
WHERE
DY.YearOfAccount <= @LatestYear
AND DY.YearOfAccount NOT IN (-1,-2)
GROUP BY
CASE
WHEN DY.YearOfAccount = @LatestYear THEN 'B'
ELSE 'T'
END
,CASE
WHEN DY.YearOfAccount <= (@LatestYear - 10) THEN CAST((@LatestYear-10) AS varchar(5)) + ' and prior'
ELSE CAST(DY.YearOfAccount AS varchar(5))
END
ORDER BY
YearOfAccount
;
As you can see I have 3 CTEs, the third of which references the first two, and then the outer query references the 3rd CTE. As I said earlier, with those parameters listed at the top it takes about 4 seconds.
If I create a stored procedure for that query, such as this:
CREATE PROCEDURE [lloyds].[up_qmc_Report_E4247_112_RBNSAtStartOfYear]
(
@LatestYearint
,@LineOfBusinesssmallint
,@SixPlusOneCurrencysmallint
,@Syndicatesmallint
,@EndDateIDint
)
AS
DECLARE @YearStartint
,@YearEndPreviousint
;
SELECT @YearStart = DateID FROM dbo.t_Dim_Date WHERE CalendarYear = @LatestYear AND MonthNumber = 1 AND DayOfMonthNumber = 1
;SELECT @YearEndPrevious = DateID FROM dbo.t_Dim_Date WHERE CalendarYear = @LatestYear - 1 AND IsAYearEnd = 1
;
WITH OpenAtStart AS
(
SELECT DISTINCT
OriginalClaimID
FROM
dbo.t_Dim_Claim
WHERE
ValidFromID <= @YearStart
AND ValidToID >= @YearStart
AND ClaimStatusShort = 'O'
)
,OpenAtEnd AS
(
SELECT DISTINCT
OriginalClaimID
FROM
dbo.t_Dim_Claim
WHERE
ValidFromID <= @EndDateID
AND ValidToID >= @EndDateID
AND ClaimStatusShort = 'O'
)
,Results AS
(
SELECT --Includes claims with no movements
DY.YearOfAccount
,SUM(FM.SettlementCurrencySynOutstandings / FER.ExchangeRate) AS GrossMovements
FROM
OpenAtStart AS OS
INNER JOIN
OpenAtEnd AS OE
ON
OS.OriginalClaimID = OE.OriginalClaimID
INNER JOIN
olap.Movements AS FM
ON
OE.OriginalClaimID = FM.OriginalClaimID
INNER JOIN
dbo.t_Dim_YearOfAccount AS DY
ON
FM.YearOfAccountID = DY.YearOfAccountID
INNER JOIN
dbo.t_Fact_ExchangeRate AS FER
ON
FM.SettlementCurrencyID = FER.CurrencyFromID
INNER JOIN
dbo.t_Dim_ExchangeRateType AS ERT
ON
FER.ExchangeRateTypeID = ERT.ExchangeRateTypeID
INNER JOIN
dbo.t_Dim_Currency AS DC
ON
FER.CurrencyToID = DC.CurrencyID
INNER JOIN
dbo.t_Dim_Date AS DD
ON
FER.ExchangeRateDateID = DD.DateID
INNER JOIN
dbo.t_Dim_Risk AS DR
ON
FM.OriginalRiskID = DR.OriginalRiskID
INNER JOIN
dbo.t_Dim_SolvencyII AS DS
ON
DR.SolvencyIIID = DS.SolvencyIIID
WHERE
FM.ProcessMonthID <= @YearEndPrevious
AND ERT.ExchangeRateType = 'Lloyd''s Quarterly Average Rates'
AND DC.CurrencyCode = 'GBP'
AND DD.DateID = @EndDateID
AND DR.CurrentRecord = 1
AND DS.SolvencyIIClassID = @LineOfBusiness
AND FM.SixPlusOneCurrencyID = @SixPlusOneCurrency
AND FM.SyndicateID = @Syndicate
GROUP BY
DY.YearOfAccount
)
SELECT
CASE
WHEN DY.YearOfAccount = @LatestYear THEN 'B'
ELSE 'T'
END AS YearSet
,CASE
WHEN DY.YearOfAccount <= (@LatestYear - 10) THEN CAST((@LatestYear-10) AS varchar(5)) + ' and prior'
ELSE CAST(DY.YearOfAccount AS varchar(5))
END AS YearOfAccount
,COALESCE(SUM(R.GrossMovements),0) AS GrossRBNS
FROM
dbo.t_Dim_YearOfAccount AS DY
LEFT JOIN
Results AS R
ON
DY.YearOfAccount = R.YearOfAccount
WHERE
DY.YearOfAccount <= @LatestYear
AND DY.YearOfAccount NOT IN (-1,-2)
GROUP BY
CASE
WHEN DY.YearOfAccount = @LatestYear THEN 'B'
ELSE 'T'
END
,CASE
WHEN DY.YearOfAccount <= (@LatestYear - 10) THEN CAST((@LatestYear-10) AS varchar(5)) + ' and prior'
ELSE CAST(DY.YearOfAccount AS varchar(5))
END
ORDER BY
YearOfAccount
;
and pass the same parameters:
EXEC lloyds.up_qmc_Report_E4247_112_RBNSAtStartOfYear 2012,3,197,2,20120630
this takes considerably longer than 4 seconds!
I am currently awaiting an execution plan so that I can hopefully spot the problem, but in the meantime I wondered if any of the bright sparks haunting the corridors of SSC had any bright ideas.
I am perfectly willing to replace the CTEs with temporary tables which will almost certainly fix the issue, but for my own edification I would like to know why this issue is occurring.
Many thanks,
Ash Shah
October 3, 2012 at 9:04 am
Just a possibility, but read up on "parameter sniffing".
Also, I've found that CTE's are generally poor performers when dealing with large result sets. In those situations, I'd suggest using temp tables.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 3, 2012 at 9:12 am
Try Adding an OPTION(RECOMPILE) to the SQL Statement that calls the CTE, as I suspect theres an element of paramater sniffing going on, whihc should be solved by this, it might not fix it entirely.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 3, 2012 at 9:16 am
Thanks for the quick replies.
I have refreshed my memory on parameter sniffing. I am not convinced that this is the issue, for two reasons:
1) Would the execution of the stored procedure try and use the same query plan as that for the direct query?
2) The parameter values are the same in both cases, so whay would I need a different plan? If anything I would want exactly the same plan!
I agree that CTEs can cause performance problems, but I clearly don't have a performance problem when using the query directly in SSMS.
Thanks again,
Ash
October 3, 2012 at 9:20 am
You don't have to change the query in the stored proc. Just execute the proc with the recompile option.
EXEC lloyds.up_qmc_Report_E4247_112_RBNSAtStartOfYear 2012,3,197,2,20120630 WITH RECOMPILE;
October 3, 2012 at 9:25 am
I take it all back, you were absoultely right!!!
Thanks so much everyone,
Ash
October 3, 2012 at 9:29 am
Now that you know parameter sniffing is the problem you may want to consider putting the recompile option in the proc if you haven't already. There might be several places where this proc is executed. If you only add the option that I gave you it will only fix it in that one call.
October 3, 2012 at 9:32 am
Way ahead of you Brendon - amended, deployed and tested. All is well.
Thanks for the follow up. Get someone to make you a nice cup of tea. You deserve it!
Ash
October 3, 2012 at 9:39 am
No problem. Thank the Jason's. They provided the solution. I just provided an alternative if modifying the proc wasn't an option.
To answer your questions above. The optimizer can pick a different query plan (and did) for the query you ran in SSMS and the execution of the stored procedure. Parameter sniffing is only a problem when "parameters" are present. There aren't parameters in your first query you ran in SSMS. You're declaring variables and setting them equal to some values. So sniffing can't happen in that instance. That's what tipped them off to parameter sniffing being your problem.
October 3, 2012 at 9:42 am
That makes perfect sense. Kudos to you and of course the Jasons.
Ash
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply