Solved

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

Posted on 2008-10-03
7
431 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

910 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now