Is "IF EXISTS()..." better than "IF (SELECT COUNT...) > 0"

[grnzbra]

I want to know if "Paris" exists in a table of cities. I've read that

IF EXISTS(SELECT * FROM tblCities WHERE fldCityName = 'Paris') PRINT 'Paris Exists'

is better than

IF(SELECT COUNT(*) FROM tblCities WHERE fldCityName = 'Paris') > 0 PRINT 'Paris Exists

because in the first case, as soon as it hits the record containing 'Paris' it stops running the query and displays the result while in the second it will read every record in the table before displaying the result.

However, when I click on "Include actual execution plan" before running these two SELECT statements, I get what seems to be the exact same result either way. In particular, the "Clustered Index Scan icon, which shows 98% indicates that the same number of rows have been read in both cases.
Is one really better than the other?

 

[SaltyTheFrog]

You should use IF EXISTS instead of SELECT COUNT(*) when checking only for the existence of any matching data values and when determining the number of matching rows is not required. IF EXISTS stops the processing of the select query as soon as the first matching row is found, whereas SELECT COUNT(*) continues searching until all matches are found, wasting I/O and CPU cycles. For example, you could replace

https://www.oreilly.com/library/view/microsoft-sql-server/9780133408539/ch45lev2sec6.html

That doesn't explain the analyzer but probably in HUGE tables it might.

 

[Mike Lewis]

in the second it will read every record in the table before displaying the result.

Not necessarily. If you have an index on fldCityName, it only needs to count the Paris index entries; it doesn't need to read any records from the table.

But I admit that that doesn't explain what you are seeing in the execution plan.

Just out of interest, how big is your tblCities?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[grnzbra]

The table contains 620 records. But I was only using that as a convenient table for a simple SELECT statement for the purpose of seeing what happens in the execution plan for the COUNT vs EXISTS comparison.

SaltyTheFrog: That's what I had read also (not the same place but said the same thing) - COUNT reads all the records while EXISTS stops when it hits the first match. This came about because I was asked to determine why one stored procedure took five times as long to run as another. I saw that the COUNT > 0 method was being used and mentioned that as a possible change, but was blown off. So I wanted to see the relative times necessary (and the actual count of rows read) was. The execution plan indicated 337 rows for each method. Of course, this is the first time I've played with execution plans so I may be totally misreading things.

 

[gmmastros]

If the execution plan is the same, then it really doesn't matter which one you use. Even with the same execution plan, the EXISTS version is probably faster, but only by a couple of clock cycles (too small to measure).

I would encourage you to always use the right tool for the job. Sometimes it won't matter, but sometimes it will. In your particular example, it made no difference. However, suppose you had a big ole fat table (lots' oc columns so that only 1 or 2 rows fit in an 8K page), and you have 100's of millions of rows, and you have a lot of matches. SQL Server would, in fact, look through the entire table to perform the count, whereas exists would stop almost immediately.

The execution plan makes sense because SQL server would need to scan all the rows (imagine the only matching row is the last one in the table). In this case, a scan makes sense. If there was an index on fldCityName, you would see an index seek instead of a scan. With the proper index, the seek would be many times faster than the scan, and you would notice a drastic improvement in performance.

Truth be told, I really love improving the performance of queries. There are a lot of tricks you can use. You mentioned that this was a stored procedure. As such, I would first make a copy of it, and then work with the copy. I usually start by adding "debug" messages, similar to this.

Create Procedure Whatever
AS 
Declare @Timer DateTime;

Set @Timer = GetDate();

-- 1st Query here

Select 'After 1st query', DateDiff(Millisecond, @Timer, GetDate());
Set @Timer = GetDate();

-- 2nd Query here

Select 'After 1st query', DateDiff(Millisecond, @Timer, GetDate());
Set @Timer = GetDate();

This way, you will know exactly what is slow. Sometimes execution plans can be misleading, but times like these are always easy to understand. It allows you to focus your attention on just a single query to optimize.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom