I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server: The proper and fastest way to check if rows matching a condition exist

You wouldn't believe how many times i've seen this code

IF (SELECT COUNT(*) FROM Table1 WHERE ... ) > 0

 

It's understandable though. Logically it's the easiest way to write "if there are any rows matching my condition do this".

But it's also wrong. Plainly and simplly wrong!

Why?

Because when you do a count(*) there is no way to get around an index range scan or a full table scan. For a large resultset this will be a huge resource hog.

So how do i do this you might ask yourself?

Very simple:  Use EXISTS!

IF EXISTS(SELECT * FROM Table1 WHERE ...)

 

Exists stops the execution as soon as it reads the first row, compared to Count which goes through the whole resultset matching our condition. 

 

This may seem as such a trivial tip but EXISTS is so underused even by experienced database developers.

And don't forget the NOT EXISTS to check if rows matching the condition don't exist.

 

SQL Server Product team recognised this problem so in SQL Server 2005 those 2 statements produce same execution plans whenever possible.

However relying on some internal "maybe when it suits me" operation is not good practice in my book.

 

kick it on DotNetKicks.com

Print | posted on Thursday, September 13, 2007 5:58 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server: The proper and fastest way to check if rows matching a condition exist

>Exists stops the execution as soon as it reads the first row...

Not quite. It stops as soon as it finds the first row _that matches_. So you could potentially still be reading the whole table, if there are no matching rows. But it will never be worse than select count().
9/13/2007 7:22 PM | Dewayne Christensen
Gravatar

# re: SQL Server: The proper and fastest way to check if rows matching a condition exist

@Dewayne:
i was reffering to the resultset, but i see how this could be misinterpreted now. thanx for pointing it out.
9/13/2007 8:04 PM | Mladen
Gravatar

# re: SQL Server: The proper and fastest way to check if rows matching a condition exist

This may not be the case anymore, but didn't it used to be that using SELECT 1 FROM instead of SELECT * FROM produced faster results? That way it didn't have to get any data back.
9/13/2007 10:46 PM | Mick
Gravatar

# re: SQL Server: The proper and fastest way to check if rows matching a condition exist

Things have changed after SQL 2005.

http://milambda.blogspot.com/2006/10/exists-or-not-exists-that-is-question.html

And the stuff about "select 1" vs. "select *" vs. "select <whatever>" is just a myth... :)
9/14/2007 9:18 AM | ML
Gravatar

# re: SQL Server: The proper and fastest way to check if rows matching a condition exist

@Mick:
i think that select 1 vs select * was true in sql server 6.5 but got fixed after 7.0 version.

@ML:
thanx for the link!
9/14/2007 10:22 AM | Mladen
Gravatar

# re: SQL Server: The proper and fastest way to check if rows matching a condition exist


Problem is, I've has "NOT EXISTS" return a wrong answer on several occasions, so you can understand that I'd be leary of using it. FYI, this was on SQL Server 2000, running scripts in Query Analyzer.

Steve G.
9/28/2007 1:01 AM | Steve G.
Gravatar

# re: SQL Server: The proper and fastest way to check if rows matching a condition exist

return wrong answer?
can you show an example, because i've never seen that.
9/28/2007 10:47 AM | Mladen
Gravatar

# re: SQL Server: The proper and fastest way to check if rows matching a condition exist

You know I wrote an article on the same topic. :)
http://www.sharpdeveloper.net/content/archive/2007/08/12/if-exists-instead-of-count-equals-increased-performance.aspx

Cheers!
10/19/2007 10:39 PM | Sameer Alibhai
Gravatar

# re: SQL Server: The proper and fastest way to check if rows matching a condition exist

Does anyone know if EXISTS check within a Stored Proc on a Temporary Table can force a run time SP: Recompile event?

Currently encoutering performance issues related to run-time procedure compilation and common factor seems to be EXISTS statement on accessing temporary table. FYI Temporary table is created and populated within the same Stored Proc prior to call to existence check.

Any Ideas?
1/30/2008 12:55 PM | Alan H
Gravatar

# re: SQL Server: The proper and fastest way to check if rows matching a condition exist

I got a good hint. I found this post very useful.
Thanks guys
8/5/2008 1:42 PM | Kumaresh
Gravatar

# re: SQL Server: The proper and fastest way to check if rows matching a condition exist

So why do I frequently find (in SQL 2000, 2005, or 2008) that
SELECT <column list> FROM <table1> INNER JOIN <table2> WHERE <condition>
IF @@ROWCOUNT > 0
<Do Something>

is orders of magnitude faster than

IF EXISTS (SELECT * FROM <table1> INNER JOIN <table2> WHERE <condition>)
<Do Something>

Both return the same execution plan, but CPU, reads, and writes may be 600 - 3000X greater for the IF EXISTS.
12/10/2009 6:47 PM | Baffled
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET