Post reply

regarding performance

  • July 24, 2008 at 11:41 pm

    #123370

    Hi,

    I would like to know about EXISTS and NOT EXISTS operator.

    And why it is used in query performance and what is real benefit of exist and not exist operator?

    Thanks

    Sunil

  • July 25, 2008 at 12:12 am

    #848222

    Check this link+

    http://www.techonthenet.com/sql/exists.php

    shree

  • July 25, 2008 at 12:21 am

    #848226

    The site you have given is not opening. So

    So

    Please give me solution.

  • July 25, 2008 at 12:45 am

    #848232

    You can read all about exists in BOL, but as you are asking about in the context of performance I suspect you are referring to how it compares with an IN operator, forgive me if I have misunderstood the question!

    EG

    Select ... from t1 where t1.c1 in (select c1 from t2)

    OR

    Select ... from t1 where exists (select 1 from t2 where t2.c2 = t1.c1)

    Will give you the same results, but you MAY find the second option is quicker, it all depends on how many rows are in both tables, what indexes etc are available.

    Conceptually the first query needs to build a complete list of values from t2 for the subquery, whereas the second executes the subquery once for each row in tybe outer query, but can stop each execution as soon as a single row is found.

    Best option is to look at the execution plans for both - you can learn a lot about how it all works by understanding the plans.

    Mike John

  • July 25, 2008 at 1:15 am

    #848241

    Thanks John.

  • July 25, 2008 at 4:15 am

    #848277

    su_kumar11 (7/25/2008)

    The site you have given is not opening. So

    So

    Please give me solution.

    Try copying the URL and pasting it to the address window in your browser.

    --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)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 5 (of 5 total)

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