Post reply

Which Clause Is Quicker?

  • 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

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

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

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply