April 2, 2007 at 9:19 am
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
April 2, 2007 at 1:56 pm
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
April 2, 2007 at 7:15 pm
If you want better performance, use derived tables instead of WHERE IN or WHERE EXISTS.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 3, 2007 at 7:27 am
Can you give me some samples, how can i use for these above queries ?
Thanks
AKP
April 3, 2007 at 8:41 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy