SQL Server Performance

Difference betwwen the execution od these Queries

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by dashbutu, Oct 27, 2006.

  1. dashbutu New Member

    hi,

    I have "order" and "busunit" tables

    Order table has 417135 rows.

    Case 1:

    select * from ord
    where idBusUnit in (select idBusUnit from busunit where idBusUnit < 500)

    It is taking 46 seconds to get the results


    Case 2:

    select * from ord
    inner join busunit
    on
    ord.idBusUnit = busunit.idBusUnit
    and busunit.idBusUnit < 500

    It is taking 1 min 23 seconds to get the results


    Case 3:

    select * from ord
    inner join busunit
    on
    ord.idBusUnit = busunit.idBusUnit
    Where busunit.idBusUnit < 500

    It is taking 1 min 46 seconds to get the results

    Case 4:

    declare @tbl table (idBusUnit int)

    insert into @tbl
    select idBusUnit from busunit where idBusUnit < 500

    select * from ord
    inner join @tbl tbl
    on
    ord.idBusUnit = tbl.idBusUnit

    It is taking 1 min 46 seconds to get the results
    Next time I run the same query it is taking 1.09 sec


    Please tell me in each case steps how we are getting the results.
    And if I am running it for the second time, then why the time taken is less than
    the first one.

    Thanks & Regards,
    butu

  2. FrankKalis Moderator

    You can observe how you get the results by hitting Ctrl+K and examine the execution plan.
    As for the second time: The magic words here are compilation and caching. You will almost always observe that subsequent calls to the same query are faster in returning the resultset.

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

Share This Page