The following procedure took over 24 hours to run. I've tried several
things to make it faster but since I am a beginner I figured I would
just post it as I originally had it and see how an expert might make
it run faster. I can't manipulate the database design. Sorry if I'm
leaving out a lot of information. Here it is:

CREATE PROC dbo.usp_CopyWOData
(
@CutOffDate datetime = NULL
)
AS
BEGIN

--STORED PROCEDURE TO PROPERLY COPY ALL COMPLETED, CANCELLED AND GLCL
WORK ORDERS,
--LAST UPDATED BEFORE A SPECIFIED DATE, INTO AN ARCHIVE TABLE
/*
Tables needed before execution:
AR5EVENTS
AR5EVTSYSTEMS
AR5EVENTOBJECTS
AR5ACTIVITIES
AR5ACTSCHEDULES
AR5ADDITIONALCHARGE
AR5TRANSLINES
*/

IF @CutOffDate IS NULL
BEGIN
SET @CutOffDate = getdate() - 180
END

BEGIN TRAN

--STEP 1--------------LEVEL
1----------------------------------------------
INSERT INTO dbo.AR5EVENTS
SELECT * from dbo.R5EVENTS --level 1
WHERE EVT_UPDATED < @CutOffDate AND
(EVT_STATUS = 'GLCL' OR
EVT_STATUS = 'C' OR EVT_STATUS = 'CANC')

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying R5EVENTS', 16, 1)

END
----------------------------------------------------------------------------

--STEP 2--------------LEVEL
2----------------------------------------------
INSERT INTO dbo.AR5EVTSYSTEMS
SELECT * from dbo.R5EVTSYSTEMS --level 2
WHERE ESY_EVENT IN
(
SELECT EVT_CODE
FROM dbo.R5EVENTS
WHERE EVT_UPDATED < @CutOffDate AND
(EVT_STATUS = 'GLCL' OR
EVT_STATUS = 'C' OR EVT_STATUS = 'CANC')
)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data from R5EVTSYSTEMS',
16, 1)

END
----------------------------------------------------------------------------

--STEP 3--------------LEVEL
2----------------------------------------------
INSERT INTO dbo.AR5EVENTOBJECTS
SELECT * from dbo.R5EVENTOBJECTS --level 2
WHERE EOB_EVENT IN
(
SELECT EVT_CODE
FROM dbo.R5EVENTS
WHERE EVT_UPDATED < @CutOffDate AND
(EVT_STATUS = 'GLCL' OR
EVT_STATUS = 'C' OR EVT_STATUS = 'CANC')
)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data from R5EVENTOBJECTS',
16, 1)

END
----------------------------------------------------------------------------

--STEP 4--------------LEVEL
2----------------------------------------------
INSERT INTO dbo.AR5ACTIVITIES
SELECT * from dbo.R5ACTIVITIES --level 2
WHERE ACT_EVENT IN
(
SELECT EVT_CODE
FROM dbo.R5EVENTS
WHERE EVT_UPDATED < @CutOffDate AND
(EVT_STATUS = 'GLCL' OR
EVT_STATUS = 'C' OR EVT_STATUS = 'CANC')
)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data from R5ACTIVITIES',
16, 1)

END
----------------------------------------------------------------------------

--STEP 5--------------LEVEL
3----------------------------------------------
INSERT INTO dbo.AR5ACTSCHEDULES
SELECT * from dbo.R5ACTSCHEDULES --level 3
WHERE (CAST(ACS_EVENT AS NVARCHAR) + CAST(ACS_ACTIVITY AS NVARCHAR))
IN
(
SELECT (CAST(ACT_EVENT AS NVARCHAR) + CAST(ACT_ACT AS NVARCHAR))
FROM dbo.R5ACTIVITIES
WHERE ACT_EVENT IN
(
SELECT EVT_CODE
FROM dbo.R5EVENTS
WHERE EVT_UPDATED < @CutOffDate AND
(EVT_STATUS = 'GLCL' OR
EVT_STATUS = 'C' OR EVT_STATUS = 'CANC')
)
)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data from R5ACTSCHEDULES',
16, 1)

END
----------------------------------------------------------------------------

--STEP 6--------------LEVEL
3----------------------------------------------
INSERT INTO dbo.AR5ADDITIONALCHARGE
SELECT * from dbo.R5ADDITIONALCHARGE --level 3
WHERE (CAST(ADC_EVENT AS NVARCHAR) + CAST(ADC_ACT AS NVARCHAR)) IN
(
SELECT (CAST(ACT_EVENT AS NVARCHAR) + CAST(ACT_ACT AS NVARCHAR))
FROM dbo.R5ACTIVITIES
WHERE ACT_EVENT IN
(
SELECT EVT_CODE
FROM dbo.R5EVENTS
WHERE EVT_UPDATED < @CutOffDate AND
(EVT_STATUS = 'GLCL' OR
EVT_STATUS = 'C' OR EVT_STATUS = 'CANC')
)
)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data from
R5ADDITIONALCHARGE', 16, 1)

END
----------------------------------------------------------------------------

--STEP 7--------------LEVEL
3----------------------------------------------
INSERT INTO dbo.AR5TRANSLINES
SELECT * from dbo.R5TRANSLINES --level3
WHERE (CAST(TRL_EVENT AS NVARCHAR) + CAST(TRL_ACT AS NVARCHAR)) IN
(
SELECT (CAST(ACT_EVENT AS NVARCHAR) + CAST(ACT_ACT AS NVARCHAR))
FROM dbo.R5ACTIVITIES
WHERE ACT_EVENT IN
(
SELECT EVT_CODE
FROM dbo.R5EVENTS
WHERE EVT_UPDATED < @CutOffDate AND
(EVT_STATUS = 'GLCL' OR
EVT_STATUS = 'C' OR EVT_STATUS = 'CANC')
)
)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data from R5TRANSLINES',
16, 1)

END
----------------------------------------------------------------------------

IF @@TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END

END
GO

(remylawrence@gmail.com) writes:
> The following procedure took over 24 hours to run. I've tried several
> things to make it faster but since I am a beginner I figured I would
> just post it as I originally had it and see how an expert might make
> it run faster. I can't manipulate the database design. Sorry if I'm
> leaving out a lot of information. Here it is:

Being an expert is one thing. But to optimize a stored procedure only
from seeing a code does not take an expert - it takes a person with
skills in clairvoyance.

That is, I would need to know the table and index defintions, have
some information about the data distribution. It would help to have
the current plans. And of course, it would help to know which query/ies
that is taking most of the time. I would also like to know how large
portion of the table much a condition like WHERE EVT_UPDATED < @CutOFfDate
would hit.

So for now, I will offer only observation:

> SELECT * from dbo.R5ACTSCHEDULES --level 3
> WHERE (CAST(ACS_EVENT AS NVARCHAR) +
> CAST(ACS_ACTIVITY AS NVARCHAR)) IN
> (
> SELECT (CAST(ACT_EVENT AS NVARCHAR) +
> CAST(ACT_ACT AS NVARCHAR))
> FROM dbo.R5ACTIVITIES
> WHERE ACT_EVENT IN
> (
> SELECT EVT_CODE
> FROM dbo.R5EVENTS
> WHERE EVT_UPDATED < @CutOffDate AND
> (EVT_STATUS = 'GLCL' OR
> EVT_STATUS = 'C' OR EVT_STATUS = 'CANC')
> )
> )

I don't know why you use string concatenation here, but my guess is
that you don't know about EXISTS:

SELECT *
FROM dbo.R5ACTSCHEDULES as
WHERE EXISTS (SELECT *
FROM dbo.R5ACTIVITIES a
WHERE a.ACT_EVENT = as.ACS_EVENT
AND a.ACT_ACTIVITY = as.ACS_ACTIVITY
AND EXISTS
(SELECT *
FROM dbo.R5EVENTS e
WHERE a.ACT_EVETNT = e.EVT_CODE
AND EVT_UPDATED < @CutOffDate
AND EVT_STATUS IN ('GLCL', 'C', 'CANC')))

How much this would help I don't know, though.

Since the conditions reapperars in the subqueries, it could also be
an idea to capture the inserted rows with the OUTPUT clause (SQL 2005
only) and then use this in the remaining queries. But that depends
on where the bottleneck is.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Which client application you use to run?

sqlcmd? osql? Query Analyzer?

I've experienced sqlcmd is very slow ... sometimes.

Maybe when there are lot of messages by PRINT
or I forgot to do SET NOCOUNT ON.

> The following procedure took over 24 hours to run. I've tried several
> things to make it faster but since I am a beginner I figured I would
> just post it as I originally had it and see how an expert might make
> it run faster. I can't manipulate the database design. Sorry if I'm
> leaving out a lot of information. Here it is:

(snip)

--
koby