SQL Server Performance

Left Join vs. not in SubQuery

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by EMoscosoCam, Apr 27, 2007.

  1. EMoscosoCam Member

    Hello

    I have to tables which have the same structure Table1 and Table2 (#Product_ID varchar(10), #Num_Year char(4), #Num_Month char(2), Qty_Forecast numeric). I would like to insert into Table1 all the rows from Table2 that it does not already has. Which of the following statements is better and why?

    First statement:


    insert into Table1
    select
    Producto_ID,
    Num_Year,
    Num_Month,
    Qty_Forecast
    from Table2
    where
    Product_ID + Num_Year + Num_Month not in
    (
    select
    Product_ID + Num_Year + Num_Month
    from Table1
    )

    Second statement:




    insert into Table1
    select
    Producto_ID,
    Num_Year,
    Num_Month,
    Qty_Forecast
    from Table2
    left join Table1 on
    Table1.Product_ID=Table2.Product_ID and
    Table1.Num_Year=Table2.Num_Year and
    Table1.Num_Month=Table2.Num_Month
    where
    Table1.Product_ID is null and
    Table1.Num_Year is null and
    Table1.Num_Month is null


    Thanks a lot for you help.
  2. merrillaldrich New Member

    I suggest running both statements with Show Execution Plan, compare what the server is doing and the query plan cost. My strong hunch is that the second one is better, because it can use indexes where the first cannot.
  3. FrankKalis Moderator

    I would second this. RDBMS will mostly try to flatten queries into JOIN operations anyway. So if you can express express a query with in a JOIN way, it is (in most cases) the "better" choice.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  4. bhushank21 New Member

    A small change is required in your second statement.
    Use the Outer join for your query to succeed. The current query will insert zero records.

    insert into Table1
    selectProducto_ID, Num_Year, Num_Month, Qty_Forecast
    from Table2
    left Outer join Table1 on -- Changed...
    Table1.Product_ID=Table2.Product_ID and
    Table1.Num_Year=Table2.Num_Year and
    Table1.Num_Month=Table2.Num_Month
    where
    Table1.Product_ID is null and
    Table1.Num_Year is null and
    Table1.Num_Month is null

    I executed both queries in three iterations (ofcourse resetting to original state before next iteration) and found same performance results in terms of CPU, reads, durations.
    Any thoughts?

    Thanks and Regards,
    Bhushan
    http://www.metacafe.com/watch/365213/wonder_what_you_are
  5. Adriaan New Member

    Better late than never, but one thing you should never do is concatenating columns for criteria ...

    where Product_ID + Num_Year + Num_Month not in (select Product_ID + Num_Year + Num_Month from Table1)

    ... because this causes the server to ignore all appropriate indexes.

    The syntax you're looking for is a correlated subquery, which is pretty much the same subquery, but with fields from the main query in the WHERE clause of the subquery:

    insert into Table1
    select Product_ID, Num_Year, Num_Month, Qty_Forecast
    from Table2
    where Table2.Product_ID not in
    (select x.ProductID FROM table1 x WHERE x.Num_Year = Table2.Num_Year AND x.Num_Month = Table2.Num_Month)

    ... or a NOT EXISTS correlated subquery:

    where NOT EXISTS
    (select x.ProductID FROM table1 x WHERE x.ProductID = table2.ProductId x.Num_Year = Table2.Num_Year AND x.Num_Month = Table2.Num_Month)

    Performance can vary depending on data distribution, but the LEFT JOIN syntax is probably a safe bet.
  6. bhushank21 New Member

    Where do the stars under the User name come from?
    For e.g. Adriaan has 5 stars.

    Thanks and Regards,
    Bhushan
    http://www.metacafe.com/watch/365213/wonder_what_you_are
  7. FrankKalis Moderator

  8. Adriaan New Member

    Bhushan,

    I was not so much responding to your posting, as to the original poster. Sorry if I seemed to jump all over you.
  9. bhushank21 New Member

    No probs!

    Thanks and Regards,
    Bhushan
    http://www.metacafe.com/watch/365213/wonder_what_you_are

Share This Page