February 15, 2012 at 5:04 am
AS in below query if we use "option(recompile)" then it execute in a one second but if we remove this option the it takes more that 30 minites.
declare @CLIENT_ID int
declare @AS_ON_DATE datetime
set @CLIENT_ID =128429
set @AS_ON_DATE ='12-FEB-2012'
select
sm.theme_syscode , AMC.AMC_NAME,stm.scheme_type_description,sm.scheme_name,
isnull(SUM(fifo.BOUGHT_QTY),0)- isnull(sum(fifo.sale_qty),0) as qty,
case isnull(sum(fifo.BOUGHT_QTY),0) - isnull(sum(fifo.sale_qty),0) when 0 then 0 else isnull(sum(BOUGHT_VALUE),0) end as initial_value,
max(nav.nav) nav , sum(fifo.bought_qty) * max(nav.nav) as amount , theme_name as Theme, sm.scheme_syscode
from wmcrs_fifo fifo
inner join wmcrs_mf_scheme_master sm on fifo.scrip_code = sm.scheme_syscode
inner join wmcrs_mf_scheme_type_master stm on sm.scheme_type_syscode=stm.scheme_type_syscode
inner join WMCRS_MF_AMC_MASTER AS AMC on SM.AMC_SYSCODE=AMC.AMC_SYSCODE
inner join wmcrs_theme_master th on th.theme_syscode = sm.theme_syscode
inner join (select scheme_syscode,max(nav) nav from wmcrs_mf_nav outnav
where nav_date = (select max(nav_date) from wmcrs_mf_nav innav
where innav.scheme_syscode = outnav.scheme_syscode and nav_Date < = @AS_ON_DATE) group by scheme_syscode
) nav on nav.scheme_syscode = fifo.scrip_code
where fifo.client_id = @CLIENT_ID
and fifo.product_id = 'MF'
and fifo.bought_date <= @AS_ON_DATE
GROUP BY AMC.AMC_NAME,stm.scheme_type_description,sm.scheme_name ,theme_name , sm.theme_syscode , sm.scheme_syscode
order by stm.scheme_type_description,sm.scheme_name
option(recompile)
February 15, 2012 at 8:18 am
This means that you have a bad plan in the cache. The bad plan could come from stale statistics at the moment the plan was calculated.
Try updating stats and issue the query again without RECOMPILE option and you should be fine.
-- Gianluca Sartori
February 15, 2012 at 10:39 pm
If one second is what you need it to be (especially compared to 30 minutes), why mess with success? Leave the option to recompile in the code. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2012 at 2:11 am
Jeff Moden (2/15/2012)
If one second is what you need it to be (especially compared to 30 minutes), why mess with success? Leave the option to recompile in the code. 😉
:hehe: "Jeff Moden's aphorisms". I could make a book out of that. 🙂
I agree to some extent, but I would investigate the root cause anyway. You might have other statements suffering from this issue totally unnoticed due to a more reasonable execution time.
-- Gianluca Sartori
February 16, 2012 at 5:59 am
It sounds like a case of bad parameter sniffing, but you can't be sure unless you investigate. You'd need to capture the two execution plans, one for when it's fast, and one for when it's slow. Then compare the compile values on the parameters to understand what leads to which plan. Then investigate the data to understand the distribution that leads to different plans.
The real shocker is that you don't occasionally get the 30 minute execution anyway.
"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
February 16, 2012 at 6:19 am
Grant Fritchey (2/16/2012)
The real shocker is that you don't occasionally get the 30 minute execution anyway.
Variables, not parameters, so no parameter sniffing and the optimiser probably mis-guessing the row counts. Option recompile means that the optimiser can sniff the variables, so probably better plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2012 at 6:26 am
GilaMonster (2/16/2012)
Grant Fritchey (2/16/2012)
The real shocker is that you don't occasionally get the 30 minute execution anyway.Variables, not parameters, so no parameter sniffing and the optimiser probably mis-guessing the row counts. Option recompile means that the optimiser can sniff the variables, so probably better plan.
Oops, didn't notice that. Makes sense.
"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
February 16, 2012 at 7:00 am
And to answer the post header question of "When we should use in a query or a procesure "option(recompile)""...
You should use option(recompile) when you want to ensure that SQLServer will re-evaluate the query plan on every execution.
It quite often used in ETL queries when tables it operates with are subject of large volume of insert/update/delete operations. The cached query plans in such cases may not be the best for the new statistics.
It's one of the way to solve parameter sniffing problem (may not be the best one for all cases).
February 16, 2012 at 7:08 am
Gianluca Sartori (2/16/2012)
Jeff Moden (2/15/2012)
If one second is what you need it to be (especially compared to 30 minutes), why mess with success? Leave the option to recompile in the code. 😉:hehe: "Jeff Moden's aphorisms". I could make a book out of that. 🙂
I agree to some extent, but I would investigate the root cause anyway. You might have other statements suffering from this issue totally unnoticed due to a more reasonable execution time.
I agree. I think it's Audi that has a new add campaign for their continuous improvement program. Their tag line for the campaign is "If it ain't broke, fix it". 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2012 at 8:16 am
Actually I have update the statistics then also without recompile option it takes more time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply