SQL Server Performance

Inner Join or Subquery

Discussion in 'T-SQL Performance Tuning for Developers' started by sqlinformation, Jan 17, 2006.

  1. sqlinformation New Member

    Hi guyz, please see if you can help me....

    I have the following query which joins 4 tables.

    SELECT A.julian_month, B.month_year, C.lienholder, C.product_credit_type


    FROM A INNER JOIN B ON A.Orig_Day_Key = B.Day_Key
    INNER JOIN C ON A.Requested_Product_Key = C.Product_Key
    INNER JOIN D ON A.Application_Key = D.Application_Key
    INNER JOIN E ON B.Julian_Month = E.Julian_Month

    WHERE D.valid_application_flag = 'Y'


    Now if I rewrite this query by using a subquery instead of a Join to the 'D' table, the query takes half the time to execute.

    SELECT A.julian_month, B.month_year, C.lienholder, C.product_credit_type AS Requested_Product_Credit_Type, C.Period


    FROM A INNER JOIN B ON A.Orig_Day_Key = B.Day_Key
    INNER JOIN C ON A.Requested_Product_Key = C.Product_Key
    INNER JOIN E ON B.Julian_Month = E.Julian_Month

    WHERE A.Application_Key In (Select Application_Key From D
    Where valid_application_flag = 'Y')


    Now my question is, is there any issue in using a subquery instead of an Inner Join here? Also is there any chance that it will affect the functionality in the future (because there is one more join after the 'D' table Join).

    Thanks in advance.....
  2. Luis Martin Moderator

    Welcome to the Forum.

    I don´t see any issue.

    Did you see both execution plan to check what indexes are used in one case and in other?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  3. sqlinformation New Member

    Thnx for the reply. In the execution plan, 'Clustered Index Scan' is used when Im using Join and 'Clustered Index Seek' is used when the subquery is used.

    Application Key is the clustered Index column on table 'D'.

    I am just concerned with this....

    If we see the sequence of execution of statements in the first query,

    SELECT A.julian_month, B.month_year, C.lienholder, C.product_credit_type

    FROM A INNER JOIN B ON A.Orig_Day_Key = B.Day_Key
    INNER JOIN C ON A.Requested_Product_Key = C.Product_Key
    INNER JOIN D ON A.Application_Key = D.Application_Key
    INNER JOIN E ON B.Julian_Month = E.Julian_Month

    WHERE D.valid_application_flag = 'Y'

    1. A is joined with B
    2. B is joined with C
    3. A is joined with D
    4. B is joined with E
    5. The resultset is Filtered with the 'Where' clause (WHERE D.valid_application_flag = 'Y').

    Now in the query, which I rewrote,

    SELECT A.julian_month, B.month_year, C.lienholder, C.product_credit_type AS Requested_Product_Credit_Type, C.Period

    FROM A INNER JOIN B ON A.Orig_Day_Key = B.Day_Key
    INNER JOIN C ON A.Requested_Product_Key = C.Product_Key
    INNER JOIN E ON B.Julian_Month = E.Julian_Month

    WHERE A.Application_Key In (Select Application_Key From D
    Where valid_application_flag = 'Y')

    the sequence will be like this...

    1. A is joined with B
    2. B is joined with C
    3. B is joined with E
    4. The resultset is then Filtered with the 'Where' clause (WHERE A.Application_Key In (Select Application_Key From D Where valid_application_flag = 'Y')).

    Im just worried, whether this will affect the functionality, because the sequence of operation is not the same in both the cases..... I am getting the same results in both cases though. Thanks....
  4. Twan New Member

    Hi ya,

    You can also try



    SELECT A.julian_month, B.month_year, C.lienholder, C.product_credit_type
    FROM A INNER JOIN B ON A.Orig_Day_Key = B.Day_Key
    INNER JOIN C ON A.Requested_Product_Key = C.Product_Key
    INNER JOIN D ON A.Application_Key = D.Application_Key
    AND D.valid_application_flag = 'Y'
    INNER JOIN E ON B.Julian_Month = E.Julian_Month


    I'd say that the reason for the change is that SQL is not filtering the D table at the time that it is being joined, instead opting to filter the entire resultset at the end

    Cheers
    Twan
  5. Adriaan New Member

    .. or rewrite with derived table, see what happens to execution plan.

    SELECT A.julian_month, B.month_year, C.lienholder, C.product_credit_type
    FROM A INNER JOIN B ON A.Orig_Day_Key = B.Day_Key
    INNER JOIN C ON A.Requested_Product_Key = C.Product_Key
    INNER JOIN (SELECT * FROM D WHERE D.valid_application_flag = 'Y') AS D ON A.Application_Key = D.Application_Key
    INNER JOIN E ON B.Julian_Month = E.Julian_Month

    All variations of T-SQL listed here will return the same rows, don't worry about that!
  6. lamprey New Member

    The sub-querey may indeed be faster. However, since SQL read left to right you may want to try changing your join order putting the most restrictive clauses/joins first. For example:



    SELECT
    A.julian_month,
    B.month_year,
    C.lienholder,
    C.product_credit_type
    FROM
    D
    INNER JOIN
    A
    ON A.Application_Key = D.Application_Key
    AND D.valid_application_flag = 'Y'
    INNER JOIN
    B
    ON A.Orig_Day_Key = B.Day_Key
    INNER JOIN
    C
    ON A.Requested_Product_Key = C.Product_Key
    INNER JOIN
    E
    ON B.Julian_Month = E.Julian_Month

  7. mmarovic Active Member

    The order joins are written doesn't guarantee the join order unless "option (force order)" clause is used.

Share This Page