RVO (11/4/2013)
Another interesting thing with [ prj_detail ] table.A very simple query runs 4 seconds with hard_coded value co_code = '7001'
and it runs
1 minute using parameter.
use proddb6
declare @P_CO_CODE varchar(4)
set @P_CO_CODE = '7001'
select
accounting_period
from
prj_detail
where
co_code = @P_CO_CODE
and sys_doc_type_code = 'UP'
group by
accounting_period
order by
accounting_period
Code above is using rather variable than parameter and therefore
optimizer estimates that query returns 30% of data.
http://m.sqlmag.com/t-sql/optimizing-variables-and-parameters
I think this the reason for 1 minute run.