SQL Parameter Sniffing slowing me down
I'd not heard of this particular problem before I ran into it so I thought I'd share with the masses. I basically had a SQL query which took longer to run as a stored proc than as the dynamic parameterized SQL that makes up the body of the stored proc. The difference was drastic - 40 seconds for the sproc, 1 second for the dynamic parameterized SQL. As this was identical SQL run through Query Analyzer, the problem was not with the application or indexes.
The roundabout story is that we had someone do something wrong with our warehouse management application which caused our warehouse to report itself as out of storage space (in the digital world). Basically, I had to write a fix which would essentially digitally move products from over here <points left> to over there <points right>. The app screen I wrote up for this task isn't particularly interesting, but here is what it essentially has to do. Given an list of UPCs of indeterminate length, move the UPCs from their currently assigned warehouse bin to an availble bin in specified warehouse rows.
Hit the Move Items button and for each product in the list, you have to find the valid bin on the given aisle then do the transfer. This process was already supported for moving an item to a new bin, but it didn't take the aisle specification. So I added that functionality to the Get_Valid_Bin stored proc. Suddenly I was getting timeouts when I tried to call my procedure. Very strange, I didn't add much in the way of logic. To test, I extracted the contents of the stored proc into a Query Analyzer window and ran them (still parameterized). I was getting my result in 1 second or less. Run the same data through the proc and I was getting my results in 40 seconds every time. WFT!?!
I asked around for help and the first set of advice I got was that I had too many decision points inside of the query (IF tests) and that I should make the query less complex. OK - I took out the part I added and went back to the original query. My new query was GetFirstValidBin and it took the row number as a parameter. This query still contained an IF test (whether or not to search on a specific row) but it now ran in 1 second or less as a stored procedure. Whoopee! HOWEVER - the original stored proc was still running in 40 seconds and several bits of existing functionality depend on this sproc. It was back in it's original state - I'd not touched it. I don't know what happened.
So I asked around again and the next set of advice I got was that I likely had a problem with the cached estimated execution plan. See - when you make a call to a stored proc, it gets compiled by the SQL Server engine and saved for a while in case of future calls. The saved version might have used different parameter values causing the stored proc to have to do major work to get around my IF tests. I wasn't really buying this explaination b/c I was running the same query over and over in Query Analyzer, but it seemed worth a shot. I also was referred to these two SQL Parameter Sniffing forum posts online - Post1 and Post2. Both mention that the way around bad cached estimated execution plans was to remove the execution plan's dependence on the passed-in parameters. This caused me to rewrite my query - assigning passed-in parameters to local variables at the top of the procedure and all the logic below used the local variables.
That did the trick - now I get my results in 3 seconds or less. I have absolutely NO idea why the original proc, which was working fine before I changed it and restored the original version, was suddenly affected. However, this did prove to be a functional solution.
I am posting the final, working query below so that everyone can see that I'm not talking about a lot of complexity.
The other tip I heard about but did not implement was write the proc in such a way that it called sp_recompile every time it was called. This didn't sound good to me (as I've always thought avoiding unnecessary recompiles was a good thing) and I didn't have to bother with it as I got things working.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vm_Get_Valid_Bins]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[vm_Get_Valid_Bins]
GO
/*
PURPOSE: This returns a list of valid bins with specified capacity, in the proper dstwhno and configured
to hold the indicated AlphCfg
PARAMETERS: @AlphCfg The cfg of the item that we want
@DstWhNo The warehouse that it should be in
@Capacity How much capacity we need
@AutoAssign Y if we want an autoassign (ie.. single bin only
EXAMPLE:
EXEC vm_Get_Valid_Bins 'CD', 1, 5, 'Y'
CREATION DATE: April 10,2003
LAST MODIFICATION DATE: April 16, 2003
MODIFIFCATIONS: Added the autoassign functionality that returns only a single bin
Also added where this checks TitleCount and MaxQtyTitle as well
LAST MODIFICATION DATE: May 14, 2003
MODIFIFCATIONS: Added capability to handle warehouse in the 700 range
*/
CREATE PROCEDURE [dbo].[vm_Get_Valid_Bins]
@AlphCfg VARCHAR(2) = NULL ,
@DstWhNo INT = NULL ,
@Capacity INT = NULL ,
@AutoAssign VARCHAR(1) = NULL
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE @ItemAlphCfg VARCHAR(2)
DECLARE @WarehouseNumber INTEGER
DECLARE @ItemQuantity INTEGER
DECLARE @AutoAssignFlag VARCHAR(1)
SET @ItemAlphCfg = @AlphCfg
SET @WarehouseNumber = @DstWhNo
SET @ItemQuantity = @Capacity
SET @AutoAssignFlag = @AutoAssign
-- if the warehouse is above 700 then subtract 700 to get the real warehouse
IF @WarehouseNumber > 700
SET @WarehouseNumber = @WarehouseNumber - 700
IF @AutoAssignFlag IS NULL
BEGIN
SELECT L.Bin_Num ,
ISNULL(SUM(I2.OnHnd),0) as test,
L.Capacity ,
L.Capacity - IsNull(SUM(I.OnHnd ),0) - ISNULL(SUM(I2.OnHnd), 0) AS QtyAvail,
L.MaxQtyTitle ,
L.MinQtyTitle ,
COUNT(X.UPC) AS NumTitlesInBin ,
L.TitleCount as MaxTitles
FROM BinLocations L with (nolock)
LEFT JOIN Bin_Xref X with (nolock)
ON X.Bin_Num = L.Bin_Num
LEFT JOIN ItmDstWh I with (nolock)
ON I.UPC = X.UPC AND L.DstWhNo = I.DstWhNo
LEFT JOIN ItmDstWh I2 with (nolock)
ON X.UPC = I2.UPC AND I2.DstWhNo = @WarehouseNumber + 700
LEFT JOIN BinCfg C with (nolock)
ON L.CfgType = C.BinCfgType
WHERE L.DstWhNo = @WarehouseNumber
AND CHARINDEX(@ItemAlphCfg,C.AlphaCfgs) <> 0
AND CASE L.MaxQtyTitle
WHEN 0 THEN 1
ELSE L.MaxQtyTitle - @ItemQuantity
END > 0
GROUP BY L.Bin_Num,
L.Capacity ,
L.MaxQtyTitle ,
L.MinQtyTitle ,
L.TitleCount
HAVING L.Capacity - IsNull(SUM(I.OnHnd),0) - IsNull(SUM(I2.OnHnd),0) >= @ItemQuantity
AND CASE L.TitleCount
WHEN 0 THEN 1
ELSE L.TitleCount - COUNT(X.UPC)
END > 0
AND CASE
WHEN L.MinQtyTitle = 0 THEN 1
WHEN L.MinQtyTitle <= @ItemQuantity THEN 1
ELSE 0
END > 0
ORDER BY L.Capacity, QtyAvail DESC, NumTitlesInBin, L.Bin_Num
END
ELSE -- AutoAssignFlag = 'Y' so we need a single bin
BEGIN
SELECT TOP 1 L.Bin_Num
FROM BinLocations L with (nolock)
LEFT JOIN Bin_Xref X with (nolock)
ON X.Bin_Num = L.Bin_Num
LEFT JOIN ItmDstWh I with (nolock)
ON I.UPC = X.UPC AND L.DstWhNo = I.DstWhNo
LEFT JOIN BinCfg C with (nolock)
ON L.CfgType = C.BinCfgType
LEFT JOIN ItmDstWh I2 with (nolock)
ON X.UPC = I2.UPC AND I2.DstWhNo = @WarehouseNumber + 700
WHERE L.AutoAssign <> 0
AND L.DstWhNo =@WarehouseNumber
AND CHARINDEX(@ItemAlphCfg,C.AlphaCfgs) <> 0
AND CASE L.MaxQtyTitle
WHEN 0 THEN 1
ELSE L.MaxQtyTitle - @ItemQuantity
END >0
GROUP BY L.Bin_Num,
L.Capacity ,
L.MaxQtyTitle ,
L.MinQtyTitle ,
L.TitleCount
HAVING L.Capacity - IsNull(SUM(I.OnHnd),0) - ISNULL(SUM(I2.OnHnd),0) >= @ItemQuantity
AND CASE L.TitleCount
WHEN 0 THEN 1
ELSE L.TitleCount - COUNT(X.UPC)
END > 0
AND CASE
WHEN L.MinQtyTitle = 0 THEN 1
WHEN L.MinQtyTitle <= @ItemQuantity THEN 1
ELSE 0
END > 0
ORDER BY L.Capacity, L.Capacity - IsNull(SUM(I.OnHnd), 0) - ISNULL(SUM(I2.OnHnd), 0) DESC, COUNT(X.UPC), L.Bin_Num
END
SET NOCOUNT OFF
SET ANSI_WARNINGS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO