-
EXISTS() vs. SELECT COUNT(*) vs. EXISTS (SELECT *)
I was reading about optimizing EXISTS today and decided to try a couple of variations on this theme. In this example, all I want to know is does the ID exist in the table. I presumed the following would all work exactly the same:
Select count(*) From Jurisdiction WITH (NOLOCK) Where JID= 22
IF EXISTS(Select count(*) From Jurisdiction WITH (NOLOCK) Where JID= 22)
print 'exists 1'
IF EXISTS(Select * From Jurisdiction WITH (NOLOCK) Where JID= 22)
print 'exists 2'
IF EXISTS(Select 1 From Jurisdiction WITH (NOLOCK) Where JID= 22)
print 'exists 3'
To test, I turned on 'Include Actual Execution Plan' and ran.
To my surprise, the first, third and fourth queries each took 33% of the total but the second took zero. They all return the correct result - the first query returns '1', the rest print 'exists x'.
In the execution plan for the first, third and fourth queries, 100% of the time taken for each query is attributed to a clustered index seek. The second query only consists of a Constant scan.
Can you explain this? Is it using something in memory that it's not showing?
-
I have tried this using a different table in a different database on a different server just for giggles. This table has no indexes. Exactly the same result except I get a Table scans instead of clustered index seeks.
-
If nobody can explain what I'm seeing here, can someone try it and let me know if they see the same result.
-
I think
IF EXISTS(Select count(*) From Jurisdiction WITH (NOLOCK) Where JID= 22)
does not need to query anything from the table because even if count(*) is 0 it always returns a resultset, exists returns true. That is true for any kind of grouping operator like max, min, sum.
So it is always true.
Running these on master database
Select * From sysobjects WITH (NOLOCK) Where ID= 22
Select count(*) From sysobjects WITH (NOLOCK) Where ID= 22
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----- ------ ------ ----------- --------------- ----------- ----------- ----------------------- ------- ----------- ---------------- ---- -------- ------- -------- ----------------------- ----------- ----------- ----------- ----------- ----------- ----------- ------
(0 row(s) affected)
-----------
0
(1 row(s) affected)
Second query returned a row with 0 as count, thus exists is always true, it is not looking for count(*) value but rows returned value.
-
In other words, the second query is not logically same as other queries.
-
Thanks for that. Obvious really, wasn't it. (Not that I thought of it!)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|