SQL Server Performance

Dropping #temptables

Discussion in 'T-SQL Performance Tuning for Developers' started by ramkumar.mu, Jul 24, 2007.

  1. ramkumar.mu New Member

    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..."
  2. Adriaan New Member

    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.
  3. satya Moderator

    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.
  4. ndinakar Member

    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/
  5. ramkumar.mu New Member

    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..."
  6. Adriaan New Member

    We don't have to look at the actual procedure, it's considered a best practice to clean up after yourself.
  7. satya Moderator

    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.
  8. ramkumar.mu New Member

    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..."
  9. satya Moderator

    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.

Share This Page