Solved

Posted on 2006-05-22

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' ))

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' ))

17 Comments

(LOL because I ask that question a lot too -- "is this homework?")

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

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' ))

so actually, I confirm your 'theory'.

the reason is that, the * needs to be expanded into the column list.

Hope this helps...

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.

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

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**15** Experts available now in Live!