469,138 Members | 1,257 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,138 developers. It's quick & easy.

SQL WHERE EXISTS vs. SELECT *

VBPhilly
I need to quickly determine if records exist given a specific field value.
Record counts can be as high 500,000 rows or more.
I only need to see if at least one record exists.

Which is faster:

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM TABLE WHERE CONDITION
OR

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 * FROM TABLE WHERE CONDITION
OR

Expand|Select|Wrap|Line Numbers
  1. IF EXISTS(SELECT * FROM TABLE WHERE CONDITION) 
Testing each of the versions has been unrealiable due to the amount of records fluctuating. I really need an idea of which is more performance friendly.
Thats all.
Thanks in advance
Sep 6 '07 #1
5 4406
Shashi Sadasivan
1,435 Expert 1GB
there is one link regarding TOPefficiency of top vs rowcount
Sep 7 '07 #2
Shashi Sadasivan
1,435 Expert 1GB
The where condition might seem to be more efficient than an exists.
Reason being than the exists has a subquery + comparisn
where statement is only a comparisn.

unless the subquery of the exists query uses Top (that could be close to a where statement)

Not completely sure about it, but if it gives you any ideas

cheers
Sep 7 '07 #3
ck9663
2,878 Expert 2GB
The where condition might seem to be more efficient than an exists.
Reason being than the exists has a subquery + comparisn
where statement is only a comparisn.

unless the subquery of the exists query uses Top (that could be close to a where statement)

Not completely sure about it, but if it gives you any ideas

cheers

grab a couple of thousand of test data and do a benchmarking....

also, try:
select tbl1.myid, tbl1.otherfield,
exist = case when tbl2.myid is null then 'false' else 'true' end
from tbl1 inner join tbl2 on tbl1.myid = tbl2.myid


make sure you have index on myid field on both table
Sep 7 '07 #4
WHERE EXISTS is about as fast as SELECT TOP 1 ... because it exits the check when the 1st hit is reached. WHERE field IN (SELECT ...) is a lot costlier though.
Sep 7 '07 #5
WHERE EXISTS is about as fast as SELECT TOP 1 ... because it exits the check when the 1st hit is reached. WHERE field IN (SELECT ...) is a lot costlier though.
Thanks all for your help. I ended up using TOP 1 *. It seems to be quicker.
for now, anyway. Thanks again.
Sep 14 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Geetha | last post: by
8 posts views Thread by Trev | last post: by
4 posts views Thread by Robin Lawrie | last post: by
4 posts views Thread by Darrel | last post: by
1 post views Thread by Jonathan Woods | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.