Multiple CTE select statement - fast in SSMS, painfully slow in a stored proc

  • Ash.Shah

    SSC Eights!

    Points: 982

    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

    @LatestYear int = 2012

    , @LineOfBusiness smallint = 3

    , @SixPlusOneCurrency smallint = 197

    , @Syndicate smallint = 2

    , @EndDateID int = 20120630

    DECLARE @YearStart int

    , @YearEndPrevious int

    ;

    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]

    (

    @LatestYear int

    , @LineOfBusiness smallint

    , @SixPlusOneCurrency smallint

    , @Syndicate smallint

    , @EndDateID int

    )

    AS

    DECLARE @YearStart int

    , @YearEndPrevious int

    ;

    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

  • Jason Selburg

    SSC-Insane

    Points: 24560

    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. Selburg
  • Jason-299789

    SSC-Insane

    Points: 21601

    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

  • Ash.Shah

    SSC Eights!

    Points: 982

    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

  • brendan woulfe

    Hall of Fame

    Points: 3888

    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;

  • Ash.Shah

    SSC Eights!

    Points: 982

    I take it all back, you were absoultely right!!!

    Thanks so much everyone,

    Ash

  • brendan woulfe

    Hall of Fame

    Points: 3888

    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.

  • Ash.Shah

    SSC Eights!

    Points: 982

    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

  • brendan woulfe

    Hall of Fame

    Points: 3888

    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.

  • Ash.Shah

    SSC Eights!

    Points: 982

    That makes perfect sense. Kudos to you and of course the Jasons.

    Ash

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply