You may want to recompile the sp... It may have been compiled before the
"statistics" for the tables involved were updated.
You could check the query plan of the sp vs. the query to see how they
differ. (Not sure what db you are using...)
Mark
-----Original Message-----
From: Tim Do [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 1:45 PM
To: CF-Talk
Subject: OT: sql question
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:268345
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
