Optimize query

  • Hello Sir,

    I have following two queries. Please let me know which is more optimized

    select * from jobqueue where (jobid between 5 and 38000) and (status = 'E')

    select * from jobqueue where (status = 'E') and (jobid between 5 and 38000)

    Actually I want to know that is there any performance change when we change the order of condition in where clause.

    Condition jobid between 5 and 38000 returns 1744 records

    Condition status = 'E' returns 3033 records

    Condition (jobid between 5 and 38000) and (status = 'E') returns 816 records

    I have checked the profiler but results are not always same.

    Please suggest.

    Thanks in advance 🙂

    Regards

    Paresh

  • Hi,

    As far as I know, the first choice should be the one for 2 reasons

    1. The first condition give less records

    2. Numeric field is quicker then varchar field

    Kupy

  • Check the execution plan. WHERE clause order can affect some execution plans, but won't affect all. On a very simply query like this, it's unlikely to make a difference.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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