Link to home
Start Free TrialLog in
Avatar of Flynnster
Flynnster

asked on

Need help with a Stored Procedure

I've got this wonderful Stored Procedure that is about to make me punt my laptop.

Yesterday, it worked fine.  This morning I made one small tweak (changing a parameter to a variable), and now it runs for well over five minutes without returning anything.

When I take out the SQL and run it directly, it runs in under a minute.  When I comment out the SQL, the Stored Procedure runs just fine in under a second.

Could someone please review this Stored Procedure and look to see if you can find any glaring errors ?  

Thanks!

- Flynn
--DROP PROCEDURE [dbo].[stp_Crystal_EndingInventoryTotals_AllStores_FGK]
--GO
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
--CREATE PROCEDURE [dbo].[stp_Crystal_EndingInventoryTotals_AllStores_FGK]
ALTER PROCEDURE [dbo].[stp_Crystal_EndingInventoryTotals_AllStores_FGK]
		@InventoryDateParam					smalldatetime = null,
		@IncludeNullDept					bit = 0--,
		
		--@YESTERDAY							smalldatetime , 
		--@RTNCODE							char 
AS

DECLARE 
		@YESTERDAY							smalldatetime , 
		@RTNCODE							char 
/*
Create table #tblStoreAttributes				(
	StoreNbr						smallint,
	[Address]						varchar(50),
	City							varchar(50),
	[State]							char(2),
	PostalCode						char(10),
	County							char(30),
	CompanyStoreFlag				bit,
	WPLAreaName						varchar(50)
)
*/
--Retrieve store attributes
--Insert Into #tblStoreAttributes
--Execute @RtnCode = stp_RetrieveActiveStoreAttributes_AdBook

If @RtnCode <> 0
Begin
	Print 'Return code ' + convert(varchar(12), @RtnCode) + ' received from stp_RetrieveActiveStoreAttributes_AdBook.'
	Return 5
End

	Select @Yesterday = Dateadd(d, -1, @InventoryDateParam)	
	
	;WITH RUN1 AS (
	SELECT ID,StoreConfiguration_StoreNbr, InventoryDate 
	FROM Inventory.HistoricalInventory 
	WHERE InventoryDate = @Yesterday)
	, RUN2 AS (
	SELECT ID,StoreConfiguration_StoreNbr, InventoryDate 
	FROM Inventory.HistoricalInventory 
	WHERE InventoryDate = @InventoryDateParam)
	, ST AS (
	SELECT ID,StoreConfiguration_StoreNbr, InventoryDate FROM RUN1
	UNION ALL
	SELECT ID,StoreConfiguration_StoreNbr, InventoryDate FROM RUN2)
	, GTIN AS (
    SELECT DISTINCT I.GTINCode, V.StoreConfiguration_StoreNbr AS STORENBR
	FROM [Control].SBTVendors V WITH (NOLOCK)
		INNER JOIN Inventory.ItemAttributes I WITH (NOLOCK)
			ON I.VendorNbr = V.VendorNbr
			AND I.WhseCode = 0
			,ST
	WHERE V.StoreConfiguration_StoreNbr = ST.StoreConfiguration_StoreNbr		
	GROUP BY I.GTINCode, V.StoreConfiguration_StoreNbr 
	UNION 
	SELECT DISTINCT I.GTINCode, S.StoreNbr
	FROM [Control].ExclusionVendors E WITH (NOLOCK)
		INNER JOIN Inventory.ItemAttributes I WITH (NOLOCK)
			ON I.VendorNbr = E.VendorNbr
		,[Control].StoreConfiguration S 
		,ST
	WHERE S.StoreNbr = ST.StoreConfiguration_StoreNbr
	UNION 
	SELECT GTINCode, S.StoreNbr
	FROM [Control].ExclusionGTINs
		,[Control].StoreConfiguration S
	    ,ST
	WHERE S.StoreNbr = ST.StoreConfiguration_StoreNbr)
    --, ATTRIB AS (Select STORENBR,City + ',' + State as DESCR From #tblStoreAttributes) 
	, MAIN AS(
	SELECT	HI.StoreConfiguration_StoreNbr AS StoreNbr, HI.InventoryDate,
			(CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand) ELSE 0 END)							as CQOH,
			(CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand * Cast(HI.Cost as money)) ELSE 0 END)	as CETC,
			(CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand * Cast(HI.Retail as money)) ELSE 0 END)	as CETR,
			(CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand) ELSE 0 END)							as PQOH,
			(CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand * Cast(HI.Cost as money)) ELSE 0 END)	as PETC,
			(CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand * Cast(HI.Retail as money)) ELSE 0 END)	as PETR
	FROM Inventory.HistoricalInventory as HI with (nolock)
	LEFT OUTER JOIN GTIN ON GTIN.STORENBR = HI.StoreConfiguration_StoreNbr 
	AND HI.GTINCODE = GTIN.GTINCODE
	, ST
	WHERE HI.SourceFlag = 'U'
	AND HI.Department IN ('0','6','H') 
	AND HI.Category NOT IN (620,621) 
	AND ST.StoreConfiguration_StoreNbr = HI.StoreConfiguration_StoreNbr
	AND ST.InventoryDate = HI.InventoryDate
	AND ST.ID = HI.ID
	AND GTIN.GTINCode IS NULL
	GROUP BY HI.StoreConfiguration_StoreNbr,HI.InventoryDate) 
	/* 
	,PRETTYITUP AS ( 
	SELECT MAIN.STORENBR, DESCR, SUM(CQOH) AS CQOH, SUM(CETC) AS CETC, 
	SUM(CETR) AS CETR, SUM(PQOH) AS PQOH, SUM(PETC) AS PETC, SUM(PETR) AS PETR 
	FROM MAIN LEFT OUTER JOIN ATTRIB ON MAIN.StoreNbr = ATTRIB.STORENBR
	GROUP BY MAIN.STORENBR, DESCR)
	SELECT STORENBR, DESCR, PQOH, CQOH, (CQOH - PQOH) AS QCHANGE, PETC, CETC
	, (CETC - PETC) AS CCHANGE, CETR
	FROM PRETTYITUP ORDER BY 1
	*/
	,PRETTYITUP AS ( 
	SELECT MAIN.STORENBR
	--, DESCR
	, SUM(CQOH) AS CQOH, SUM(CETC) AS CETC, 
	SUM(CETR) AS CETR, SUM(PQOH) AS PQOH, SUM(PETC) AS PETC, SUM(PETR) AS PETR 
	FROM MAIN --LEFT OUTER JOIN ATTRIB ON MAIN.StoreNbr = ATTRIB.STORENBR
	GROUP BY MAIN.STORENBR
	--, DESCR
	)
	SELECT STORENBR
	--, DESCR
	, PQOH, CQOH, (CQOH - PQOH) AS QCHANGE, PETC, CETC, (CETC - PETC) AS CCHANGE, CETR
	FROM PRETTYITUP ORDER BY 1
	
Return 0

Open in new window

Avatar of jasonduan
jasonduan
Flag of United States of America image
This might be something to do with "Parameter Sniffing".

http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices--Parameter-Sniffing.htm

You can try this:
ALTER PROCEDURE [dbo].[stp_Crystal_EndingInventoryTotals_AllStores_FGK]
		@InventoryDateParam_IN	smalldatetime = null,
		@IncludeNullDept_IN	bit = 0
AS

	DECLARE 
		@InventoryDateParam	smalldatetime,
		@IncludeNullDept		bit,
		@YESTERDAY		smalldatetime , 
		@RTNCODE			char 

	SET @InventoryDateParam = @InventoryDateParam_IN
	SET @IncludeNullDept = @IncludeNullDept_IN

         .... the rest of your procedure ...

Open in new window

Avatar of Flynnster
Flynnster

ASKER

Unfortunately, it is still running with no resulting rows as of thre minutes...so I'm not certain this is the issue.  However, I will review the article in the link you sent me!

- Flynn
Avatar of Flynnster
Flynnster

ASKER

Ok, I'm now certain that it *IS* a case of parameter sniffing.  I just went into the code, commented out the declaration statements for the input parameters, and changed the private variables within the SQL to literal strings.  It ran in under a minute.

However, I am still unable to get it working with the common workaround of declaring the parameters then using SET to assign their value to a local private variable, as jason had suggested.

Any thoughts ?  -Flynn
Avatar of Flynnster
Flynnster

ASKER

Also, I am using 2005, so using an OPTION for OPTIMIZER UNKNOWN won't work here :(

- Flynn
ASKER CERTIFIED SOLUTION
Avatar of rmm2001
rmm2001
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Flynnster
Flynnster

ASKER

USE [StoreInventory]
GO
/****** Object:  StoredProcedure [dbo].[stp_Crystal_EndingInventoryTotals_AllStores_FGK]    Script Date: 03/01/2011 08:18:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            Flynn Kinkade      
-- Create date: 02/28/2011
-- Description:      Stored procedure to drive an
-- exception report for the current report titled
-- SPI Ending Inventory Store Totals.
-- Code takes 45-55 seconds to run.
-- =============================================
--CREATE PROCEDURE [dbo].[stp_Crystal_EndingInventoryTotals_AllStores_FGK]

ALTER PROCEDURE [dbo].[stp_Crystal_EndingInventoryTotals_AllStores_FGK]
            @InventoryDateParam_IN      smalldatetime = null
            ,@IncludeNullDept_IN      bit = 0
AS

      DECLARE
            @InventoryDateParam      smalldatetime,
            @IncludeNullDept            bit,
            @YESTERDAY            smalldatetime ,
            @RTNCODE                  char

      SET @InventoryDateParam = @InventoryDateParam_IN
      SET @IncludeNullDept = @IncludeNullDept_IN

If @RtnCode <> 0
Begin
      Print 'Return code ' + convert(varchar(12), @RtnCode) + ' received from stp_RetrieveActiveStoreAttributes_AdBook.'
      Return 5
End

      Select @Yesterday = Dateadd(d, -1, @InventoryDateParam)       
/*CREATING TABLE tblStoreAttributes */
Create table #tblStoreAttributes                        (
      StoreNbr                                    smallint,
      [Address]                                    varchar(50),
      City                                          varchar(50),
      [State]                                          char(2),
      PostalCode                                    char(10),
      County                                          char(30),
      CompanyStoreFlag                        bit,
      WPLAreaName                                    varchar(50)
)
Insert Into #tblStoreAttributes
Execute @RtnCode = stp_RetrieveActiveStoreAttributes_AdBook      
/* FINISHED CREATING TABLE tblStoreAttributes */      
/* CREATING TABLE GTIN */      
CREATE TABLE #GTIN
(
    GTINCode VARCHAR(50),
    STORENBR VARCHAR(50)
)
CREATE NONCLUSTERED INDEX IX_GTIN_StoreNbr ON #GTIN (StoreNbr)

INSERT #GTIN
SELECT DISTINCT I.GTINCode, V.StoreConfiguration_StoreNbr as StoreNbr
FROM [Control].SBTVendors V WITH (NOLOCK)
      INNER JOIN Inventory.ItemAttributes I WITH (NOLOCK)
            ON I.VendorNbr = V.VendorNbr
            AND I.WhseCode = 0       
GROUP BY I.GTINCode, V.StoreConfiguration_StoreNbr
UNION
SELECT DISTINCT I.GTINCode, S.StoreNbr
FROM [Control].ExclusionVendors E WITH (NOLOCK)
      INNER JOIN Inventory.ItemAttributes I WITH (NOLOCK)
            ON I.VendorNbr = E.VendorNbr
      ,[Control].StoreConfiguration S  
UNION
SELECT GTINCode, S.StoreNbr
FROM [Control].ExclusionGTINs
      ,[Control].StoreConfiguration S
/*FINISHED CREATING TABLE GTIN */

/*CREATING TABLE DATES */      
CREATE TABLE #DATES
(
    STORENBR VARCHAR(50),
    INVENTORYDATE SMALLDATETIME
)
CREATE NONCLUSTERED INDEX IX_DATES_StoreNbr ON #DATES (StoreNbr)
      
INSERT #DATES
SELECT DISTINCT HI.StoreConfiguration_StoreNbr AS StoreNbr, HI.InventoryDate  
FROM Inventory.HistoricalInventory as HI with (nolock)       
WHERE HI.InventoryDate = @Yesterday
UNION ALL
SELECT DISTINCT HI.StoreConfiguration_StoreNbr AS StoreNbr, HI.InventoryDate  
FROM Inventory.HistoricalInventory as HI with (nolock)       
WHERE HI.InventoryDate = @InventoryDateParam
/* FINISHED CREATING TABLE DATES */
/* Create Table HI_SUBSET */
CREATE TABLE #HI_SUBSET
( StoreNbr VARCHAR(50)
 ,DESCR VARCHAR(50)
 ,CQOH numeric
 ,CETC decimal
 ,CETR decimal
 ,PQOH numeric
 ,PETC decimal
 ,PETR decimal)
 
INSERT #HI_SUBSET
SELECT      HI.StoreConfiguration_StoreNbr as StoreNbr,
            SA.City+','+SA.[State] as DESCR,
            (CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam)
                  THEN Sum(HI.BalanceOnHand) ELSE 0 END)                                                            as CQOH,
            (CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam)
                  THEN ROUND(Sum(HI.BalanceOnHand * Cast(HI.Cost as money)),2) ELSE 0 END)            as CETC,
            (CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam)
                  THEN ROUND(Sum(HI.BalanceOnHand * Cast(HI.Retail as money)),2) ELSE 0 END)      as CETR,
            (CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam)
                  THEN Sum(HI.BalanceOnHand) ELSE 0 END)                                                            as PQOH,
            (CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam)
                  THEN ROUND(Sum(HI.BalanceOnHand * Cast(HI.Cost as money)),2) ELSE 0 END)            as PETC,
            (CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam)
                  THEN ROUND(Sum(HI.BalanceOnHand * Cast(HI.Retail as money)),2) ELSE 0 END)      AS PETR
FROM Inventory.HistoricalInventory as HI with (nolock)
      LEFT OUTER JOIN #tblStoreAttributes as SA
            ON HI.StoreConfiguration_StoreNbr = SA.StoreNbr
      LEFT OUTER JOIN #GTIN X
            ON X.StoreNbr = HI.StoreConfiguration_StoreNbr
            AND X.GTINCode = HI.GTINCode
    , #DATES Z
WHERE HI.SourceFlag = 'U'
AND ((@IncludeNullDept = 1 AND HI.Department IN ('0','6','H') OR HI.Department IS NULL)
  OR (@IncludeNullDept = 0 AND HI.Department IN ('0','6','H')))  
AND HI.Category NOT IN (620,621)
AND X.GTINCode IS NULL
AND HI.StoreConfiguration_StoreNbr = Z.STORENBR
AND HI.InventoryDate = Z.InventoryDate
GROUP BY      HI.StoreConfiguration_StoreNbr,
                  HI.InventoryDate,
                  SA.City+','+SA.[State]
ORDER BY      HI.StoreConfiguration_StoreNbr,3
/* Finished creating table HI_SUBSET */
/* Run SQL for report */
SELECT A.STORENBR,A.DESCR,B.PQOH,A.CQOH,(A.CQOH - B.PQOH) AS QOH_DIFF
      ,B.PETC,A.CETC, (A.CETC - B.PETC) AS ETC_DIFF,A.CETR
FROM #HI_SUBSET A, #HI_SUBSET B
WHERE A.STORENBR = B.STORENBR
AND A.CQOH > 0
AND B.PQOH > 0      
/* Finished running SQL for report */
/*Cleanup*/      
DROP TABLE #GTIN
DROP TABLE #DATES
DROP TABLE #HI_SUBSET
DROP TABLE #tblStoreAttributes
/*End Cleanup*/
Return 0
Avatar of rmm2001
rmm2001
Flag of United States of America image
Um isn't that what I said and posted? I think points should be assigned.
Avatar of Flynnster
Flynnster

ASKER

Southmod, please award the expert his points.  Chalk it up to lack of understanding of the system on my part.