July 30, 2008 at 11:26 pm
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
July 31, 2008 at 12:51 am
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
July 31, 2008 at 5:28 am
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