The following procedure took about 24 hours to run. I've tried
changing the nested queiries to joins, getting rid of the @Cutoffdate
variable (manually entering a date), setting nocount on, running from
outside a stored procedure and a bunch of other things but since I am
truely a beginner I figured I would just send it as I had it
originally and see what an expert can do to make it the most
effieicnet. It is the first step to archive a very large database.
Sorry if I'm leaving out important details. 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 (AT) gmail (DOT) com wrote:

How long does each part take? What indexes are defined on each of
the tables used?

How many rows are in R5EVENTS? How many meet these criteria?

Give AR5EVENTS an index on EVT_CODE (if it doesn't already have
one), then:

INSERT INTO dbo.AR5EVTSYSTEMS
SELECT es.* from dbo.R5EVTSYSTEMS es
JOIN AR5EVENTS e on es.ESY_EVENT = e.EVT_CODE

Or, if AR5EVENTS contains many rows for each EVT_CODE, then maybe
create a separate table containing just those distinct values (and
with an index on EVT_CODE), and use that instead.

Similarly for subsequent steps.

I think formulas in the WHERE clause tend to slow things down a
lot. Better to do it this way:

INSERT INTO dbo.AR5ACTSCHEDULES
SELECT as.* from dbo.R5ACTSCHEDULES
WHERE EXISTS (
SELECT *
FROM dbo.R5ACTIVITIES a
JOIN AR5EVENTS e on a.ACT_EVENT = e.EVT_CODE
WHERE a.ACT_EVENT = as.ACS_EVENT AND a.ACT_ACT = as.ACS_ACTIVITY
)

Similarly for subsequent steps.

Here is the SP with some changes. A few notes:

1). Make sure you have appropriate indexes on the columns involved in the
WHERE filters.
2). Some of the changes I made are cosmetic, like replacing the multiple OR
conditions with IN, because the optimizer will probably generate the same
plan.
3). Look more closely at the changes in steps 5, 6, and 7. You had the
concatenation of columns in the filter which is not efficient. I changed to
EXISTS, but you have to verify the logic as not sure of the exact meaning of
those columns.
4). You can isolate and time the queries individually to see what are the
slow performers and then concentrate on improving there.
5). Using 'SELECT *' spells a trouble (except on EXISTS), a good idea to
list all columns.

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 IN ('GLCL', 'C', '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 IN ('GLCL', 'C', '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 IN ('GLCL', 'C', '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 IN ('GLCL', 'C', '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 AS S --level 3
WHERE EXISTS
(
SELECT *
FROM dbo.R5ACTIVITIES AS T
WHERE ACT_EVENT IN
(
SELECT EVT_CODE
FROM dbo.R5EVENTS
WHERE EVT_UPDATED < @CutOffDate
AND EVT_STATUS IN ('GLCL', 'C', 'CANC')
)
AND S.ACS_EVENT = T.ACT_EVENT
AND S.ACS_ACTIVITY = T.ACT_ACT
)

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 AS C --level 3
WHERE EXISTS
(
SELECT *
FROM dbo.R5ACTIVITIES AS T
WHERE ACT_EVENT IN
(
SELECT EVT_CODE
FROM dbo.R5EVENTS
WHERE EVT_UPDATED < @CutOffDate
AND EVT_STATUS IN ('GLCL', 'C', 'CANC')
)
AND C.ADC_EVENT = T.ACT_EVENT
AND C.ADC_ACT = T.ACT_ACT
)

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 AS T --level3
WHERE EXISTS
(
SELECT *
FROM dbo.R5ACTIVITIES AS R
WHERE ACT_EVENT IN
(
SELECT EVT_CODE
FROM dbo.R5EVENTS
WHERE EVT_UPDATED < @CutOffDate
AND EVT_STATUS IN ('GLCL', 'C', 'CANC')
)
AND T.TRL_EVENT = R.ACT_EVENT
AND T.TRL_ACT = R.ACT_ACT
)

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

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Feb 8, 4:24*pm, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) com> wrote:
THanks for your suggestions! I'm testing right now and it's looking
good.

<remylawrence (AT) gmail (DOT) com> wrote


Do the tables AR5EVENTS, etc have indices on them?

How many rows are in them to begin with?

What about the R5EVENTS, etc tables?


Depending on the size, it may be faster to drop the indices on AR5* and
insert and them rebuild the indices.

And if you don't have indices on the R5* tables, it may be worth adding
them.





--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html