could someone please help to improve the performance of this store proc,it takes 2hrs 30mins to execute

Question

Post reply

could someone please help to improve the performance of this store proc,it takes 2hrs 30mins to execute

ansu_411

SSC Rookie

Points: 33
More actions
December 21, 2009 at 3:44 pm

#136870

USE [Pubs]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spMedia_MediaFileArchive]
@MonthsTINYINT = 5 -- months to keep
,@ListingStatusBatchTINYINT = 75 -- checks to see if listing is active or not
,@BatchSizeINT= 300
,@MaxRecordINT= 500000
,@DebugBIT = 0
AS
SET NOCOUNT ON
SET ANSI_WARNINGS ON
BEGIN
DECLARE @ErrorSeverityINT,
@ErrorStateINT,
@SubmissionIDINT,
@RecordsToArchINT,
@TotalArchINT,
@MediaClassIDSMALLINT,
@xmlparamVARCHAR(8000),
@ErrorMessageVARCHAR(8000),
@xmlXML,
@ArchMediaClassCodeVARCHAR(10),
@ArchiveActionVARCHAR(10),
@MinMediaIDINT,
@TotalRecordsToArchSMALLINT,
@TotalSearchedRecINT,
@CurrRcdSrcSMALLINT,
@MaxTempMediaIDINT,
@archivemediaclassid int
DECLARE @TMediaClass TABLE (
MediaClassIDSMALLINT,
MediaClassCodeCHAR(12)
)
CREATE TABLE #tLIST_MEDIA_DA (
MLSIDchar(4),
SourceListingIDvarchar(15),
DataSourceNameVARCHAR(30),
Typechar(1),
ListingIDint,
Statuschar(1)
)
CREATE TABLE #media(
EntityIDBIGINT,
MediaClassIDSMALLINT,
SubScriberCheckedBIT DEFAULT(0)
)
BEGIN TRY
IF @Debug = 1
BEGIN
SELECT '-> Starting batch: ' + CONVERT(VARCHAR(25), GETDATE(), 121)
END
SET @ArchMediaClassCode = 'RDCARCHIMG'
SET @ArchiveAction = 'archive'
SET @TotalArch = 0
SET @ErrorMessage = ''
INSERT @TMediaClass (MediaClassID, MediaClassCode)
SELECT MediaClassID, MediaClassCode
FROM [MEDIA_CLASS] mc (NOLOCK)
WHERE mediaclasscode IN ('RDCMLLIIMG', 'RDCCSLIIMG', 'RDCSSLIIMG')
SET @RecordsToArch = 0
SET @TotalSearchedRec = 0
SELECT @MinMediaID = MIN(MediaID)
FROM Media m (NOLOCK)
JOIN @TMediaClass tmc ON m.MediaClassID = tmc.MediaClassID
WHEREm.UpdateDate <= DATEADD(m, -@months, GETDATE())
select @archivemediaclassid = MediaClassID
from [MEDIA_CLASS] mc (NOLOCK)
WHERE mediaclasscode = @ArchMediaClassCode
WHILE 1 = 1
BEGIN
-- get 300 images
WHILE 1 = 1
BEGIN
INSERT INTO #media ( EntityID, MediaClassID )
SELECT TOP(@ListingStatusBatch) m.entityid, m.mediaclassid
FROM [MEDIA] m (NOLOCK)
JOIN @TMediaClass tmc ON m.MediaClassID = tmc.MediaClassID
WHERE m.MediaID >= @MinMediaID
AND m.UpdateDate <= DATEADD(m, -@months, GETDATE())
GROUP BY m.entityid, m.mediaclassid
SET @CurrRcdSrc = @@ROWCOUNT
IF @Debug = 1
BEGIN
SELECT DISTINCT 'records to archive', *
FROM #media
END
-- update records with the same entityid and mediaclassid so that we don't have to check them again
UPDATE m
SET UpdateDate = GETDATE()
FROM #Media tm
JOIN media m ON tm.entityid = m.entityid AND m.MediaClassID = tm.MediaClassID
delete tm
from #media tm
join media m (nolock) on tm.entityid = m.entityid
where m.MediaClassID = @archivemediaclassid
SELECT @xmlparam = CONVERT(VARCHAR(8000), (
SELECT'@listingid'= tm.entityid,
'@listingtype'= 'P'
FROM #media tm
WHERE SubScriberChecked = 0
GROUP BY tm.entityid
FOR XML PATH('listing'), ROOT('listings')
))
INSERT INTO #tLISTING_MEDIA_DA (
MLSID,
SourceListingID,
DataSourceName,
Type,
ListingID,
Status
)
EXEC xListingInternal_Srv.xlisting.dbo.spXLST_DAListingID_sel @xml = @xmlparam, @Resultset = 1
-- makes sure we do not check these listings again
UPDATE #Media
SET SubScriberChecked = 1
WHERE SubScriberChecked = 0
-- we want at least the batch size
SELECT @RecordsToArch = COUNT(1)
FROM #tLISTING_MEDIA_DA tlmda
JOIN #media m ON tlmda.ListingID = m.entityid
WHERE tlmda.Status IS NULL
IF @Debug = 1
BEGIN
SELECT '@xmlparam' = @xmlparam
SELECT '-> Total Records TO Archive [' + CAST(@RecordsToArch AS VARCHAR(10)) + '] : ' + CONVERT(VARCHAR(25), GETDATE(), 121)
END
-- check to see if theres any record to archive during the time we query
IF (@RecordsToArch >= @BatchSize or @CurrRcdSrc = 0)
BEGIN
BREAK
END
END
-- total of how many will be archived
SET @TotalSearchedRec = @TotalSearchedRec + @RecordsToArch
IF @Debug = 1
BEGIN
SELECT '-> Formating xml for submission [' + cast(@RecordsToArch as varchar(10)) + '] : '
SELECT * FROM #tLISTING_MEDIA_DA WHERE status IS NULL
END
-- insert into the media_queue table to archive
SELECT @xml = (
SELECT
'@entityid'= m.[EntityID],
'@action'= 'archive',
'@sequence'= 0,
'@mediaclasscode'= @ArchMediaClassCode
FROM #tLISTING_MEDIA_DA tlmda
JOIN #media m ON tlmda.ListingID = m.entityid
WHERE tlmda.Status IS NULL
GROUP BY m.[EntityID]
FOR XML PATH('image'), ROOT('images'),TYPE
)
IF @Debug = 1
BEGIN
SELECT '-> Submitting files for Archive: ' + CONVERT(VARCHAR(25), GETDATE(), 121)
SELECT '@xml' = @xml
END
IF ( @xml IS NOT NULL )
begin
EXEC spXMED_MediaQueue_Ins @SubmissionID = @SubmissionID OUTPUT, @ExternalID = '', @InitialStatus = 'Queued', @Priority = 0, @xml = @xml--, @debug = 1
END
IF @@ERROR != 0
BEGIN
SET @ErrorMessage = @ErrorMessage + ' ' + 'Error executing spXMED_MediaQueue_Ins :'
RAISERROR( @ErrorMessage, 11, 1)
END
IF @Debug = 1
BEGIN
SELECT 'SubmissionID' = @SubmissionID
SELECT '-> Finished Submitting files for Archive: ' + CONVERT(VARCHAR(25), GETDATE(), 121)
END
TRUNCATE TABLE #tLISTING_MEDIA_DA
TRUNCATE TABLE #media
IF (@TotalSearchedRec >= @MaxRecord OR @TotalSearchedRec = 0 or @CurrRcdSrc = 0)
BEGIN
BREAK
END
END
RETURN 0
END TRY
BEGIN CATCH
SELECT
@ErrorMessage= @ErrorMessage + ' ' + ERROR_MESSAGE(),
@ErrorState= ERROR_STATE(),
@ErrorSeverity= ERROR_SEVERITY()
RAISERROR(@ErrorMessage,
@ErrorSeverity,
@ErrorState
)
RETURN -1
END CATCH
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

For assistance with this, we need to have the table structure, index structure and actual execution plan.

Please post this information to help us help you.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Heh... you have a triangular join, a while loop, and XML and still want to know why this is slow? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That's an awful lot of code. Do some analysis yourself and find where the slow portions of that procedure are, then post those. Also, please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

There's some info on breaking procedures down here

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 4

"While 1 = 1" ?? I'm surprised it ever ends! lol

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;