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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy