temporary tablesvs derived table

  • The following query uses a temporary table.

    Northwind.dbo.Categories = 8 rows,

    Northwind.dbo.Products = 77 rows

    SELECT GETDATE() as Time

    GO

    CREATE TABLE #Temp_Example (

    [CategoryID] INT NOT NULL,

    [Category_Count] INT NOT NULL

    )

    INSERT INTO #Temp_Example (CategoryID, Category_Count)

    SELECT C.CategoryID, COUNT(*) AS Category_Count

    FROM Categories C

    INNER JOIN Products P ON C.CategoryID = P.CategoryID

    GROUP BY C.CategoryID, C.CATEGORYNAME

    SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice, #Temp_Example.Category_Count

    FROM Categories C

    INNER JOIN Products P ON C.CategoryID = P.CategoryID

    INNER JOIN #Temp_Example ON C.CategoryID = #Temp_Example.CategoryID

    ORDER BY C.CategoryName

    DROP TABLE #Temp_Example

    GO

    SELECT GETDATE() as Time

    --Duration = 80-110

    ==================

    The following query uses a derived table.

    Northwind.dbo.Categories = 8 rows,

    Northwind.dbo.Products = 77 rows

    SELECT GETDATE() as Time

    GO

    SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice, CT.Category_Count

    FROM Categories C

    INNER JOIN Products P ON C.CategoryID = P.CategoryID

    INNER JOIN (

    SELECT C.CategoryID, COUNT(*) AS Category_Count

    FROM Categories C

    INNER JOIN Products P ON C.CategoryID = P.CategoryID

    GROUP BY C.CategoryID, C.CategoryName

    )CT ON C.CategoryID = CT.CategoryID

    ORDER BY C.CategoryName

    GO

    SELECT GETDATE() as Time

    --Duration = 50-80

    ***

    By using derived tables instead of temporary tables, you reduce disk I/O thereby improving the application's performance

    Question: Any suggestions ?

    Regards

    Kevin

    sonyt65@yahoo.com

  • 1. I see no difference in the time when measured properly...

    --SELECT GETDATE() as Time

    --GO

    SET STATISTICS TIME ON

    CREATE TABLE #Temp_Example (

    [CategoryID] INT NOT NULL,

    [Category_Count] INT NOT NULL

    )

    INSERT INTO #Temp_Example (CategoryID, Category_Count)

    SELECT C.CategoryID, COUNT(*) AS Category_Count

    FROM Categories C

    INNER JOIN Products P ON C.CategoryID = P.CategoryID

    GROUP BY C.CategoryID, C.CATEGORYNAME

    SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice, #Temp_Example.Category_Count

    FROM Categories C

    INNER JOIN Products P ON C.CategoryID = P.CategoryID

    INNER JOIN #Temp_Example ON C.CategoryID = #Temp_Example.CategoryID

    ORDER BY C.CategoryName

    DROP TABLE #Temp_Example

    SET STATISTICS TIME OFF

    GO

    --SELECT GETDATE() as Time

    PRINT REPLICATE('=',100)

    -- --Duration = 80-110

    --

    -- ==================

    -- The following query uses a derived table.

    -- Northwind.dbo.Categories = 8 rows,

    -- Northwind.dbo.Products = 77 rows

    -- SELECT GETDATE() as Time

    -- GO

    SET STATISTICS TIME ON

    SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice, CT.Category_Count

    FROM Categories C

    INNER JOIN Products P ON C.CategoryID = P.CategoryID

    INNER JOIN (

    SELECT C.CategoryID, COUNT(*) AS Category_Count

    FROM Categories C

    INNER JOIN Products P ON C.CategoryID = P.CategoryID

    GROUP BY C.CategoryID, C.CategoryName

    )CT ON C.CategoryID = CT.CategoryID

    ORDER BY C.CategoryName

    SET STATISTICS TIME OFF

    -- GO

    -- SELECT GETDATE() as Time

    --Duration = 50-80

    2. There will probably be a little difference in time because you've included an INSERT in the first set. 2nd set only has one operation.

    3. The following statement you posted....

    By using derived tables instead of temporary tables, you reduce disk I/O thereby improving the application's performance

    ... is true ONLY if you intend to use the calculation saved in the Temp Table once.

    4. For complex joins with aggragates, it is sometimes MUCH cheaper to use your first method even if the results in the Temp table will only be used once. It just didn't turn out that way for your simple example. Divide and Conquer is a powerful and effective method to greatly enhance the performance of queries.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • As I said the first time you asked this question:

    What is the preferred option, temporary table or derived table.

    The general thinking is to prefer derived tables because they leave the optimizer with more freedom to optimize. However, experience also shows that the more complex the query, the less likely the optimizer is going to make the best choices. Consequently, for significantly complex queries, converting one or more derived tables to temporary tables serves both as a way to force certain decisions on the optimizer and as a way to simplify the query, thus allowing the optimizer to make better decisions with what remains. The catch here is that if you convert the wrong derived table to a temporary table, then you end up forcing a bad choice on the optimizer, thus making the overall performance worse instead of better.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply