Different Query plans for a same query on the same database
-
HI,
I have one table called 'tbl_expected_funds' in the database (MS SQL 2000, SP4), which has got the Primary Key on 'expected_fund_id' column & an index on 'referral_id' column.
I am executing the same query with different parameters on the same table on the same database, which is giving me different query plans. The query is as follows:
select expected_fund_id
from tbl_expected_funds
where referral_id = 1 and wli_process_instance_id = '' and
datediff (day, expected_date, getdate()) < 0 and
datepart (month, expected_date) > datepart (month, getdate()) and
payment_type = '3'
order by expected_date
go
select expected_fund_id
from tbl_expected_funds
where referral_id = 3300 and wli_process_instance_id = '192.168.0.179-131ceda.1075590254e.-7ffc' and
datediff (day, expected_date, getdate()) < 0 and
datepart (month, expected_date) > datepart (month, getdate()) and
payment_type = '3'
order by expected_date
In case of 1st query it makes a index seek on the index, where as in the 2nd qyery it makes a index scan on the index creatde for the primary key.The query execution plan for the above queries are as follows:
Apart from the above, if the same query is executed through a stored procedure with those input parameters, the query plans remans the same (i.e. it makes a index seek on the index).
Can anybody help me in solving the problem. Why the same query with different parameter values shows different query execution plan.
Thankx in advance.
-
You should not be surprised that changing the parameters results in a different executions plan since the number of rows returned is likely to be different and so the most efficient way of returning those rows might not be the same.
If you want to compare execution plans, you should run DBCC FREEPROCCACHE before examining the plan for each respective query. (Be careful of doing this on a production system.) The sp_recompile stored procedure may also be useful if you are looking at execution plans for stored procedures. Also, search this site and others for "parameter sniffing".
John
-
Hi John,
Thankx a lot for the feedback. But if it is so, then in the SP when I am passing the parameters, how is it giving the same query plan?
-
Have you read about parameter sniffing?
John
-
You need to understand how query plans exist in the cache and the process by which they operate. I find this quite often in my roles of performance tuning, the plan is not always recompiled for a proc so if the parameters can vary the plan may not match the query and performance suffers. There's loads of articles about this, this site, technet, msdn, most sql server books.
For optimal stored proc performance the query should always use the same plan ( indexes etc. ) if not you might want to add a with recompile statement in the proc - sort of defeats using procs - but you should get the correct plan, with degraded performance of course!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
That "degraded performance" though, Colin, could be far less than the degraded performance resulting from NOT having the correct plan. I'm not suggesting you should recompile every SP routinely, but for those whose queries could potentially optimise to give very different plans with different parameters, WITH RECOMPILE is well worth considering. (There are other reasons for using SPs besides performance).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply