Which Clause Is Quicker?

• Buxton69-502449

  SSCrazy

  Points: 2026

  More actions

  November 12, 2007 at 8:40 am

  #184198

  Which is quicker and why?

  An IN statement with about 15 items in the IN criteria

  15 separate where statements using one criteria in each statement

  Having a massive where clause where there are 15 OR operators

  e.g.

  SELECT * FROM Table WHERE Code IN (d1,d2.....d15)

  OR

  SELECT * FROM Table WHERE Code = d1

  SELECT * FROM Table WHERE Code = d2

  .

  .

  SELECT..................................................= d15

  OR

  SELECT....WHERE Code = d1 OR Code = d2......Or Code = d15

  I think it might be the 15 seperate select statements but I'm not sure.

  Thanks

• Source-NH

  SSCarpal Tunnel

  Points: 4165

  More actions

  November 12, 2007 at 9:16 am

  #750296

  The answer lies within the execution plan. Suggest that you go into SSMS, write the three versions of the query, and then compare their execution plans to determine the answer. That is truly the only way that you can get the answer.

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  November 12, 2007 at 9:21 am

  #750299

  That may actually depend greatly on your data, but in a quick test on one of my production databases:

  SELECT * FROM AccountMaster WHERE AccountID IN (55,74)

  GO

  SELECT * FROM AccountMaster WHERE AccountID = 55

  SELECT * FROM AccountMaster WHERE AccountID = 74

  GO

  SELECT * FROM AccountMaster WHERE AccountID = 55 OR AccountID = 74

  Each query took 25% of the batch.

  The execution plans for the first and the last were exactly the same and the two in the middle - well that took twice as long because it had to run two queries (although it cached the execution plan and used it twice).

  The best thing to do for your situation is to turn on the Show Execution Plan option and it will give you the statistics of what percent of the time each query took.

  I think you will find that the first and last option perform the same and are much quicker, but this could be swayed by your indexing and the amount of data in your table.

  I also don't know why you would want to return 15 individual recordsets when you could return just one.

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  November 12, 2007 at 12:27 pm

  #750350

  you could also use a table with said 15 values. With an index I'd imagine that would be the fastest of all (with an inner join).

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?