Solved

using "top 1" or "exists" or other method performance ideas

Posted on 2008-10-03
7
432 Views
Last Modified: 2012-05-05
I have code that needs to know if any rows exist before deciding to do something. I'll make a stored procedure for the code.

My select statement would be
select * from table1 where col1=value

I only want to know if any rows exist that match the criteria following the where clause.

"select top 1 ..." would be an improvement, I think.
Would "exists" syntax be even better? What would the "exists" statement look like?

Thanks for any ideas.
0
Comment
Question by:GordonPrince
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 22634768
exists will be better

select * from table1 where exists (select 1 from ... where  <> )
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22634769
exists would be better in this case because using a top(1) indicates that an ORDER By may be occurring behind the scenes.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 125 total points
ID: 22634785
if exists (select null from Table1 where col1=value)
begin
print 'do something'
end

No need to do a top since the condition will be met if ONE record exists.  It won't return all records.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 60

Expert Comment

by:chapmandew
ID: 22634916
wow.
0
 
LVL 32

Expert Comment

by:bhess1
ID: 22634933
The above answers are all correct - the EXISTS syntax is almost always more efficient than any other method of checking.  I do note, however, different versions of the exists check using NULL and 1.  These are based on a condition that existed in SQL Server no later that version 7, and (I think) not since 6.x - that specifying a constant value in the EXISTS chech is faster than specifying some value from the actual data record.  This is not true, so the version:

IF EXISTS (
    SELECT *
    FROM Table1
    WHERE Col1 = @Value
    )

is just as fast as the versions with a 1 or NULL in the select list, and looks more natural to the potentially inexperienced coder in the future who may need to maintain the code.  I recommend using it.  
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22635105
I don't * or 1 for any reason other than I want to make it clear to "inexperienced coders" that it is not actually selecting any data.

My opinion is that an inexperienced coder probably shouldn't be attempting to decypher anything I have written regardless :).
0
 
LVL 4

Author Comment

by:GordonPrince
ID: 22635269
I think all coders owe it to the other coders to make their code as easy to understand as possible, assuming no performance problems. So I think I'm with BHESS1 above on that.

Also, if "select *" is version independent, that's a plus.

Whenever there's a choice, write the code so you or someone else will have the maximum chance of quickly understanding what's going on and how the code could be fixed or modified or enhanced. In my opinion.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question