×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

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

CODE --> T-SQL

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

CODE --> T-SQL

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?

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

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...

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

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

Quote:

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

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

(OP)
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.

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

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.

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close