March 12, 2009 at 2:07 pm
I have a stored procedure that if I call it directly in ssms it never stops running. But if I run it as a query takes about 18secs. Does anyone know why there would be such a difference or what can be done to fix it. I have checked all the indexes. Any help would be appreciated
I have posted the code below
ALTER PROCEDURE [dbo].[ACR_UPDATE_EZPosts_Activate]
--declare--
(
@ClientID INT = NULL
,@ClientSiteID INT = NULL
,@ScrapeDestinationID INT = NULL)
AS
--select @clientid =null,@clientsiteid=null,@scrapedestinationid=null
BEGIN
/*Set local dates*/
DECLARE @NowDate DATETIME, @EndDate DATETIME
SELECT @NowDate = GETDATE(), @EndDate = DATEADD(dd,29,GETDATE())
/*Get the ez post destination*/
SELECT TOP 1 @ScrapeDestinationID = SD.ScrapeDestinationID
FROM dbo.MDP_ScrapeDestination SD (NOLOCK)
WHERE SD.[Name] = 'EZ Post' AND DeleteDate IS NULL
/*Get the ads to Activate for Approved Sample jobs for approved clients*/
DECLARE @SampleAds TABLE(AdID INT, AdItemID INT)
INSERT INTO @SampleAds
SELECT A.AdID, AI.AdItemID
FROMdbo.MDP_ScrapeAttempt sa (NOLOCK)
JOINdbo.ACR_ScrapeLog sl (NOLOCK)
ONsa.ClientID = sl.ClientID
ANDUPPER(RTRIM(sa.Identifier)) = UPPER(RTRIM(sl.Identifier))
AND(sl.ClientID = COALESCE(@ClientID,sl.ClientID))
AND (sa.ClientSiteID = COALESCE(@ClientSiteID,SA.ClientSiteID))
AND (sl.ScrapeLogStatusID = 2) --Update the WebEndDate for all current jobs and refresh expired jobs that should be current.
AND (sa.ScrapeDestinationID = @ScrapeDestinationID)
AND sl.DeleteDate IS NULL
AND sa.Status = 1
JOINdbo.ACR_EZPostClient EZ (NOLOCK) ON SL.ClientID = COALESCE(EZ.AgencyClientID,EZ.ClientID) AND EzPostStateId = 2
JOINdbo.RAO_AdItem AI (NOLOCK) ON SL.AdItemID = AI.AdItemID
JOINdbo.SalesOrderItem SOI (NOLOCK) ON AI.SalesOrderItemID = SOI.SalesOrderItemID
JOINdbo.SalesOrder SO (NOLOCK) ON SOI.SalesOrderID = SO.SalesOrderID
JOINdbo.RAO_Ad A (NOLOCK) ON SO.SalesOrderID = A.SalesOrderID AND A.AdStatusID = 14
--Activate Any Sample Scrapes
UPDATE dbo.RAO_AdItem
SET
WebPubDate = @NowDate,
WebEndDate = DATEADD(dd,29,GETDATE())
FROM dbo.RAO_AdItem (NOLOCK)
JOIN @SampleAds A ON RAO_AdItem.AdItemID = A.AdItemID
INSERT INTO dbo.RAO_AdItemRun (AdItemID,StartDate,EndDate,CreateDate,AutoRenewed)
SELECT AdItemID, @NowDate, @EndDate, @NowDate, 0 FROM @SampleAds
/*Get the ads to Activate for inactive jobs for approved clients*/
DECLARE @InactiveAds TABLE(AdID INT, ScrapeLogID INT)
INSERT INTO @InactiveAds
SELECT DISTINCTa.AdID, sl.ScrapeLogID
FROMdbo.MDP_ScrapeAttempt sa (NOLOCK)
Joindbo.ACR_ScrapeLog sl (NOLOCK)
ONsa.ClientID = sl.ClientID
AND RTRIM(sa.Identifier) = RTRIM(sl.Identifier)
AND (sa.ClientID = COALESCE(@ClientID,sl.ClientID))
AND (sa.ClientSiteID = COALESCE(@ClientSiteID,SA.ClientSiteID))
AND (sa.ScrapeDestinationID = 11)
AND (sl.ScrapeLogStatusID < 3)--AND (sl.ScrapeLogStatusID = 2) --republish all items updated in the aditem table
AND sl.DeleteDate IS NULL
AND sa.Status = 1
JOIN(SELECTCrH.ClientSiteID, CrH.CrawlStartTime, CrH.CrawlEndTime
FROMdbo.MDP_CrawlHistory CrH
JOIN(SELECT ClientSiteID, [CrawlHistoryID] = MAX(CrawlHistoryID)
FROM dbo.MDP_CrawlHistory (NOLOCK)
WHERE ResultCount > 0 AND ExceptionID IS NULL
GROUP BY ClientSiteID) H ON CrH.CrawlHistoryID = H.CrawlHistoryID) CH
ONSA.ClientSiteID = CH.ClientSiteID
AND SA.AttemptTime BETWEEN CH.CrawlStartTime AND CH.CrawlEndTime
JOINdbo.MDP_CrawlSchedule CS (NOLOCK) ON CH.ClientSiteID = CS.ClientSiteID AND CS.EndDate >= @NowDate
JOINdbo.ACR_EZPostClient EZ (NOLOCK) ON SL.ClientID = COALESCE(EZ.AgencyClientID,EZ.ClientID) AND EzPostStateId = 2
JOINdbo.RAO_AdItem ai (NOLOCK) ON sl.AdItemID = ai.AdItemID
JOINdbo.SalesOrderItem soi (NOLOCK) ON ai.SalesOrderItemID = soi.SalesOrderItemID
JOINdbo.SalesOrder so (NOLOCK) ON soi.SalesOrderID = so.SalesOrderID
JOINdbo.RAO_Ad a (NOLOCK) ON so.SalesOrderID = a.SalesOrderID AND NOT a.AdStatusID IN(14)
--Activate Ads
UPDATEdbo.RAO_Ad
SETActive = 1,
RepublishWeb = 1
FROMdbo.RAO_Ad (NOLOCK)
JOIN(SELECT DISTINCT AdID FROM @InactiveAds
UNION SELECT DISTINCT AdID FROM @SampleAds) A ON RAO_Ad.AdID = A.AdID
--Activate Jobs from Log
UPDATEdbo.ACR_ScrapeLog
SETScrapeLogStatusID = 1,
StatusDate = @NowDate,
WebMembershipID = NULL
FROMdbo.ACR_ScrapeLog (NOLOCK)
JOIN(SELECT DISTINCT ScrapeLogID FROM @InactiveAds) A ON ACR_ScrapeLog.ScrapeLogID = A.ScrapeLogID
-- RETURN @@Error
END
March 12, 2009 at 3:54 pm
You need to lookup "Parameter Sniffing". This is usually the culprit in these situations.
One other comment is that you might get better performance if you remove the functions from the JOIN and Where clauses, that typically limits the effectiveness of indexes because they cause a scan as each row has to be passed through the function.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply