• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Performance of "exists" queries

Query B is clearly faster than Query A:

A:   select * from mytable where x='y'

B:   select 1 from mytable where x='y'



But what if an "exists" clause is added -- is the performance still better using Query B ?  (In theory yes, but what about in real life?)

A:   if (not exists (select * from mytable where x='y' ))

B:   if (not exists (select 1 from mytable where x='y' ))




0
knightEknight
Asked:
knightEknight
  • 6
  • 5
  • 2
  • +3
5 Solutions
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Is this homework?
0
 
knightEknightAuthor Commented:
No.  We have a lot of stored procedures that use the A form and I want to change them to the B form, but I need to have my theory validated.

(LOL because I ask that question a lot too -- "is this homework?")
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Query A is clearly faster than Query B:
how did you determine that?

because actually, the B: (resp C:) should be faster
C:   select x from mytable where x='y'

please confirm that field x is indexed?


regarding the exists/not exists, the same applies
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
knightEknightAuthor Commented:
>> >Query A is clearly faster than Query B:

huh?

as previously noted, query B is faster.
0
 
knightEknightAuthor Commented:
ok, good point, now we have three scenarios:

A:   if (not exists (select  *  from mytable where x='y' ))

B:   if (not exists (select  1  from mytable where x='y' ))

C:   if (not exists (select  x  from mytable where x='y' ))

0
 
Scott PletcherSenior DBACommented:
I have found that format B does seem to perform better in EXISTS, so I use it exclusively now.  I think the "*" requires more prep time because SQL probably loads all the column info, even though it shouldn't technically need to.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Sorry, misread :-(
so actually, I confirm your 'theory'.
the reason is that, the * needs to be expanded into the column list.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<We have a lot of stored procedures that use the A form and I want to change them to the B form, but I need to have my theory validated.>>Ok then...;) It's not just theory...using a limited scope variables (select 1, select field1....) to be returned as opposed to an implicit unlimited scope saves resources .  Except that, angel is right better use select x from table1 where x='y' as it makes execution plans cleaner..

Hope this helps...
0
 
imran_fastCommented:
A and B are same because they return true or false only. so you can either use query A, B or C its the same.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Another way to look at it...//select '*'// type of statement is pure nonsense both on a performance AND on a relational standpoint...Please forget them...;)
0
 
knightEknightAuthor Commented:
>>  Except that, angel is right better use select x from table1 where x='y' as it makes execution plans cleaner..

ok, so using  select x  is faster than  select 1?    (assume x is indexed)

>> A and B are same because they return true or false only. so you can either use query A, B or C its the same.

the exists function returns true or false, but what does it do with the enclosed query?  If it executes it using rowcount=1 then the form of the query does indeed make a difference.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<ok, so using  select x  is faster than  select 1? >>  They are equivalent... select 1 is about 0.0000000000001% faster...better use C

<<so you can either use query A, B or C its the same>>No.  A is different from B and C...B and C are mainly Index Seek while A may involve additional operations AND overhead
0
 
Atlanta_MikeCommented:
Okay, what about:
 if (not exists (select  NULL  from mytable where x='y' ))
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<if (not exists (select  NULL  from mytable where x='y' ))>>same as B and C..
0
 
Scott PletcherSenior DBACommented:
Don't know, haven't tried that one :-) .  But it's somewhat odd to read, isn't it?

Maybe for clarity we should do this:

IF EXISTS ( SELECT 1 AS [FoundIt] FROM mytable WHERE x = 'y' )
0
 
knightEknightAuthor Commented:
Thanks everyone!  Time to dole out the points.
0
 
knightEknightAuthor Commented:
thanks for the homework help!  j/k  :p
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 6
  • 5
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now