August 10, 2012 at 8:30 am
Hi All,
we are facing issue while running query. when t-sql query run with hard coded value the sql result set provide result in 2 min but when we passing parameter value the result set provide more then 45 min.
from database side all index , stats are up to date ? can you provide soln?
--vfpostcalcdata :::: It's view -695828971 no fo records
declare @Year char(4)--Meant to be a parameter
declare @Month char(3)--Meant to be a parameter
set @Year = '2012'
set @Month = 'JUL'
declare @ProjInstKey int
declare @TimeZeroKey int
declare @PeriodCount int--This is the max number of time periods (months) that will be captured
set @PeriodCount = 6
select
@ProjInstKey = i.d_projection_instance_key
from
D_PROJECTION_INSTANCE i with (nolock)
join D_TIME t on i.Release_time_key = t.d_time_key
where
t.d_time_year = @Year
and t.d_time_month = @Month
and i.IS_PRIMARY = 'Y'
select
@TimeZeroKey = t.d_time_key
from
D_PROJECTION_INSTANCE i
join D_TIME t with (nolock) on i.Actuals_time_key = t.d_time_key
where
i.d_projection_instance_key = @ProjInstKey
select
fa.d_assignment_name 'Assignment', fa.d_category_name 'CAB',
convert(smalldatetime, DATEADD(d, -1, DATEADD(mm, 1, convert(smalldatetime, fa.d_time_year + '/' + fa.d_time_month + '/1')))) 'EOMDate',
0 'TotalReinvExp',
max(fa.fvalue) 'TotalFloatExp',
0 'FFReinvExp',
0 'M1LiborReinvExp',
0 'M3LiborReinvExp',
0 'M6LiborReinvExp',
0 'M3TreasReinvExp',
0 'Y1TreasReinvExp',
0 'Y2TreasReinvExp',
0 'Y3TreasReinvExp',
0 'Y4TreasReinvExp',
0 'Y5TreasReinvExp',
0 'Y10TreasReinvExp',
0 'Y30TreasReinvExp',
case when fc.d_category_name like '%_M' then sum(fa.fvalue * fc.fvalue) else 0 end 'M1LiborFloatExp',
case when fc.d_category_name like '%_Q' then sum(fa.fvalue * fc.fvalue) else 0 end 'M3LiborFloatExp',
case when fc.d_category_name like '%_S' then sum(fa.fvalue * fc.fvalue) else 0 end 'M6LiborFloatExp'
from
vfpostcalcdata fa with (nolock) join vfpostcalcdata fc with (nolock) on
fa.d_time_key = fc.d_time_key and fa.d_assignment_key = fc.d_assignment_key
and fa.d_category_name = left(fc.d_category_name, len(fc.d_category_name)-2)
and fa.d_projection_instance_key = fc.d_projection_instance_key
where
-- ***********************
-- PARAMETER VALUE
--************************
fa.d_projection_instance_key = @ProjInstKey
and fa.d_time_key between @TimeZeroKey and @TimeZeroKey + @PeriodCount
-- ***********************
-- HARD CODED VALUE
--************************
--fa.d_projection_instance_key = 528
--and fa.d_time_key between 126 and 126 + 72
and (fc.d_category_name like '%_M' or fc.d_category_name like '%_Q' or fc.d_category_name like '%_S')
and fa.fvalue <> 0
and fc.fvalue <> 0
group by fa.d_assignment_name, fa.d_category_name, fc.d_category_name, fa.d_time_year, fa.d_time_month
option(recompile)
🙂 🙂 🙂
Amey ghag
<a href='http://' class='authorlink' target='_blank'></a>
August 13, 2012 at 4:44 am
It sounds like a case of bad parameter sniffing. This occurs when a particular parameter value is used and the statistics are different for it than for the hard-coded value. Can you look at the execution plans for each of the two versions of the query? If they are different, that will tell you what you need.
From the code, are you sure you're using a parameter & not a local variable? If you're using a local variable, you're going to get a sampled selection from your statistics. If you use a parameter, you're going to get specific values, almost like hard coding it.
"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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply