Performance of COUNT vs EXISTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Performance of COUNT vs EXISTS

Author

Topic

Marioi
Posting Yak Master

132 Posts

Posted - 2007-05-02 : 16:52:22

Which performs better? Are there differences between SS2000 and 2005? My informal benchmarking didn't reveal a significant difference.

IF (SELECT Count(*) FROM table WHERE ... ) > 0
BEGIN
...
END

vs.

IF exists (SELECT * FROM table WHERE ... )
BEGIN
...
END

In the case of exists, does it matter whether SELECT is on * or just one field?

TIA.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-02 : 16:55:43

How many records where you testing against?

A count scans complete table (with or without an index).
An exists quits after first records found.

Peter Larsson
Helsingborg, Sweden

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-02 : 16:55:43

I think the EXISTS would be faster. And it doesnt matter if its a SELECT * or SELECT 1 or SELECT <column>. SQL Server looks for the first record that matches the condition and exits out as soon as it finds one. They would pobably be equal if the condition doesnt match. The first logic has to count ALL the rows that match the condition before it comes back to the IF part. In the EXISTS, as soon as it hits the first record with the condition it exits the loop.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-02 : 17:09:30

for your case in sql server 2k5 both are equal performance wise.
that is because the optimizer now simply short-circuits some statements and this is one of them.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Marioi
Posting Yak Master

132 Posts

Posted - 2007-05-02 : 22:56:39

Thank you all. The table currently has 75000 rows. I probably don't see the difference because of the RAM caching of that table on the server. I will use exists since the DB is ss2000.

Kristen
Test

22859 Posts

Posted - 2007-05-03 : 06:25:04

"And it doesnt matter if its a SELECT * or SELECT 1 or SELECT <column>. SQL Server looks for the first record that matches the condition and exits out as soon as it finds one"

Isn't "SELECT <column>" likely to induce / force a specific index to be used, which may or may not be the best?

(I haven't checked query plans, of course, but someone with time to kill could!)

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 06:27:19

this was true in the 6.5 version.
but since 7 it isn't so anymore.
the optimizer automaticaly uses the narrowest index possible

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-05-03 : 06:37:01

Hmmmm .... using "... EXISTS (SELECT 1 ..." would then allow me to have a meaningful search of the source code for any accidental usage of "SELECT *" ...

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 06:38:38

you tell me