Dropping #temptables | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Dropping #temptables

while recently reviewing a SP, i saw at the end of the SP they are dropping #temptables. Though this code is unncessary, is it worth removing the code? considering that those tables will hold 2-5 million rows, will i be able to get any performance gain??? Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
Not strictly necessary, and probably no performance gain. But one of the golden rules in programming is still: always clean up after yourself. It makes you pay attention to what you’re declaring and creating, and it forces you to keep track of things.
TEMP TABLES must be dropped as and when their existence is not required, otherwise you might be wasting space on TEMPDB and adding another overhead in this case. If that SP is perform really really slow then post it here for a better suggestions, I think temp tables process may not be the issue. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Yes it is a good practice to drop the temp tables at the end of the proc. As Satya mentioned saves tempdb space (maintenance of Tempdb if you have any indexes on the tables) etc. It makes more sense if you have so much data in the temp tables. ***********************
Dinakar Nethi
***********************
http://weblogs.sqlteam.com/dinakar/
Hi, Dont the #temptables dropped automatically after the SP completes? Here is the SP that is for 4.5 hrs
CREATE PROCEDURE dbo.CalCP
(
@pRowCnt INT OUT,
@pPrintSQL BIT = 0,
@pExecuteSQL BIT = 1
) AS
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET XACT_ABORT OFF
SET DATEFORMAT YMD SET DATEFIRST 7 — 7 (default, U.S. English) Sunday DECLARE @lvcSPName SYSNAME — For Error Handling
DECLARE @lvcStepName VARCHAR(200) — For Error Handling
DECLARE @lvcErrorCode VARCHAR(7) — For Error Handling
DECLARE @lvcTableName VARCHAR(100) — For Holding The Table Name
DECLARE @lnError INTEGER — For Holding Error Number DECLARE @vRowCnt INT
DECLARE @vReturnCode INT — For holding Return Code
DECLARE @vErrorCode INT
DECLARE @vRetCode INT DECLARE @vDatabaseName SYSNAME
DECLARE @vTableName SYSNAME
DECLARE @vIndexName SYSNAME DECLARE @vDefaultSupplierID INT
DECLARE @vDefaultSupplierSeriesNumber INT DECLARE @vTempPSDyValidCPSupplierID INT
DECLARE @vTempPSDyNoValidCPSupplierID INT
DECLARE @vCreateIndexStatement VARCHAR (2000) DECLARE @vTempTableName1 SYSNAME
DECLARE @vTempIndexName1 SYSNAME
DECLARE @vTempTableName2 SYSNAME
DECLARE @vTempIndexName2 SYSNAME
DECLARE @vTempTableName3 SYSNAME
DECLARE @vTempIndexName3 SYSNAME BEGIN SET @vReturnCode = 0
SET @vRetCode = 0
SET @pRowCnt = 0
SET @lvcSPName = OBJECT_NAME(@@PROCID) SET @vDatabaseName = ‘dmETL’
SET @vTableName = ‘SPSDYCostPrice’
SET @vIndexName = @vTableName + ‘_CL_IX’ SET @vTempTableName1 = ‘#TempPSDyValidCPSupplier’
SET @vTempIndexName1 = @vTempTableName1 + ‘_CL_IX’
SET @vTempTableName2 = ‘#TempPSDyNoValidCPSupplier’
SET @vTempIndexName2 = @vTempTableName2 + ‘_CL_IX’
SET @vTempTableName3 = ‘#TempPSDyValidNoValidCPSupplier’
SET @vTempIndexName3 = @vTempTableName3 + ‘_CL_IX’
SET @vTempPSDyNoValidCPSupplierID = 0
SET @vTempPSDyValidCPSupplierID = 0 EXEC @vRetCode = dbo.csp_DropTable @vDatabaseName, @vTableName, @pPrintSQL, @pExecuteSQL IF @vRetCode <> 0
BEGIN
SET @lvcTableName = ‘csp_DropTable’
SET @lvcErrorCode = ‘DMW3420’
SET @vReturnCode = -1
SET @lvcStepName = ‘SP: ‘ + @lvcTableName
GOTO OnError
END SELECT @vDefaultSupplierID = SupplierID,
@vDefaultSupplierSeriesNumber = SupplierSeriesNumber
FROM dbo.SPSDYSupplier (NOLOCK)
WHERE SupplierLongName = ‘DEFAULT’ SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR IF @lnError <> 0 OR @vRowCnt = 0 BEGIN
SET @lvcTableName = ‘SPSDYSupplier’
SET @lvcErrorCode = ‘DMW3421’
SET @vReturnCode = -1
SET @lvcStepName = ‘Select from ‘ + @lvcTableName
GOTO OnError
END SELECT ProductID, ProductSCDID, DeliveryMethodID
INTO #TempProduct
FROM dmBTK.dbo.Product (NOLOCK) SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR IF @lnError <> 0 OR @vRowCnt = 0 BEGIN
SET @lvcTableName = ‘#TempProduct’
SET @lvcErrorCode = ‘DMW3438’
SET @vReturnCode = -1
SET @lvcStepName = ‘Select from ‘ + @lvcTableName
GOTO OnError
END
SET @vCreateIndexStatement = ‘CREATE CLUSTERED INDEX #TempProduct_IX ON #TempProduct (ProductID, ProductSCDID)’ IF @pPrintSQL = 1 PRINT @vCreateIndexStatement IF @pExecuteSQL = 1 BEGIN EXEC (@vCreateIndexStatement) SELECT @lnError = @@ERROR IF @lnError <> 0
BEGIN
SET @lvcTableName = ‘#TempProduct’
SET @lvcErrorCode = ‘DMW3437’
SET @vReturnCode = -1
SET @lvcStepName = ‘Create Index #TempProduct_IX on: ‘ + @lvcTableName
GOTO OnError
END END
— Find all Valid or Invalid CP’s and a Count of CPs (By Supplier) within the Date Boundaries
— Note: Change 1 to @vDefaultSupplier
SELECT
LPSD.BusinessDateID,
LPSD.ProductID,
LPSD.StoreID,
LPSD.ProductSCDID,
ISNULL(SPSDCP.SupplierID, @vDefaultSupplierID) AS SupplierID,
CASE WHEN MIN(ISNULL(SPSDCP.StartDateID, 0)) = 0 THEN 0 ELSE COUNT(*) END AS CostPriceCount,
CASE WHEN MIN(ISNULL(SPSDCP.StartDateID, 0)) = 0 THEN 0 ELSE 1 END AS ValidCostPriceFlag
INTO #TempPSDyValidNoValidCPSupplier
FROM dbo.LoadProductStoreDay LPSD (NOLOCK)
LEFT OUTER JOIN dmBTK.dbo.SupplierProductStoreDayCostPrice SPSDCP (NOLOCK)
ON (SPSDCP.ProductID = LPSD.ProductID
AND SPSDCP.StoreID = LPSD.StoreID
–AND SPSDCP.ProductSCDID = LPSD.ProductSCDID — Live Fix QC: 1152
AND LPSD.BusinessDateID >= SPSDCP.StartDateID
AND LPSD.BusinessDateID <= ISNULL(SPSDCP.EndDateID, 99991231))
–OR (SPSDCP.StartDateID >= LPSD.BusinessDateID AND SPSDCP.StartDateID <= LPSD.BusinessDateID)) — Not needed
GROUP BY LPSD.BusinessDateID,
LPSD.ProductID,
LPSD.StoreID,
LPSD.ProductSCDID,
ISNULL(SPSDCP.SupplierID, @vDefaultSupplierID) SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR IF @lnError <> 0 BEGIN
SET @lvcTableName = ‘#TempPSDyValidNoValidCPSupplier’
SET @lvcErrorCode = ‘DMW3422’
SET @vReturnCode = -1
SET @lvcStepName = ‘Select into ‘ + @lvcTableName
GOTO OnError
END
SET @vCreateIndexStatement = ‘CREATE CLUSTERED INDEX ‘ + @vTempIndexName3 + ‘ ON ‘ + @vTempTableName3 + ‘ (BusinessDateID, ProductID, StoreID, SupplierID) ‘ IF @pPrintSQL = 1 PRINT @vCreateIndexStatement IF @pExecuteSQL = 1 BEGIN EXEC (@vCreateIndexStatement) SELECT @lnError = @@ERROR IF @lnError <> 0
BEGIN
SET @lvcTableName = @vTempTableName3
SET @lvcErrorCode = ‘DMW3437’
SET @vReturnCode = -1
SET @lvcStepName = ‘Create Index: ‘ + @vTempIndexName3 + ‘ on: ‘ + @lvcTableName
GOTO OnError
END END — For All CP’s with Multiple Supplier CP’s
SELECT CAST(NULL AS INT) AS TempPSDyValidCPSupplierID,
A.BusinessDateID,
A.ProductID,
A.StoreID,
A.ProductSCDID,
A.SupplierID,
S.SupplierSeriesNumber,
S.StartDateID AS SupplierSCDStartDateID, — Added 12.01.2007
S.EndDateID AS SupplierSCDEndDateID, — Added 12.01.2007
CASE WHEN SPSDCP.CalculatedWeightCostPriceFlag = 0 THEN SPSDCP.UnitCostPriceMajor ELSE SPSDCP.WeightCostPriceMajor END AS CostPriceMajor,
SPSDCP.UnitDutyChargeMajor AS DutyChargeMajor,
SPSDCP.StartDateID AS StartDateID,
ISNULL(SPSDCP.EndDateID, 99991231) AS EndDateID,
SPSDCP.CalculatedWeightCostPriceFlag,
1 AS SupplierCount,
A.CostPriceCount,
CAST(0 AS BIT) AS PreviousCostPriceFlag,
LBIPSCD.DeliveryMethodID
INTO #TempPSDyValidCPSupplier
FROM #TempPSDyValidNoValidCPSupplier A (NOLOCK)
INNER JOIN dmBTK.dbo.SupplierProductStoreDayCostPrice SPSDCP (NOLOCK)
ON (A.ProductID = SPSDCP.ProductID
AND A.StoreID = SPSDCP.StoreID
AND A.SupplierID = SPSDCP.SupplierID
AND A.BusinessDateID >= SPSDCP.StartDateID
AND A.BusinessDateID <= ISNULL(SPSDCP.EndDateID, 99991231))
INNER JOIN dbo.SPSDYSupplier S (NOLOCK)
ON (SPSDCP.SupplierID = S.SupplierID
AND A.BusinessDateID >= SPSDCP.StartDateID
AND A.BusinessDateID <= ISNULL(SPSDCP.EndDateID, 99991231))
INNER JOIN #TempProduct LBIPSCD (NOLOCK) — Use Data Mart data in a #Temp Table NOT the DCS Load Tables
ON (LBIPSCD.ProductSCDID = A.ProductSCDID
AND LBIPSCD.ProductID = A.ProductID) SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR IF @lnError <> 0 BEGIN
SET @lvcTableName = @vTempTableName1
SET @lvcErrorCode = ‘DMW3423’
SET @vReturnCode = -1
SET @lvcStepName = ‘Select into ‘ + @lvcTableName
GOTO OnError
END SET @vCreateIndexStatement = ‘CREATE CLUSTERED INDEX ‘ + @vTempIndexName1 + ‘ ON ‘ + @vTempTableName1 + ‘ (BusinessDateID, ProductID, StoreID, SupplierSeriesNumber, SupplierSCDStartDateID DESC, StartDateID DESC, CostPriceMajor DESC, SupplierID DESC, ProductSCDID DESC) ‘ IF @pPrintSQL = 1 PRINT @vCreateIndexStatement IF @pExecuteSQL = 1 BEGIN EXEC (@vCreateIndexStatement) SELECT @lnError = @@ERROR IF @lnError <> 0
BEGIN
SET @lvcTableName = @vTempTableName1
SET @lvcErrorCode = ‘DMW3424’
SET @vReturnCode = -1
SET @lvcStepName = ‘Create Index: ‘ + @vTempIndexName1 + ‘ on: ‘ + @lvcTableName
GOTO OnError
END END UPDATE #TempPSDyValidCPSupplier
SET @vTempPSDyValidCPSupplierID = TempPSDyValidCPSupplierID = @vTempPSDyValidCPSupplierID + 1 SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR IF @lnError <> 0 BEGIN
SET @lvcTableName = @vTempTableName1
SET @lvcErrorCode = ‘DMW3425’
SET @vReturnCode = -1
SET @lvcStepName = ‘Update of ‘ + @lvcTableName
GOTO OnError
END — For All CP’s with Multiple Supplier CP’s, find the Single ‘Most Valid CP’ Record
SELECT
A.TempPSDyValidCPSupplierID,
A.BusinessDateID,
A.ProductID,
A.StoreID,
A.ProductSCDID,
A.SupplierID,
A.SupplierSeriesNumber,
A.CostPriceMajor,
A.DutyChargeMajor,
A.StartDateID,
A.EndDateID,
A.CalculatedWeightCostPriceFlag,
A.SupplierCount,
A.CostPriceCount,
A.PreviousCostPriceFlag,
A.DeliveryMethodID
INTO #TempPSDyValidCPSupplierSingleCP
FROM #TempPSDyValidCPSupplier A (NOLOCK)
INNER JOIN
(
SELECT
MIN(TempPSDyValidCPSupplierID) AS TempPSDyValidCPSupplierID,
BusinessDateID,
ProductID,
StoreID,
SupplierSeriesNumber
FROM #TempPSDyValidCPSupplier (NOLOCK)
GROUP BY BusinessDateID,
ProductID,
StoreID,
SupplierSeriesNumber
) AS B
ON (A.BusinessDateID = B.BusinessDateID
AND A.ProductID = B.ProductID
AND A.StoreID = B.StoreID
AND A.SupplierSeriesNumber = B.SupplierSeriesNumber
AND A.TempPSDyValidCPSupplierID = B.TempPSDyValidCPSupplierID) SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR IF @lnError <> 0 BEGIN
SET @lvcTableName = ‘#TempPSDyValidCPSupplierSingleCP’
SET @lvcErrorCode = ‘DMW3426’
SET @vReturnCode = -1
SET @lvcStepName = ‘Select into ‘ + @lvcTableName
GOTO OnError
END — Obtain Delivery Info for these no Valid Cost Prices
SELECT
X.BusinessDateID,
X.ProductID,
X.StoreID,
X.ProductSCDID,
LBIPSCD.DeliveryMethodID,
B.StartDateID,
B.EndDateID,
ISNULL(B.DepotID, -1) AS DepotID — Default Depot
INTO #TempPSDyNoValidCPDelivery
FROM #TempPSDyValidNoValidCPSupplier X
–INNER JOIN dbo.LoadBIProductSCD LBIPSCD (NOLOCK) — Live Performance Fix use Data Mart
INNER JOIN #TempProduct LBIPSCD (NOLOCK) — Use Data Mart data in a #Temp Table NOT the DCS Load Tables
ON (LBIPSCD.ProductSCDID = X.ProductSCDID
AND LBIPSCD.ProductID = X.ProductID)
LEFT OUTER JOIN dbo.SPSDYStoreDepotDeliveryMethod B (NOLOCK)
ON (B.StoreID = X.StoreID
AND B.DeliveryMethodID = LBIPSCD.DeliveryMethodID
AND X.BusinessDateID >= B.StartDateID
AND X.BusinessDateID <= ISNULL(B.EndDateID, 99991231))
–OR (B.StartDateID >= X.BusinessDateID AND B.StartDateID <= X.BusinessDateID)) — Not Needed
WHERE X.ValidCostPriceFlag = 0 SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR IF @lnError <> 0 BEGIN
SET @lvcTableName = ‘#TempPSDyNoValidCPDelivery’
SET @lvcErrorCode = ‘DMW3427’
SET @vReturnCode = -1
SET @lvcStepName = ‘Select into ‘ + @lvcTableName
GOTO OnError
END — Find all Records where there have been a Delivery from 1 or more Suppliers (the number of Suppliers is irrelevant) these will be set to the Default Supplier
SELECT
A.BusinessDateID,
A.ProductID,
A.ProductSCDID,
A.StoreID,
A.DepotID,
A.DeliveryMethodID
INTO dbo.#TempPSDyNoValidCPDefaultSupplier
FROM dbo.#TempPSDyNoValidCPDelivery A (NOLOCK) — What Depot is used to deliver a Product to a Store for a BusinessDate
INNER JOIN dbo.SPSDYSupplierProductDepotDelivery B (NOLOCK) –List of all Deliveries from a Supplier to a Depot
ON (A.ProductID = B.ProductID
AND A.DepotID = B.DepotID
AND B.DeliveryDateID < A.BusinessDateID)
GROUP BY A.BusinessDateID, A.ProductID, A.ProductSCDID, A.StoreID, A.DepotID, A.DeliveryMethodID SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR IF @lnError <> 0 BEGIN
SET @lvcTableName = ‘#TempPSDyNoValidCPDefaultSupplier’
SET @lvcErrorCode = ‘DMW3428’
SET @vReturnCode = -1
SET @lvcStepName = ‘Select into ‘ + @lvcTableName
GOTO OnError
END — Find all No Delivery Records where there could have been a possible previous Cost Price
SELECT
A.BusinessDateID,
A.ProductID,
A.ProductSCDID,
A.StoreID,
A.DepotID,
A.DeliveryMethodID
INTO dbo.#TempPSDyNoValidCPPossibleMostRecentCP
FROM dbo.#TempPSDyNoValidCPDelivery A (NOLOCK)
LEFT OUTER JOIN dbo.#TempPSDyNoValidCPDefaultSupplier B (NOLOCK)
ON (A.BusinessDateID = B.BusinessDateID
AND A.ProductID = B.ProductID
AND A.StoreID = B.StoreID
AND A.DepotID = B.DepotID
AND A.DeliveryMethodID = B.DeliveryMethodID)
WHERE B.BusinessDateID IS NULL SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR IF @lnError <> 0 BEGIN
SET @lvcTableName = ‘#TempPSDyNoValidCPPossibleMostRecentCP’
SET @lvcErrorCode = ‘DMW3429’
SET @vReturnCode = -1
SET @lvcStepName = ‘Select into ‘ + @lvcTableName
GOTO OnError
END — Find all Records where there are previous valid Cost Prices but there have not been any Deliveries
SELECT
X.ProductID,
X.ProductSCDID,
X.StoreID,
X.SupplierID,
S.SupplierSeriesNumber,
S.StartDateID AS SupplierSCDStartDateID,
S.EndDateID AS SupplierSCDEndDateID,
X.CostPriceMajor,
X.DutyChargeMajor,
X.MinBusinessDateID,
X.MaxBusinessDateID,
X.CalculatedWeightCostPriceFlag,
1 AS SupplierCount,
1 AS PreviousCostPriceFlag,
X.DeliveryMethodID,
X.DepotID
INTO dbo.#TempPSDyNoValidCPMostRecentCP
FROM
(
SELECT — Without ProductSCDID
B.ProductID,
A.ProductSCDID,
B.StoreID,
B.SupplierID,
CASE WHEN B.CalculatedWeightCostPriceFlag = 0 THEN B.UnitCostPriceMajor ELSE B.WeightCostPriceMajor END AS CostPriceMajor,
B.UnitDutyChargeMajor AS DutyChargeMajor,
A.MinBusinessDateID,
A.MaxBusinessDateID,
B.CalculatedWeightCostPriceFlag,
A.DeliveryMethodID,
A.DepotID
FROM dmBTK.dbo.SupplierProductStoreDayCostPrice B (NOLOCK)
INNER JOIN
(
SELECT
A.ProductID,
A.StoreID,
A.ProductSCDID,
A.DeliveryMethodID,
A.DepotID,
MAX(B.StartDateID) AS PreviousStartDateID,
MAX(ISNULL(B.EndDateID, 99991231)) AS PreviousEndDateID,
MIN(A.BusinessDateID) AS MinBusinessDateID,
MAX(A.BusinessDateID) AS MaxBusinessDateID
FROM #TempPSDyNoValidCPPossibleMostRecentCP A (NOLOCK)
INNER JOIN dmBTK.dbo.SupplierProductStoreDayCostPrice B (NOLOCK)
ON B.ProductID = A.ProductID
AND B.StoreID = A.StoreID
AND B.StartDateID <= A.BusinessDateID
AND B.EndDateID <= A.BusinessDateID
GROUP BY A.ProductID, A.StoreID, A.ProductSCDID, A.DeliveryMethodID, A.DepotID
) AS A
ON A.ProductID = B.ProductID
AND A.StoreID = B.StoreID
AND A.PreviousStartDateID = B.StartDateID
AND A.PreviousEndDateID = ISNULL(B.EndDateID, 99991231) UNION ALL SELECT — With ProductSCDID
B.ProductID,
A.ProductSCDID,
B.StoreID,
B.SupplierID,
CASE WHEN B.CalculatedWeightCostPriceFlag = 0 THEN B.UnitCostPriceMajor ELSE B.WeightCostPriceMajor END AS CostPriceMajor,
B.UnitDutyChargeMajor AS DutyChargeMajor,
A.MinBusinessDateID,
A.MaxBusinessDateID,
B.CalculatedWeightCostPriceFlag,
A.DeliveryMethodID,
A.DepotID
FROM dmBTK.dbo.SupplierProductStoreDayCostPrice B (NOLOCK)
INNER JOIN
(
SELECT
A.ProductID,
A.StoreID,
A.ProductSCDID,
A.DeliveryMethodID,
A.DepotID,
MAX(B.StartDateID) AS PreviousStartDateID,
MAX(ISNULL(B.EndDateID, 99991231)) AS PreviousEndDateID,
MIN(A.BusinessDateID) AS MinBusinessDateID,
MAX(A.BusinessDateID) AS MaxBusinessDateID
FROM #TempPSDyNoValidCPPossibleMostRecentCP A (NOLOCK)
INNER JOIN dmBTK.dbo.SupplierProductStoreDayCostPrice B (NOLOCK)
ON B.ProductID = A.ProductID
AND B.StoreID = A.StoreID
AND B.ProductSCDID = A.ProductSCDID
AND B.StartDateID <= A.BusinessDateID
AND B.EndDateID <= A.BusinessDateID
GROUP BY A.ProductID, A.StoreID, A.ProductSCDID, A.DeliveryMethodID, A.DepotID
) AS A
ON A.ProductID = B.ProductID
AND A.StoreID = B.StoreID
AND A.ProductSCDID = B.ProductSCDID
AND A.PreviousStartDateID = B.StartDateID
AND A.PreviousEndDateID = ISNULL(B.EndDateID, 99991231)
) AS X
INNER JOIN dbo.SPSDYSupplier S (NOLOCK)
ON (S.SupplierID = X.SupplierID) SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR IF @lnError <> 0 BEGIN
SET @lvcTableName = ‘#TempPSDyNoValidCPMostRecentCP’
SET @lvcErrorCode = ‘DMW3430’
SET @vReturnCode = -1
SET @lvcStepName = ‘Select into ‘ + @lvcTableName
GOTO OnError
END — Find All Most Recent Cost Prices (could have multiple records)
SELECT CAST(NULL AS INT) AS TempPSDyNoValidCPSupplierID,
LPSD.BusinessDateID,
A.ProductID,
A.StoreID,
A.ProductSCDID,
A.SupplierID,
A.SupplierSeriesNumber,
A.SupplierSCDStartDateID,
A.SupplierSCDEndDateID,
A.CostPriceMajor,
A.DutyChargeMajor,
A.MinBusinessDateID AS StartDateID,
A.MaxBusinessDateID AS EndDateID,
A.CalculatedWeightCostPriceFlag,
1 AS SupplierCount,
COUNT(*) AS CostPriceCount,
CAST(1 AS BIT) AS PreviousCostPriceFlag,
A.DeliveryMethodID
INTO #TempPSDyNoValidCPSupplier
FROM #TempPSDyNoValidCPMostRecentCP A (NOLOCK)
INNER JOIN dbo.LoadProductStoreDay LPSD (NOLOCK)
ON A.ProductID = LPSD.ProductID
AND A.StoreID = LPSD.StoreID
AND A.ProductSCDID = LPSD.ProductSCDID
AND A.MinBusinessDateID <= LPSD.BusinessDateID
AND A.MaxBusinessDateID >= LPSD.BusinessDateID
GROUP BY LPSD.BusinessDateID,
A.ProductID,
A.StoreID,
A.ProductSCDID,
A.SupplierID,
A.SupplierSeriesNumber,
A.SupplierSCDStartDateID,
A.SupplierSCDEndDateID,
A.CostPriceMajor,
A.DutyChargeMajor,
A.MinBusinessDateID,
A.MaxBusinessDateID,
A.CalculatedWeightCostPriceFlag,
A.DeliveryMethodID SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR IF @lnError <> 0 BEGIN
SET @lvcTableName = ‘#TempPSDyNoValidCPSupplier’
SET @lvcErrorCode = ‘DMW3431’
SET @vReturnCode = -1
SET @lvcStepName = ‘Select into ‘ + @lvcTableName
GOTO OnError
END SET @vCreateIndexStatement = ‘CREATE CLUSTERED INDEX ‘ + @vTempIndexName2 + ‘ ON ‘ + @vTempTableName2 + ‘ (BusinessDateID, ProductID, StoreID, SupplierSeriesNumber, SupplierSCDStartDateID DESC, StartDateID DESC, CostPriceMajor DESC, SupplierID DESC, ProductSCDID) ‘
IF @pPrintSQL = 1 PRINT @vCreateIndexStatement IF @pExecuteSQL = 1 BEGIN EXEC (@vCreateIndexStatement) SELECT @lnError = @@ERROR IF @lnError <> 0
BEGIN
SET @lvcTableName = @vTempTableName2
SET @lvcErrorCode = ‘DMW3432’
SET @vReturnCode = -1
SET @lvcStepName = ‘Create Index: ‘ + @vTempIndexName2 + ‘ on: ‘ + @lvcTableName
GOTO OnError
END END UPDATE #TempPSDyNoValidCPSupplier
SET @vTempPSDyNoValidCPSupplierID = TempPSDyNoValidCPSupplierID = @vTempPSDyNoValidCPSupplierID + 1 SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR IF @lnError <> 0 BEGIN
SET @lvcTableName = @vTempTableName2
SET @lvcErrorCode = ‘DMW3433’
SET @vReturnCode = -1
SET @lvcStepName = ‘Update of ‘ + @lvcTableName
GOTO OnError
END — For All Most Recent CP’s, find the Single ‘Most Valid CP’ Record
SELECT
A.TempPSDyNoValidCPSupplierID,
A.BusinessDateID,
A.ProductID,
A.StoreID,
A.ProductSCDID,
A.SupplierID,
A.SupplierSeriesNumber,
A.CostPriceMajor,
A.DutyChargeMajor,
A.StartDateID,
A.EndDateID,
A.CalculatedWeightCostPriceFlag,
A.SupplierCount,
A.CostPriceCount,
A.PreviousCostPriceFlag,
A.DeliveryMethodID
INTO #TempPSDyNoValidCPSupplierSingleCP
FROM #TempPSDyNoValidCPSupplier A (NOLOCK)
INNER JOIN
(
SELECT
MIN(TempPSDyNoValidCPSupplierID) AS TempPSDyNoValidCPSupplierID,
BusinessDateID,
ProductID,
StoreID, SupplierSeriesNumber
FROM #TempPSDyNoValidCPSupplier (NOLOCK)
GROUP BY BusinessDateID,
ProductID,
StoreID,
SupplierSeriesNumber
) AS B
ON (A.BusinessDateID = B.BusinessDateID
AND A.ProductID = B.ProductID
AND A.StoreID = B.StoreID
AND A.SupplierSeriesNumber = B.SupplierSeriesNumber
AND A.TempPSDyNoValidCPSupplierID = B.TempPSDyNoValidCPSupplierID) SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR IF @lnError <> 0 BEGIN
SET @lvcTableName = ‘#TempPSDyNoValidCPSupplierSingleCP’
SET @lvcErrorCode = ‘DMW3434’
SET @vReturnCode = -1
SET @lvcStepName = ‘Select into ‘ + @lvcTableName
GOTO OnError
END
— Find all Records with no Previous Cost Price Records or where there have been Deliveries . These will be allocated to the Default Supplier
SELECT
A.BusinessDateID,
A.ProductID,
A.ProductSCDID,
A.StoreID,
@vDefaultSupplierID AS SupplierID,
@vDefaultSupplierSeriesNumber AS SupplierSeriesNumber,
A.StartDateID,
A.EndDateID,
CAST(NULL AS SMALLMONEY) AS CostPriceMajor,
CAST(NULL AS SMALLMONEY) AS DutyChargeMajor,
CAST(0 AS BIT) AS CalculatedWeightCostPriceFlag,
1 AS SupplierCount,
0 AS CostPriceCount,
CAST(0 AS BIT) AS PreviousCostPriceFlag,
A.DeliveryMethodID
INTO #TempPSDyNoValidCP
FROM
(
SELECT
A.BusinessDateID,
A.ProductID,
A.ProductSCDID,
A.StoreID,
A.BusinessDateID AS StartDateID,
A.BusinessDateID AS EndDateID,
A.DeliveryMethodID
FROM #TempPSDyNoValidCPPossibleMostRecentCP A (NOLOCK)
LEFT OUTER JOIN #TempPSDyNoValidCPSupplierSingleCP B (NOLOCK)
–LEFT OUTER JOIN #TempPSDyNoValidCPSupplier B (NOLOCK)
ON (A.BusinessDateID = B.BusinessDateID
AND A.ProductID = B.ProductID
AND A.StoreID = B.StoreID
AND A.ProductSCDID = B.ProductSCDID)
WHERE B.BusinessDateID IS NULL UNION SELECT
A.BusinessDateID,
A.ProductID,
A.ProductSCDID,
A.StoreID,
A.BusinessDateID AS StartDateID,
A.BusinessDateID AS EndDateID,
A.DeliveryMethodID
FROM #TempPSDyNoValidCPDefaultSupplier A (NOLOCK)
) AS A SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR IF @lnError <> 0 BEGIN
SET @lvcTableName = ‘#TempPSDyNoValidCP’
SET @lvcErrorCode = ‘DMW3435’
SET @vReturnCode = -1
SET @lvcStepName = ‘Select into ‘ + @lvcTableName
GOTO OnError
END SELECT
IDENTITY(INT, 1, 1) AS SPSDYCostPriceID,
A.BusinessDateID,
A.ProductID,
A.ProductSCDID,
A.StoreID,
A.SupplierID,
A.SupplierSeriesNumber,
A.CostPriceMajor,
A.DutyChargeMajor,
A.StartDateID,
A.EndDateID,
A.CalculatedWeightCostPriceFlag,
A.SupplierCount,
A.CostPriceCount,
A.PreviousCostPriceFlag,
A.DeliveryMethodID
INTO dbo.SPSDYCostPrice
FROM
(
SELECT
BusinessDateID,
ProductID,
ProductSCDID,
StoreID,
SupplierID,
SupplierSeriesNumber,
CostPriceMajor,
DutyChargeMajor,
StartDateID,
EndDateID,
CalculatedWeightCostPriceFlag,
SupplierCount,
CostPriceCount,
PreviousCostPriceFlag,
DeliveryMethodID
–FROM #TempPSDyValidCPSupplier (NOLOCK)
FROM #TempPSDyValidCPSupplierSingleCP (NOLOCK) UNION ALL SELECT
BusinessDateID,
ProductID,
ProductSCDID,
StoreID,
SupplierID,
SupplierSeriesNumber,
CostPriceMajor,
DutyChargeMajor,
StartDateID,
EndDateID,
CalculatedWeightCostPriceFlag,
SupplierCount,
CostPriceCount,
PreviousCostPriceFlag,
DeliveryMethodID
–FROM #TempPSDyNoValidCPSupplier (NOLOCK)
FROM #TempPSDyNoValidCPSupplierSingleCP (NOLOCK) UNION ALL SELECT
BusinessDateID,
ProductID,
ProductSCDID,
StoreID,
SupplierID,
SupplierSeriesNumber,
CostPriceMajor,
DutyChargeMajor,
StartDateID,
EndDateID,
CalculatedWeightCostPriceFlag,
SupplierCount,
CostPriceCount,
PreviousCostPriceFlag,
DeliveryMethodID
FROM #TempPSDyNoValidCP (NOLOCK)
) AS A SELECT @vRowCnt = @@ROWCOUNT, @lnError = @@ERROR IF @lnError <> 0 BEGIN
SET @lvcTableName = @vTableName
SET @lvcErrorCode = ‘DMW3436’
SET @vReturnCode = -1
SET @lvcStepName = ‘Select Into ‘ + @lvcTableName
GOTO OnError
END SELECT @pRowCnt = @vRowCnt GOTO OnSuccess END OnError:
BEGIN SET @vErrorCode = CONVERT(INT, RIGHT(@lvcErrorCode, 4)) RAISERROR (50510, 16, 1, @lvcSPName, @lvcStepName, @vErrorCode) GOTO OnSuccess END OnSuccess: BEGIN DROP TABLE #TempPSDyNoValidCP
DROP TABLE #TempPSDyNoValidCPSupplier
DROP TABLE #TempPSDyNoValidCPSupplierSingleCP
DROP TABLE #TempPSDyNoValidCPMostRecentCP
DROP TABLE #TempPSDyNoValidCPPossibleMostRecentCP
DROP TABLE #TempPSDyNoValidCPDefaultSupplier
DROP TABLE #TempPSDyNoValidCPDelivery
DROP TABLE #TempPSDyValidNoValidCPSupplier
DROP TABLE #TempPSDyValidCPSupplier
DROP TABLE #TempPSDyValidCPSupplierSingleCP
DROP TABLE #TempProduct RETURN (@vReturnCode) END Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
We don’t have to look at the actual procedure, it’s considered a best practice to clean up after yourself.
As referred you could explicity mention to drop the temp tables that are created during this process, if not you can take help of PROFILER to see what are created to make a note of them. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
quote:Originally posted by Adriaan We don’t have to look at the actual procedure, it’s considered a best practice to clean up after yourself.

I have pasted the proc as Satya asked to check for tuning possiblities. Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
Have you checked what I have referred after you have posted, it seems this relates to other tables too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>