January 25, 2016 at 8:24 am
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)?
January 25, 2016 at 8:41 am
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
January 25, 2016 at 8:41 am
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.
😎
January 25, 2016 at 8:57 am
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.
January 25, 2016 at 8:57 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply