Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65

    Unanswered: T-SQL: Tables vs. Temp Tables (and general performance)

    I was wondering if there is any performance difference between creating an actual table in a DB and inserting records in a Stored Procedure vs. creating a temp table in the Stored Procedure (i.e. CREATE TABLE #MyTempTable .....) and inserting records.

    I need to create one of those massive "everything in the database, hundreds of pages ", overview reports, which consists of about 40 elements of caclulated data per product. It will be three levels deep:

    Generic Example:
    1. Calculated data elements for Cars.
    2. Calculated data elements for all of a Car's Parts.
    3. Calculated data elements for all of a Car's Part's Components.

    Example Report Format:
    Code:
    Product                Curr 12 Months    Jan   Feb   Mar   Apr   May   Jun   Jul   Aug   Sep   Oct   Nov   Dec   YTD   PrevYtd   %Change
    --------------------------------------------------------------------------------------------------------------------------------------------------
    Car1
        Element1
        Element2
        Element3
    
    Car Part1
        Element1
        Element2
    
    Car Part2
    
        Element1
        Element2
    
    Car Part3
        Element1
        Element2
    
    Car PartN
        Element1
        Element2
    
        Car Part Element1
        Car Part Element2
        Car Part Element3
        Car Part ElementN
    
    ------------------------------------------
    
    Car2
        Element1
        Element2
        Element3
    
    ... etc
    As of now, I completed part 1 above, and the SP takes about 10 minutes to run. It is using a T-SQL created temp table (#MyTempTable). I was wondering, before I recode anything, if anyone knows if my SP will run faster if I actually create the table in the DB instead? I figure that once I complete parts 2 and 3 above, the SP could take 45 minutes to run.
    Last edited by HardCode; 03-15-06 at 13:44.
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    there might be 3rd way. how about following your own advice?

    http://www.dbforums.com/showthread.php?t=1214030
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    Umm, I don't see how posting my SP will answer the question if using a temp table is faster/slower than using a "real" table, but if you insist:

    Part 1:
    Code:
    CREATE PROCEDURE APPS_AAAReport_GetTotals_TEST
    @ErrNum        AS INT OUTPUT,
    @ErrMsg        AS VARCHAR(4000) OUTPUT
    AS
    
    BEGIN
    
    SET NOCOUNT ON
    
    BEGIN TRAN T1
    
    ------------------------------------------------------------------------------------------
    -- Declarations
    ------------------------------------------------------------------------------------------
    DECLARE @CurrMonth        AS SMALLINT
    DECLARE @CurrYear        AS SMALLINT
    DECLARE @Start            AS VARCHAR(50)
    DECLARE @End            AS VARCHAR(50)
    DECLARE @ThisJproID        AS INT
    DECLARE @ThisJproCode        AS VARCHAR(200)
    
    DECLARE @Curr12            AS VARCHAR(50)
    DECLARE @Month1             AS VARCHAR(50)
    DECLARE @Month2             AS VARCHAR(50)
    DECLARE @Month3             AS VARCHAR(50)
    DECLARE @Month4             AS VARCHAR(50)
    DECLARE @Month5             AS VARCHAR(50)
    DECLARE @Month6             AS VARCHAR(50)
    DECLARE @Month7             AS VARCHAR(50)
    DECLARE @Month8             AS VARCHAR(50)
    DECLARE @Month9             AS VARCHAR(50)
    DECLARE @Month10         AS VARCHAR(50)
    DECLARE @Month11         AS VARCHAR(50)
    DECLARE @Month12         AS VARCHAR(50)
    DECLARE @PrevYtd        AS VARCHAR(50)
    DECLARE @Ytd            AS VARCHAR(50)
    DECLARE @YtdChange        AS VARCHAR(50)
    
    DECLARE @Loop            AS SMALLINT
    DECLARE @TempDate        AS VARCHAR(50)
    DECLARE @ThisMonth        AS INT
    DECLARE @ThisYear        AS INT
    
    
    ------------------------------------------------------------------------------------------
    -- Set up a temp table for the report data
    ------------------------------------------------------------------------------------------
    /* 
    Row_Type:
        1 = Product
        2 = Products Ads
        3 = Products Ad Books
    
    Item_Type:
        1 = Insertions
        2 = Pages
        3 = Dollars
    
    */
    CREATE TABLE #Table4Report     (Jpro_ID INTEGER,
                    Jpro_Code VARCHAR(50), 
                    Row_Type SMALLINT, 
                    Item_Type SMALLINT, 
                    Jnad_ID INTEGER, 
                    Jmed_Id INTEGER, 
                    Curr_12_Months VARCHAR(50), 
                    Month_1 VARCHAR(50), Month_2 VARCHAR(50), Month_3 VARCHAR(50), Month_4 VARCHAR(50), 
                    Month_5 VARCHAR(50), Month_6 VARCHAR(50), Month_7 VARCHAR(50), Month_8 VARCHAR(50), 
                    Month_9 VARCHAR(50), Month_10 VARCHAR(50), Month_11 VARCHAR(50), Month_12 VARCHAR(50), 
                    Previous_Ytd VARCHAR(50), 
                    Yr_to_Date VARCHAR(50), 
                    Ytd_Change VARCHAR(50))
    
    ------------------------------------------------------------------------------------------
    -- Get the current month and year and set the start and end dates for this 12 month cycle
    ------------------------------------------------------------------------------------------
    
    -- For TESTING ONLY, hardcode the current month and year
    
    SET @CurrMonth = 1
    SET @CurrYear = 2006
    /*
    SELECT         @CurrMonth = CurrentMonth, 
            @CurrYear = CurrentYear
    FROM        AAACurrentDate
    */
    
    
    
    SET @End = dbo.fn_AAA_GetEndDate(@CurrMonth, @CurrYear)
    SET @Start = dbo.fn_AAA_GetStartDate(@End)
    
    ------------------------------------------------------------------------------------------
    -- Get the AAA Products for the report
    ------------------------------------------------------------------------------------------
    DECLARE curAAAProducts CURSOR STATIC LOCAL READ_ONLY FOR
        SELECT        JND_COST_FACT_TB.JPRO_ID, 
                JND_PRODUCT_DIM_TB.JPRO_PRODUCT_CDE
        FROM             JND_COST_FACT_TB 
                INNER JOIN JND_PRODUCT_DIM_TB ON JND_COST_FACT_TB.JPRO_ID = JND_PRODUCT_DIM_TB.JPRO_ID 
                INNER JOIN JND_AAA_GROUP_DIM_TB ON JND_COST_FACT_TB.JGRP_ID = JND_AAA_GROUP_DIM_TB.JGRP_ID 
                INNER JOIN JND_CLASS_DIM_TB ON JND_COST_FACT_TB.JCLA_ID = JND_CLASS_DIM_TB.JCLA_ID
        WHERE         JND_COST_FACT_TB.JTME_ISSUE_DTE BETWEEN CONVERT(DATETIME, @Start, 102) AND CONVERT(DATETIME, @End, 102) 
                AND (JND_CLASS_DIM_TB.JCLA_CLASS_CODE_TYP = '0')
        GROUP BY     JND_COST_FACT_TB.JPRO_ID, JND_PRODUCT_DIM_TB.JPRO_PRODUCT_CDE
        HAVING          COUNT(JND_COST_FACT_TB.JPRO_ID) > 0
                -- ********************** TEMP CONDITION *******************
                --AND JND_COST_FACT_TB.JPRO_ID = 693397
                AND JND_PRODUCT_DIM_TB.JPRO_PRODUCT_CDE LIKE 'A%'
        ORDER BY     JND_PRODUCT_DIM_TB.JPRO_PRODUCT_CDE
    
    OPEN curAAAProducts
    FETCH NEXT FROM curAAAProducts INTO @ThisJproID, @ThisJproCode
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        ------------------------------------------------------------------------------------------
        -- INSERTIONS
        ------------------------------------------------------------------------------------------
        -- Process current 12 months
        SET @Curr12 = dbo.fn_AAA_Curr12MonthInsertions_Product (@ThisJproID, @Start, @End)
        
        -- Process last 12 months
        -- 11 months back
        SET @TempDate = DATEADD(MONTH, -11, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month1 = dbo.fn_AAA_MonthlyInsertions_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 10 months back
        SET @TempDate = DATEADD(MONTH, -10, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month2 = dbo.fn_AAA_MonthlyInsertions_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 9 months back
        SET @TempDate = DATEADD(MONTH, -9, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month3 = dbo.fn_AAA_MonthlyInsertions_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 8 months back
        SET @TempDate = DATEADD(MONTH, -8, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month4 = dbo.fn_AAA_MonthlyInsertions_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 7 months back
        SET @TempDate = DATEADD(MONTH, -7, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month5 = dbo.fn_AAA_MonthlyInsertions_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 6 months back
        SET @TempDate = DATEADD(MONTH, -6, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month6 = dbo.fn_AAA_MonthlyInsertions_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 5 months back
        SET @TempDate = DATEADD(MONTH, -5, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month7 = dbo.fn_AAA_MonthlyInsertions_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 4 months back
        SET @TempDate = DATEADD(MONTH, -4, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month8 = dbo.fn_AAA_MonthlyInsertions_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 3 months back
        SET @TempDate = DATEADD(MONTH, -3, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month9 = dbo.fn_AAA_MonthlyInsertions_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 2 months back
        SET @TempDate = DATEADD(MONTH, -2, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month10 = dbo.fn_AAA_MonthlyInsertions_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 1 months back
        SET @TempDate = DATEADD(MONTH, -1, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month11 = dbo.fn_AAA_MonthlyInsertions_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- This month
        SET @Month12 = dbo.fn_AAA_MonthlyInsertions_Product (@ThisJproID, @CurrMonth, @CurrYear)
    
        -- Process previous year-to-date
        SET @PrevYtd = dbo.fn_AAA_PrevYtdInsertions_Product (@ThisJproID, @End, @CurrYear)
        -- Process year-to-date
        SET @Ytd = dbo.fn_AAA_YtdInsertions_Product (@ThisJproID, @End, @CurrYear)
        -- Process year-to-date change
        SET @YtdChange = dbo.fn_AAA_FormatYtdChange (@Ytd , @PrevYtd)
    
        -- TODO: Expand the INSERT to include the months
        INSERT INTO #Table4Report    (Jpro_ID, Jpro_Code, Row_Type, Item_Type, Jnad_ID, Jmed_Id, Curr_12_Months, 
                        Month_1, Month_2, Month_3, Month_4, Month_5, Month_6, 
                        Month_7, Month_8, Month_9, Month_10, Month_11, Month_12, 
                        Previous_Ytd, Yr_to_Date, Ytd_Change)
        VALUES                (@ThisJproID, @ThisJproCode, 1, 1, 0, 0, @Curr12, 
                        @Month1, @Month2, @Month3, @Month4, @Month5, @Month6,
                        @Month7, @Month8, @Month9, @Month10, @Month11, @Month12,
                        @PrevYtd, @Ytd, @YtdChange)
        SET @ErrNum = @@ERROR
        IF @ErrNum <> 0 GOTO ERROR_TRAP
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  4. #4
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    Part 2:
    Code:
        ------------------------------------------------------------------------------------------
        -- PAGES
        ------------------------------------------------------------------------------------------
        -- Process current 12 months
        SET @Curr12 = dbo.fn_AAA_Curr12MonthPages_Product (@ThisJproID, @Start, @End)
        
        -- Process last 12 months
        -- 11 months back
        SET @TempDate = DATEADD(MONTH, -11, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month1 = dbo.fn_AAA_MonthlyPages_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 10 months back
        SET @TempDate = DATEADD(MONTH, -10, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month2 = dbo.fn_AAA_MonthlyPages_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 9 months back
        SET @TempDate = DATEADD(MONTH, -9, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month3 = dbo.fn_AAA_MonthlyPages_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 8 months back
        SET @TempDate = DATEADD(MONTH, -8, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month4 = dbo.fn_AAA_MonthlyPages_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 7 months back
        SET @TempDate = DATEADD(MONTH, -7, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month5 = dbo.fn_AAA_MonthlyPages_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 6 months back
        SET @TempDate = DATEADD(MONTH, -6, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month6 = dbo.fn_AAA_MonthlyPages_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 5 months back
        SET @TempDate = DATEADD(MONTH, -5, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month7 = dbo.fn_AAA_MonthlyPages_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 4 months back
        SET @TempDate = DATEADD(MONTH, -4, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month8 = dbo.fn_AAA_MonthlyPages_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 3 months back
        SET @TempDate = DATEADD(MONTH, -3, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month9 = dbo.fn_AAA_MonthlyPages_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 2 months back
        SET @TempDate = DATEADD(MONTH, -2, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month10 = dbo.fn_AAA_MonthlyPages_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 1 months back
        SET @TempDate = DATEADD(MONTH, -1, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month11 = dbo.fn_AAA_MonthlyPages_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- This month
        SET @Month12 = dbo.fn_AAA_MonthlyPages_Product (@ThisJproID, @CurrMonth, @CurrYear)
    
        -- Process previous year-to-date
        SET @PrevYtd = dbo.fn_AAA_PrevYtdPages_Product (@ThisJproID, @End, @CurrYear)
        -- Process year-to-date
        SET @Ytd = dbo.fn_AAA_YtdPages_Product (@ThisJproID, @End, @CurrYear)
        -- Process year-to-date change
        SET @YtdChange = dbo.fn_AAA_FormatYtdChange (@Ytd , @PrevYtd)
    
        -- TODO: Expand the INSERT to include the months
        INSERT INTO #Table4Report    (Jpro_ID, Jpro_Code, Row_Type, Item_Type, Jnad_ID, Jmed_Id, Curr_12_Months, 
                        Month_1, Month_2, Month_3, Month_4, Month_5, Month_6, 
                        Month_7, Month_8, Month_9, Month_10, Month_11, Month_12, 
                        Previous_Ytd, Yr_to_Date, Ytd_Change)
        VALUES                (@ThisJproID, @ThisJproCode, 1, 2, 0, 0, @Curr12, 
                        @Month1, @Month2, @Month3, @Month4, @Month5, @Month6,
                        @Month7, @Month8, @Month9, @Month10, @Month11, @Month12,
                        @PrevYtd, @Ytd, @YtdChange)
        SET @ErrNum = @@ERROR
        IF @ErrNum <> 0 GOTO ERROR_TRAP
    
        ------------------------------------------------------------------------------------------
        -- Dollars
        ------------------------------------------------------------------------------------------
        -- Process current 12 months
        SET @Curr12 = dbo.fn_AAA_Curr12MonthDollars_Product (@ThisJproID, @Start, @End)
        
        -- Process last 12 months
        -- 11 months back
        SET @TempDate = DATEADD(MONTH, -11, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month1 = dbo.fn_AAA_MonthlyDollars_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 10 months back
        SET @TempDate = DATEADD(MONTH, -10, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month2 = dbo.fn_AAA_MonthlyDollars_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 9 months back
        SET @TempDate = DATEADD(MONTH, -9, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month3 = dbo.fn_AAA_MonthlyDollars_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 8 months back
        SET @TempDate = DATEADD(MONTH, -8, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month4 = dbo.fn_AAA_MonthlyDollars_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 7 months back
        SET @TempDate = DATEADD(MONTH, -7, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month5 = dbo.fn_AAA_MonthlyDollars_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 6 months back
        SET @TempDate = DATEADD(MONTH, -6, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month6 = dbo.fn_AAA_MonthlyDollars_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 5 months back
        SET @TempDate = DATEADD(MONTH, -5, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month7 = dbo.fn_AAA_MonthlyDollars_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 4 months back
        SET @TempDate = DATEADD(MONTH, -4, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month8 = dbo.fn_AAA_MonthlyDollars_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 3 months back
        SET @TempDate = DATEADD(MONTH, -3, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month9 = dbo.fn_AAA_MonthlyDollars_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 2 months back
        SET @TempDate = DATEADD(MONTH, -2, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month10 = dbo.fn_AAA_MonthlyDollars_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- 1 months back
        SET @TempDate = DATEADD(MONTH, -1, @End)
        SET @ThisMonth = DATEPART(MONTH, @TempDate)
        SET @ThisYear = DATEPART(YEAR, @TempDate)
        SET @Month11 = dbo.fn_AAA_MonthlyDollars_Product (@ThisJproID, @ThisMonth, @ThisYear)
    
        -- This month
        SET @Month12 = dbo.fn_AAA_MonthlyDollars_Product (@ThisJproID, @CurrMonth, @CurrYear)
    
        -- Process previous year-to-date
        SET @PrevYtd = dbo.fn_AAA_PrevYtdDollars_Product (@ThisJproID, @End, @CurrYear)
        -- Process year-to-date
        SET @Ytd = dbo.fn_AAA_YtdDollars_Product (@ThisJproID, @End, @CurrYear)
        -- Process year-to-date change
        SET @YtdChange = dbo.fn_AAA_FormatYtdChange (@Ytd , @PrevYtd)
    
        -- TODO: Expand the INSERT to include the months
        INSERT INTO #Table4Report    (Jpro_ID, Jpro_Code, Row_Type, Item_Type, Jnad_ID, Jmed_Id, Curr_12_Months, 
                        Month_1, Month_2, Month_3, Month_4, Month_5, Month_6, 
                        Month_7, Month_8, Month_9, Month_10, Month_11, Month_12, 
                        Previous_Ytd, Yr_to_Date, Ytd_Change)
        VALUES                (@ThisJproID, @ThisJproCode, 1, 3, 0, 0, @Curr12, 
                        @Month1, @Month2, @Month3, @Month4, @Month5, @Month6,
                        @Month7, @Month8, @Month9, @Month10, @Month11, @Month12,
                        @PrevYtd, @Ytd, @YtdChange)
        SET @ErrNum = @@ERROR
        IF @ErrNum <> 0 GOTO ERROR_TRAP
    
    
    
    
        -- TODO: Nest cursor for Product Ads
            -- TODO: Nest cursor for Product Ad Books
    
        FETCH NEXT FROM curAAAProducts INTO @ThisJproID, @ThisJproCode
    END
    
    CLOSE curAAAProducts
    DEALLOCATE curAAAProducts
    
    COMMIT TRAN T1
    
    SELECT * FROM #Table4Report ORDER BY Jpro_Code, Row_Type, Item_Type, Jnad_ID, Jmed_Id
    
    DROP TABLE #Table4Report
    
    RETURN 0
    
    ERROR_TRAP:
        ROLLBACK TRAN T1
        DEALLOCATE curAAAProducts
        DROP TABLE #Table4Report
        SELECT @ErrMsg = [description] FROM master.dbo.sysmessages WHERE [error] = @ErrNum
        RETURN -1
    
    END
    GO
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do NOT use permanent tables in place of temporary tables if you expect your application to ever be multi-user. I doubt that you will find the permanent tables to be any faster for this particular purpose anyway.

    Any performance issues that you are experiencing are coming from your unnecessary use of cursor-based processing instead of more efficient set-based processing. If you want better performance, rewrite it from the ground up.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    You mean it is better (faster) to write one massive SQL statement with nested subqueries such as:

    Code:
    SELECT        JND_COST_FACT_TB.JPRO_ID, 
                JND_PRODUCT_DIM_TB.JPRO_PRODUCT_CDE
        FROM             JND_COST_FACT_TB 
                INNER JOIN JND_PRODUCT_DIM_TB ON JND_COST_FACT_TB.JPRO_ID = JND_PRODUCT_DIM_TB.JPRO_ID 
                INNER JOIN JND_AAA_GROUP_DIM_TB ON JND_COST_FACT_TB.JGRP_ID = JND_AAA_GROUP_DIM_TB.JGRP_ID 
                INNER JOIN JND_CLASS_DIM_TB ON JND_COST_FACT_TB.JCLA_ID = JND_CLASS_DIM_TB.JCLA_ID
        WHERE         JND_COST_FACT_TB.JTME_ISSUE_DTE BETWEEN CONVERT(DATETIME, @Start, 102) AND CONVERT(DATETIME, @End, 102) 
                AND (JND_CLASS_DIM_TB.JCLA_CLASS_CODE_TYP = '0')
        GROUP BY     JND_COST_FACT_TB.JPRO_ID, JND_PRODUCT_DIM_TB.JPRO_PRODUCT_CDE
        HAVING          COUNT(JND_COST_FACT_TB.JPRO_ID) > 0
     ORDER BY     JND_PRODUCT_DIM_TB.JPRO_PRODUCT_CDE
    ... but including SUM()s and COUNT()s and such? There will be about 40 subquerie then wrapped in the overall SELECT statement. That sounds like a maintenance nightmare. Is that what you mean, however? And doing that, will I thank you in the morning
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, use a select with nested sub queries (or find ways to "distill" them out using expression), and yes, it will probably be between 50 and 1000 times faster than using a cursor to achieve the same result.

    -PatP

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, and from my brief review of your algorithm, you may not need as many subqueries as you think.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    Well, I rewrote that mess into some long but fast SQL statements. So far, I have about 2/3 of it done, and it runs in only 2 minutes, as opposed to the 2hrs 27 minutes the old way
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So what's to frown about with that? I've never had a problem telling the boss: "Well, I re-wrote it, and now it runs 100+ times faster". I'm thinking that you've scored a big "win" as far as I can see!

    -PatP

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by HardCode
    ...And doing that, will I thank you in the morning
    Don't bother. I'll probably leave before you wake up. But hey, you were great, kid.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Mar 2006
    Location
    Boston, MA
    Posts
    3
    Nested sub-queries are definitely more efficient.

    However, if you do decide to go the temporary table route in the future, I'd suggest considering the temp table variable available in SQL Server (and Oracle).

    Much more efficient for a number of reasons. See http://support.microsoft.com/default.aspx/kb/305977

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •