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:

SELECT * FROM TABLE WHERE CONDITION

OR

SELECT TOP 1 * FROM TABLE WHERE CONDITION

OR

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

[Shashi Sadasivan]

there is one link regarding TOPefficiency of top vs rowcount

[Shashi Sadasivan]

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

[ck9663]

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

[Afterlife]

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.

[VBPhilly]

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.