Are these two Same ?

  • Hello everyone,

    Help me in this case. I know IN operator can replace with EXISTS in query and I also did that. The replacement displays same result as original and faster than original. Please give me some suggestions whether these two queries are same or not ?

    Original Query with IN :

    SELECT p_id, c_id, s_id, r_id, f_name, l_name, f_name, c_id, d_email, e_confirm, e_notify, s_page, r_type, offset FROM v_test WHERE

    ((p_id IN (SELECT app_by_id FROM wks WITH(NOLOCK) WHERE w_id='9E90211C56BE41878CD83EBF0AF16055'))

    OR (p_id IN (SELECT c_rp_id from v_wks WITH(NOLOCK) WHERE w_id='9E90211C56BE41878CD83EBF0AF16055')))

    New Query with EXISTS:

    SELECT p_id, c_id, s_id, r_id, f_name, l_name, f_name, c_id, d_email, e_confirm, e_notify, s_page, r_type, offset FROM v_test WHERE

    ((exists (select * from wks with(nolock) where v_test.p_id=wks.app_by_id

    and w_id='C9F120F665F442E2A9D48BC51493A7AF'))

    OR (exists(select * from v_wks with (nolock) where v_test.p_id=v_wks.c_rp_id and w_id='C9F120F665F442E2A9D48BC51493A7AF')))

    In above, V_test and V_wks are two views.

    If not, provide me the equivalent of IN using EXISTS. ASAP

    Thanks

    AKP

  • Your exists queries should do just fine !

    And chances are they would be performing better.

    Compare the execution plans ! and runtimes

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • If you want better performance, use derived tables instead of WHERE IN or WHERE EXISTS.

    --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)

  • Can you give me some samples, how can i use for these above queries ?

    Thanks

    AKP

  • SELECT

    v.p_id,

    v

    .c_id,

    v

    .s_id,

    v

    .r_id,

    v

    .f_name,

    v

    .l_name,

    v

    .f_name,

    v

    .c_id,

    v

    .d_email,

    v

    .e_confirm,

    v

    .e_notify,

    v

    .s_page,

    v

    .r_type,

    v

    .offset

    FROM

    v.v_test

    WHERE

    EXISTS (SELECT * FROM wks AS w1 WITH(NOLOCK) WHERE w1.w_id = '9E90211C56BE41878CD83EBF0AF16055' AND v.p_id = w1.app_by_id)

    OR EXISTS (SELECT * from v_wks AS w2 WITH(NOLOCK) WHERE w_id = '9E90211C56BE41878CD83EBF0AF16055' AND v.p_id = w2.c_rp_id)

    SELECT

    v.p_id,

    v

    .c_id,

    v

    .s_id,

    v

    .r_id,

    v

    .f_name,

    v

    .l_name,

    v

    .f_name,

    v

    .c_id,

    v

    .d_email,

    v

    .e_confirm,

    v

    .e_notify,

    v

    .s_page,

    v

    .r_type,

    v

    .offset

    FROM

    v_test AS v

    INNER

    JOIN (

    SELECT app_by_id AS ID

    FROM wks WITH(NOLOCK)

    WHERE w_id = '9E90211C56BE41878CD83EBF0AF16055'

    UNION

    SELECT c_rp_id

    from v_wks WITH(NOLOCK)

    WHERE w_id = '9E90211C56BE41878CD83EBF0AF16055'

    ) AS x ON x.ID = v.p_id


    N 56°04'39.16"
    E 12°55'05.25"

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

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