Solved

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

Posted on 2008-10-03
7
435 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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