Performance Issue w/ IF Exists

• sqlSushi

  Mr or Mrs. 500

  Points: 516

  More actions

  January 21, 2004 at 9:54 am

  #107361

  Hi,

  We are encountering an issue while querying a very large table.

  While working in QA,

  if we just do a select * ... it comes back in about 1 second.

  if we so an if exists(select *...) it takes around 10 seconds.

   

  I thought if exists was supposed to be the fastest way??

  Anyone ese experience this issue?

   

  Thanks

   

• ThomasH

  SSCertifiable

  Points: 7437

  More actions

  January 21, 2004 at 11:49 am

  #491015

  Can you post the queries you are using in context?

  
  
  Once you understand the BITs, all the pieces come together

• jarretg

  SSCommitted

  Points: 1736

  More actions

  January 22, 2004 at 6:27 am

  #491107

  I have had this problem before when dealing with a trigger and the virtual inserted/deleted tables.

  What I had to do was change the if exists(select * ...) to if (select count(*) ...) > 0

  I thought that the if exists() should be faster as well, and it is in most cases (for me anyway).  But for this situation, it was out for the count(*). 

  Jarret

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  January 22, 2004 at 6:31 am

  #491109

  I might be flamed again , but I think EXISTS will almost always be faster than using COUNT(*).

  But as Thomas already said, without having the query code and, maybe the table structure any advise will be like a shot in the dark.

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• sqlSushi

  Mr or Mrs. 500

  Points: 516

  More actions

  January 26, 2004 at 9:03 am

  #491638

  Thank you for the posts,

   

  We did our own testing and optimizing and found the same thing, using a count was 6Xs faster than the If Exists().  I had always thought I learned that If Exists was always the way to go...

  It was happening on a large table with about .75 mill records.