December 22, 2011 at 8:28 am
Why would query #1 execute in about 20 seconds and query # 2 take about 5 minutes. Same logic, same results. It all has to do with the evaluation of the variables in the where clause concerning the dates.
NOTE: The Sword_Calendar is our table of fiscal dates. Based on a year and week number passed I develop the Sunday and Saturday dates bookending the week selected.
dw_pros_dt is defined as smalldatetime
QUERY #1
Declare @sun datetime, @Sat datetime
select @sun = b.wk_begin_date
,@Sat = b.wk_end_date
from (select top 1 convert(datetime,wk_begin_date,121)[Wk_Begin_Date]
, convert(datetime,wk_end_date,121)[Wk_End_Date] from Sword_Calendar
where yr_number = @Yr
and wk_number = @Wk) b
declare @sql varchar(max)
set @sql = '
insert into Pharmacy_Flash
select convert(varchar(10),dw_Pros_dt,121) ,
'''',
sum(dw_sales_rtl)[TotalSales],
sum(case when dwstore >= 7000 then dw_sales_rtl
else 0
end) [FranchiseSales],
sum(case when dwstore >= 7000 then dw_sales_rtl
else 0
end) [CompanySales]
from spf053_Sales a
join dwf160_itemfile b
on a.dwsku = b.dw_sku
where dw_Pros_DT >= convert(datetime,''' + convert(varchar(28),@Sun,121) + ''',102)
and dw_Pros_dt <= convert(datetime,'''+ convert(varchar(28),@Sat,121) + ''',102)
and b.dept = 631
group by dw_pros_dt
order by dw_pros_dt
'
exec ( @sql)
QUERY #2
Declare @sun datetime, @Sat datetime
select @sun = b.wk_begin_date
,@Sat = b.wk_end_date
from (select top 1 convert(datetime,wk_begin_date,121)[Wk_Begin_Date]
, convert(datetime,wk_end_date,121)[Wk_End_Date] from Sword_Calendar
where yr_number = @Yr
and wk_number = @Wk) b
insert into Pharmacy_Flash
select convert(varchar(10),dw_Pros_dt,121) ,
'',
sum(dw_sales_rtl)[TotalSales],
sum(case when dwstore >= 7000 then dw_sales_rtl
else 0
end) [FranchiseSales],
sum(case when dwstore < 7000 then dw_sales_rtl
else 0
end) [CompanySales]
from spf053_Sales a
join dwf160_itemfile b
on a.dwsku = b.dw_sku
where dw_Pros_DT >= @sun
and dw_Pros_dt <= @Sat
and b.dept = 631
group by dw_pros_dt
order by dw_pros_dt
December 22, 2011 at 8:36 am
Probably this: http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply