Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

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

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
GordonPrince
Asked:
GordonPrince
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
exists will be better

select * from table1 where exists (select 1 from ... where  <> )
0
 
chapmandewCommented:
exists would be better in this case because using a top(1) indicates that an ORDER By may be occurring behind the scenes.
0
 
BrandonGalderisiCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
chapmandewCommented:
wow.
0
 
Brendt HessSenior DBACommented:
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
 
BrandonGalderisiCommented:
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
 
GordonPrinceAuthor Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now