Archiving...INSERT & DELETE

Question

Archiving...INSERT & DELETE

oscarooko

SSC-Addicted

Points: 445
More actions
September 11, 2012 at 8:34 am

#267594

I am trying to delete old records from a table, and insert them into another table. Problem is that it is taking too long. The table has about 3million records. Query is running for more than 1 hr.
Any ideas of how to expedite this process?

Viewing 15 posts - 1 through 15 (of 32 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

oscarooko (9/11/2012)
I am trying to delete old records from a table, and insert them into another table. Problem is that it is taking too long. The table has about 3million records. Query is running for more than 1 hr.
Any ideas of how to expedite this process?

That's far too long. Can you post the query? The estimated plan would be even better.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

oscarooko (9/11/2012)
I am trying to delete old records from a table, and insert them into another table. Problem is that it is taking too long. The table has about 3million records. Query is running for more than 1 hr.
Any ideas of how to expedite this process?

You should probably consider putting this into batches. It will actually end up taking longer but it will greatly ease the pressure on your system.

I am pretty swamped right now but let me know if you need some help getting this going.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

ChrisM@Work (9/11/2012)
oscarooko (9/11/2012)
I am trying to delete old records from a table, and insert them into another table. Problem is that it is taking too long. The table has about 3million records. Query is running for more than 1 hr.
Any ideas of how to expedite this process?
That's far too long. Can you post the query? The estimated plan would be even better.

Maybe, maybe not. 3 million records, but how big is each record? Is the INSERT and DELETE being done as a single batch? Are there indexes defined on the destination (archive) table?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Lynn Pettis (9/11/2012)
ChrisM@Work (9/11/2012)
oscarooko (9/11/2012)
I am trying to delete old records from a table, and insert them into another table. Problem is that it is taking too long. The table has about 3million records. Query is running for more than 1 hr.
Any ideas of how to expedite this process?
That's far too long. Can you post the query? The estimated plan would be even better.
Maybe, maybe not. 3 million records, but how big is each record? Is the INSERT and DELETE being done as a single batch? Are there indexes defined on the destination (archive) table?

Pound to a penny it's two-stage with a non-sargable where clause on the delete 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

ChrisM@Work (9/11/2012)
Lynn Pettis (9/11/2012)
ChrisM@Work (9/11/2012)
oscarooko (9/11/2012)
I am trying to delete old records from a table, and insert them into another table. Problem is that it is taking too long. The table has about 3million records. Query is running for more than 1 hr.
Any ideas of how to expedite this process?
That's far too long. Can you post the query? The estimated plan would be even better.
Maybe, maybe not. 3 million records, but how big is each record? Is the INSERT and DELETE being done as a single batch? Are there indexes defined on the destination (archive) table?
Pound to a penny it's two-stage with a non-sargable where clause on the delete 😉

Not a bet I will take. Need to see the query.

oscarooko SSC-Addicted Points: 445 More actions · Answer 6

This is the query I have. I already considered deleting in batches

CREATE PROCEDURE [dbo].[usp_SPT_ArchiveLog]

(@dCutOffDate DateTime)

AS

BEGIN

SET NOCOUNT ON;

BEGIN TRY

BEGIN TRAN

INSERT INTO [dbo].[SPTLogArchive]

([LogID]

,[LogTime]

,[ArchiveTime])

SELECT [LogID]

,[LogTime]

,GetDate()

FROM [dbo].[SPTLog]

WHERE [LogTime] < @dCutOffDate;

PRINT 'Data inserted into the Archive table'

--Delete the archived data from SPTLog Table

---------------------------------

DECLARE

@topcount int

,@batch int

Select @topcount = 10000

Select @batch = 1

WHILE @batch <> 0

Begin

Delete TOP (@topcount) FROM [dbo].[SPTLog]

WHERE LogTime < @dCutOffDate;

Set @batch = @@ROWCOUNT

PRINT @batch

End

-------------------------

COMMIT TRAN

END TRY

BEGIN CATCH

--Rollback any active or uncommittable transactions before

--Inserting information in the ErrorLog

DECLARE @nResult Int

IF @@TRANCOUNT > 0

BEGIN

print 'SQL Error, causing a TRANSACTION ROLLBACK '

ROLLBACK TRAN

END

--Log Error

EXEC usp_LogSQLErrorProc

--Return

SET @nResult = -1 --Set nResult for Return, any integer other than zero indicates failure.

RETURN @nResult

END CATCH

END

oscarooko SSC-Addicted Points: 445 More actions · Answer 7

Lynn Pettis (9/11/2012)
ChrisM@Work (9/11/2012)
Lynn Pettis (9/11/2012)
ChrisM@Work (9/11/2012)
oscarooko (9/11/2012)
I am trying to delete old records from a table, and insert them into another table. Problem is that it is taking too long. The table has about 3million records. Query is running for more than 1 hr.
Any ideas of how to expedite this process?
That's far too long. Can you post the query? The estimated plan would be even better.
Maybe, maybe not. 3 million records, but how big is each record? Is the INSERT and DELETE being done as a single batch? Are there indexes defined on the destination (archive) table?
Pound to a penny it's two-stage with a non-sargable where clause on the delete 😉
Not a bet I will take. Need to see the query.

Yes, I do have indexes in the destination table, and I am deleting in batches.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

oscarooko (9/11/2012)
Lynn Pettis (9/11/2012)
ChrisM@Work (9/11/2012)
Lynn Pettis (9/11/2012)
ChrisM@Work (9/11/2012)
oscarooko (9/11/2012)
I am trying to delete old records from a table, and insert them into another table. Problem is that it is taking too long. The table has about 3million records. Query is running for more than 1 hr.
Any ideas of how to expedite this process?
That's far too long. Can you post the query? The estimated plan would be even better.
Maybe, maybe not. 3 million records, but how big is each record? Is the INSERT and DELETE being done as a single batch? Are there indexes defined on the destination (archive) table?
Pound to a penny it's two-stage with a non-sargable where clause on the delete 😉
Not a bet I will take. Need to see the query.
Yes, I do have indexes in the destination table, and I am deleting in batches.

Can you confirm that you are using SQL Server 2012?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden