Exists Vs Not Exists . Which one is Better?

Question

Post reply

Exists Vs Not Exists . Which one is Better?

Paarthasarathy

SSChasing Mays

Points: 649
More actions
June 27, 2007 at 4:42 am

#124513

Hai ,
I am a novice in MS SQL Querying , I have a very basic doubt .. pls help me.
Is using a " IF exists clause " in a select query or                                                   "If not exists" in a query boost performance?
else which one is a better one to use exists or not exists?
IF EXISTS ( SELECT 'X'
FROM table (NOLOCK)
WHERE <condition> )
BEGIN
         SELECT 'SUCCESS'
END
ELSE
BEGIN
          SELECT 'fAILURE'
END
or to use
IF not EXISTS ( SELECT 'X'
FROM table (NOLOCK)
WHERE <condition> )
BEGIN
        SELECT 'failure'
END
ELSE
BEGIN
         SELECT 'success'
END
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com

Viewing 15 posts - 1 through 15 (of 16 total)

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

I'd prefer the positive way.

So I'd prefer "Is the sun shining" above "Is the sun not shining"

Also keep in mind queries may deliver ambiguous results like

Q: "Is the sun not shining ?"

A: "Yes"

So the sun is not shining

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Joseph Manalang Valued Member Points: 62 More actions · Answer 2

I would say... it's the same performance output!

But if you want.. you can have the profiler run during execution of your query. This way you can see how fast or slow it executes... if you you small number of records, you won't noticed it.

Hope this helps

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

I would think that a search ends quicker when you find what you are looking for (EXISTS) rather than searching the entire table to declare that something is not there (NOT EXISTS). Not sure if this holds true in practice.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

I put them both into analyzer together and looked at the execution plan. They each had a cost of 50% of the whole.

Joseph Manalang Valued Member Points: 62 More actions · Answer 5

That settles it.

Thanks mrpolecat for carrying out the task...

David McFarland SSChampion Points: 11815 More actions · Answer 6

But isn't Exists sargable, while Not Exists isn't? If true, I'd think that Exists would at least situationally be better, while always at least equalling Not Exists performance. If that's the case, I'd probably lean towards using Exists.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 7

After my initial test I also ran some examples on both indexed and non indexed fields in a table with 1M+ rows. I saw no difference in the time to execute although none of my queries took more than 4 seconds to execute. It is funny that everyone with an opinion leans toward Exists yet each for a diffrent reason.

David McFarland SSChampion Points: 11815 More actions · Answer 8

I ran a couple of spot checks myself and come up with the same results as you. I still think Exists is sargable (seems like it would have to be), but I'll agree that it often doesn't seem to make a difference.

Sergiy SSC Guru Points: 110208 More actions · Answer 9

David, I see it's your rule - don't believe in facts if they contradict your believes.

Acceptable for a priest, not good for a scientist.

_____________
Code for TallyGenerator

mrpolecat SSCertifiable Points: 7014 More actions · Answer 10

I see no contradiction here. The tests we ran showed no difference and David agreed that is what the test shows.

David believes EXISTS is sargable and that NOT EXISTS is not sargable. This is still in question. If his belief is true then a deeper dive into the analysis with extensive tests using more intensive queries on indexed fields may show us that one method works better than another.

Sergiy SSC Guru Points: 110208 More actions · Answer 11

Heh,

There is no "NOT EXISTS".

There is "EXISTS" and "NOT" which reverse boolean result of "EXISTS".

What "not sargable" could appear in reversing boolean value?

_____________
Code for TallyGenerator

mrpolecat SSCertifiable Points: 7014 More actions · Answer 12

mrpolecat

SSCertifiable

Points: 7014

June 27, 2007 at 3:23 pm

#715644

Sounds reasonable to me. So is EXISTS sargable?

Sergiy SSC Guru Points: 110208 More actions · Answer 13

Does it matter?

The question was if EXISTS and NOT EXISTS are equivalent.

The answer does not depend on sargability of EXISTS.

_____________
Code for TallyGenerator

mrpolecat SSCertifiable Points: 7014 More actions · Answer 14

It does not matter for the original poster's question which has been answered. Since the question was raised I am curious to know the answer although in this case I don't think it would matter.