SQL Server Performance

Temp table for repeatedly used subquery

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by tsashoka, Nov 3, 2007.

  1. tsashoka New Member

    Hi,

    Here is my query which lists all orders for products supplied by Supplier-3.
    A typical Query on the Northwind database i wrote is like this..

    Select * FROM [Order Details] WHERE ProductID in
    (Select ProductID From Products where SupplierID = 3)

    The subquery in Red was used in multiple places in one of my Stored Procedures..

    So what i thought was - use a temp table to store the resultset from this subquery, and then use the temp table instead of querying the Products table everywhere..

    My Query looked something like this..

    Declare @ProductIDs TABLE
    (ProductID int)

    INSERT INTO @ProductIDs
    Select ProductID From Products where SupplierID = 3

    Select * FROM [Order Details] WHERE ProductID in
    (Select ProductID FROM @ProductIDs)
    Well, I expected an increase in performance with the latter approach, but seems my Stored Procedure is taking more time with the second solution..
    Would be glad to see ne explanation on this behavior..
    Thanks in Advance..
  2. richierich New Member

    Why wouldn't you write your query like...
    select od.* from [order details] od inner join [products] pr on od.productid=pr.productid where supplierid=3
    This would be quicker than both solutions above.
    Rich.
  3. tsashoka New Member

    yeah.. u r right..
    But what i tried to do with that sample is i was using the same subquery in multiple sections of my procedure.. but with different main queries,,
    Originial Procedure:
    Select * FROM [Order Details] WHERE ProductID in
    (Select ProductID From Products where SupplierID = 3)
    Select od.customerID, od.orderdate FROM [Order Details] odet JOIN Orders od on odet.OrderID = od.OrderID WHERE ProductID in
    (Select ProductID From Products where SupplierID = 3)
    New Procedure:
    Declare @ProductIDs TABLE
    (ProductID int)

    INSERT INTO @ProductIDs
    Select ProductID From Products where SupplierID = 3

    Select * FROM [Order Details] WHERE ProductID in
    (Select ProductID FROM @ProductIDs)
    Select od.customerID, od.orderdate FROM [Order Details] odet JOIN Orders od on odet.OrderID = od.OrderID WHERE ProductID in
    (Select ProductID FROM @ProductIDs)
    Although my procedure was not with Northwind, i tried to depict the same using queries on Northwind database.. The actual queries are much complex, but i have a similar concept of using a temp table for common subquery..
  4. satya Moderator

    What have you observed with Execution Plan for both the queries?

Share This Page