Are these two Same ?

• AKP

  SSCommitted

  Points: 1753

  More actions

  April 2, 2007 at 9:19 am

  #177549

  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

• Johan Bijnens

  SSC Guru

  Points: 135382

  More actions

  April 2, 2007 at 1:56 pm

  #698175

  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

• Jeff Moden

  SSC Guru

  Points: 1004441

  More actions

  April 2, 2007 at 7:15 pm

  #698200

  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)
  

• AKP

  SSCommitted

  Points: 1753

  More actions

  April 3, 2007 at 7:27 am

  #698274

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

  Thanks

  AKP

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  April 3, 2007 at 8:41 am

  #698306

  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"