WITH Recompile VS Option Recompile , With Recompile performs worse?

  • I expected a proc using with recompile to perform exactly the same as a proc which had each batch have an option(recompile).

    So this was run on Adventureworks2012, on a SQL2012 instance, but since there is no 2012 perf tuning group, I have added this topic here.

    The procs below are a port of a poor performing proc that I am currently fixing, one withRecompile and the other with (option(recompile) in the query.

    DDL, tests and cleanup below.

    SET ANSI_NULLS,QUOTED_IDENTIFIER ON

    GO

    create PROCEDURE testProcOptionRecompile

    (

    @productID INT = NULL,@customerid int = null

    )

    AS

    WITH productidStats AS

    (

    SELECT

    SOH.CustomerID

    ,COUNT(SOH.CustomerID ) AS TotalSales

    ,SUM(P.listprice) AS TotalPrice

    ,MIN(P.productmodelid) AS MinPRodModel

    ,MAX(P.productmodelid) AS MaxPRodModel

    ,SUM(SOH.[SubTotal]) AS SubTotal

    FROM

    [Production].[Product] P

    INNER JOIN [Sales].[SalesOrderDetail] SOD ON SOD.productid = P.productid

    INNER JOIN [Sales].[SalesOrderHeader] SOH ON SOH.SalesOrderID = SOD.SalesOrderID

    WHERE (@productID IS NULL OR P.productid = @productID)

    GROUP BY

    SOH.CustomerID

    )

    Select C.CustomerID,

    TotalSales

    ,WeeklyViewCount = ISNULL(TotalPrice, 0)

    ,MinPRodModel

    ,MaxPRodModel

    ,SubTotal

    FROM

    [Sales].[Customer] C

    LEFT OUTER JOIN productidStats ss ON C .customerid = ss.customerid

    WHERE

    (@customerid IS NULL OR C.Customerid = @customerid)

    OPTION(RECOMPILE);

    go

    create PROCEDURE testProcWithrecompile

    (

    @productID INT = NULL,@customerid int = null

    )

    WITH Recompile

    AS

    ;WITH productidStats AS

    (

    SELECT

    SOH.CustomerID

    ,COUNT(SOH.CustomerID ) AS TotalSales

    ,SUM(P.listprice) AS TotalPrice

    ,MIN(P.productmodelid) AS MinPRodModel

    ,MAX(P.productmodelid) AS MaxPRodModel

    ,SUM(SOH.[SubTotal]) AS SubTotal

    FROM

    [Production].[Product] P

    INNER JOIN [Sales].[SalesOrderDetail] SOD ON SOD.productid = P.productid

    INNER JOIN [Sales].[SalesOrderHeader] SOH ON SOH.SalesOrderID = SOD.SalesOrderID

    WHERE (@productID IS NULL OR P.productid = @productID)

    GROUP BY

    SOH.CustomerID

    )

    Select C.CustomerID

    ,TotalSales

    ,WeeklyViewCount = ISNULL(TotalPrice, 0)

    ,MinPRodModel

    ,MaxPRodModel

    ,SubTotal

    FROM

    [Sales].[Customer] C

    LEFT OUTER JOIN productidStats ss ON C .customerid = ss.customerid

    WHERE

    (@customerid IS NULL OR C.Customerid = @customerid)

    go

    Test cases.

    exec testProcOptionRecompile @customerid = NULL,@productID = null

    exec testProcWithRecompile @customerid = NULL,@productID = null

    exec testProcOptionRecompile @customerid = 11407,@productID = null

    exec testProcWithRecompile @customerid = 11407,@productID = null

    exec testProcOptionRecompile @customerid = null,@productID = 2

    exec testProcWithRecompile @customerid = null,@productID = 2

    exec testProcOptionRecompile @customerid = 11407,@productID = 1

    exec testProcWithRecompile @customerid = 11407,@productID = 1

    Cleanup

    drop procedure testProcOptionRecompile

    drop procedure testProcWithRecompile

    Firstly, can anyone else reproduce what I am seeing, to make sure I am not on the sauce?

    Secondly, if anyone can reproduce this, any idea why With Recompile performs worse than Option(Recompile)?

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • You have a catch-all query there. The WITH RECOMPILE does not give the same effects w.r.t. that query as option recompile does. option recompile relaxes the optimiser's requirements for the plan to be safe for reuse, and so you get an optimal plan for each execution. With recompile doesn't.

    https://www.simple-talk.com/content/article.aspx?article=2280

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • If I'm reading this correctly then the sproc returns sales by either a product, a customer or both. This kind of code is not likely to benefit from recompilation as it is, suggest you look into either dynamic SQL or conditional execution of more optimizable code.

    😎

  • Thanks Gail.

    I have been using your article as a way to optimise a catch all proc without DSQL cos the devs are, let's say, averse to DSQL.

    On re-reading, I do see in the comments that you mentioned this

    WITH RECOMPILE on the proc isn't going to help with any of the cases I cover in this article, because the compilation is still at the batch level. It's not an alternative to option(recompile)

    So that answers it for me.

    @Eirikur - Yes, the query makes no sense, I just had to anonymise it but also make it behave the same way it did with production data to repro the behaviour.

    So in adventureworks, it will make no sense.

    DSQL was my first choice cos I am well versed in it now, but the devs, they pee their pants when I say DSQL.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • Eirikur Eiriksson (1/25/2016)


    This kind of code is not likely to benefit from recompilation as it is, suggest you look into either dynamic SQL or conditional execution of more optimizable code.

    😎

    It benefits just fine from OPTION(RECOMPILE), but not the older WITH RECOMPILE, because that doesn't give the optimiser the guarantees it needs to generate optimal plans for each query.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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