I have a stored procedure that is taking 15-20 seconds to run. However,
if I take the query inside the stored procedure and run it... it only
takes 2-3 seconds. I've read something about parameter sniffing but not
sure if it applies to my stored procedure. Here is my sp:
CREATE PROC dbo.get_vendorActivityPeriod
(
@periodMonth integer
, @periodYear integer
, @propertyID varchar(6) = NULL
)
as
Begin
select
count(*) countVoucherHeader
, month(dateadd(day, A.date_posted-693596, '1 Jan
1900')) periodMonth
, year(dateadd(day, A.date_posted-693596, '1 Jan
1900')) periodYear
, A.vendor_code
, (select top 1 address_name from apmaster where
vendor_code = A.vendor_code order by address_type, pay_to_code)
address_name
, sum(amt_gross) as amountGross
, case when @propertyID IS NULL then '0000' else
@propertyID end propertyID
, case when @propertyID IS NULL then 'All
Communities' else (select distinct display_name from
property.dbo.complex where display_num = @propertyID) end
propertyDescription
from
apvohdr A
where
month(dateadd(day, A.date_posted-693596, '1 Jan
1900')) = @periodMonth
and year(dateadd(day, A.date_posted-693596, '1 Jan
1900')) = @periodYear
and A.posting_code = IsNull(@propertyID, A.posting_code)
group by
month(dateadd(day, A.date_posted-693596, '1 Jan
1900'))
, year(dateadd(day, A.date_posted-693596, '1 Jan
1900'))
, vendor_code
order by
year(dateadd(day, A.date_posted-693596, '1 Jan
1900'))
, month(dateadd(day, A.date_posted-693596, '1 Jan
1900'))
, address_name
end
thanks!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268343
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
