Question on IF EXISTS()

Question

Post reply

Question on IF EXISTS()

kingscriber

Say Hey Kid

Points: 705
More actions
February 15, 2010 at 2:44 pm

#145284

Running some STAT IO and Profiler on this...
If I STAT IO this line
IF EXISTS (SELECT TOP 1 (FileId) FROM FilesView WHERE Element IS NOT NULL)
I will get about 14K logical disk reads which is bad for this view
However, If I do this instead:
declare @FileId as uniqueidentifier;
SELECT TOP 1 @FileId = FileId FROM FilesView WHERE Element IS NOT NULL;
IF @FileId IS NOT NULL
I get about 100.
If there anything here besides looking at an exec plan that you can think of that would cause this?
[font="Tahoma"]forgive them, for they do not know what they do.[/font]

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 1

What happens if you reverse the order between the two methods?

I'm thinking that cache helped the second one... a lot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323546 More actions · Answer 2

I was recently "schooled"(ie chastised..hehe) here on the differences of IF EXISTS.

IF EXISTS (SELECT 1 FROM FilesView WHERE Element IS NOT NULL)

or

IF EXISTS (SELECT * FROM FilesView WHERE Element IS NOT NULL)

will perform exactly the same, and much faster that selecting a specific field.

by selecting a specific field, the query tests where non-null FilesView values; to do that, it does a scan; since you are using EXISTS, you most likely do not need to know the top 1 non null value, so you can change it to the other format for a performance enhancement.

that way, EXISTS will bail on the first matching condition, and perform quite a bit better.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kingscriber Say Hey Kid Points: 705 More actions · Answer 3

Jeff Moden (2/15/2010)
What happens if you reverse the order between the two methods?
I'm thinking that cache helped the second one... a lot.

Good thinking, but I am afraid I get the same results. Even with SQL Profiler.

---Lowell---

"so you can change it to the other format for a performance enhancement"

I did so. I changed it to both lines. I am still getting 14K + logical read errors. It would seem that Exists needs to perform full table scans within that view? Maybe the optimizer doesn't work for functions like this?

[font="Tahoma"]forgive them, for they do not know what they do.[/font]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

kingscriber (2/16/2010)
It would seem that Exists needs to perform full table scans within that view?

No it doesn't

Maybe the optimizer doesn't work for functions like this?

Sure it does.

The execution plan should be the very first thing that you look at in cases like this, not the last.

Please post table definitions, view definition, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323546 More actions · Answer 5

kingscriber with exists(), it should bail out on the first match it finds; seems like it ends up scanning 14K items until it hits a match, so I'd think that adding an index on the Element Column would help speed up the scan;

can you show us the current indexes on the table FilesView , maybe adding or including the column Element would help.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kingscriber Say Hey Kid Points: 705 More actions · Answer 6

GilaMonster (2/16/2010)
kingscriber (2/16/2010)
It would seem that Exists needs to perform full table scans within that view?
No it doesn't
Maybe the optimizer doesn't work for functions like this?
Sure it does.
The execution plan should be the very first thing that you look at in cases like this, not the last.
Please post table definitions, view definition, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

No problem. I numbered everything in order. Believe it or not, the execution plan that is NoExists.sqlplan that is associated with the query that doesn't use the EXISTS() has a considerably better duration, and the logical reads is considerably less. In this case I am just concerned about the 14K difference in reads between the two queries.

[font="Tahoma"]forgive them, for they do not know what they do.[/font]