Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My colleague suggests that Query 1 should run faster than Query 2. If it's true, please anyone explain me the reason. Thanks.

Query (1):

IF ( SELECT COUNT(UnitTrustCounterId)
     FROM   UnitTrustCounter
     WHERE  PeriodId = 0
   ) > 0 
    SELECT  1
ELSE 
    SELECT  0  

Query (2):

IF EXISTS ( SELECT  *
            FROM    UnitTrustCounter
            WHERE   PeriodId = 0 ) 
    SELECT  1
ELSE 
    SELECT  0   
share|improve this question

migrated from stackoverflow.com Dec 11 '12 at 10:01

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 3 down vote accepted

Well I would like to comment on further aspect of using if-else with exists.

It's best to avoid using if-else with exists. Why?

  1. You need to make sure at least one record exists in your table
  2. This method does not allow for concurrency (specially in insert, updates transactions, it could end up creating deadlock)

Instead you may use WHERE EXISTS

Reference materials that explains well: avoid if else with exists in sql

And to prove your colleagues wrong ;) check this article out too. I am not going to re-write Author's data here. But the tag line is worth re-writing again and again.

" The other benefit of EXISTS is that once it finds a single record that matches it stops processing. This doesn't have a huge impact if you're checking on a primary key. It does have a big impact if you're checking for existance based on another field. "

e.g. from article

if exists (select *
    from authors
    where state = 'ca')
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'

if (select count(*)
    from authors
    where state = '172-32-1176') > 0
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'

In the pubs database there are only 23 records in the authors table. Even with that small number of records, the IF EXISTS version runs 4 times faster than selecting a count. This is because it stops as soon as it finds a single record that matches the criteria. The second statement must process all the rows that match. --<<--taboo :D

Reference : using if exists vs if count(*)

share|improve this answer
4  
The article you link to is wrong. Using WHERE EXISTS instead of IF EXISTS doesn't solve the issues with concurrency. There is no indication the OP is doing an upsert anyway. –  Martin Smith Dec 11 '12 at 10:27
    
@MartinSmith There are two articles that I have used in the answer :) So with the reference to the article which you are pointing at:- the intention of adding that reference is not to stress on concurrency but rather to show the advantages gained using 'if-else' vs 'where' on 'exisits'. Hence it's additional informatin to OP's quetion. I have given correct answer to OP's direct question as well. Author of that article is saying 'this method doesn't allow for concurrency'. So if the article is wrong, then only an expert like you can enlighten us. Which you did I guess. –  bonCodigo Dec 12 '12 at 5:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.