title: Best practice for a SQL Archiving Stored Procedure
body: <p>I have a very large database (~100Gb) primarily consisting of two tables I want to reduce in size (both of which have approx. 50 million records). I have an archive DB set up on the same server with these two tables, using the same schema. I'm trying to determine the best conceptual way of going about removing the rows from the live db and inserting them in the archive DB. In pseudocode this is what I'm doing now:</p>
  
  <pre><code>Declare @NextIDs Table(UniqueID)
  Declare @twoYearsAgo = two years from today's date
  
  Insert into @NextIDs 
       SELECT top 100 from myLargeTable Where myLargeTable.actionDate &lt; twoYearsAgo
  
  Insert into myArchiveTable
  &lt;fields&gt;
  SELECT &lt;fields&gt; 
  FROM myLargeTable INNER JOIN @NextIDs on myLargeTable.UniqueID = @NextIDs.UniqueID
  
  DELETE MyLargeTable
  FROM MyLargeTable INNER JOIN @NextIDs on myLargeTable.UniqueID = @NextIDs.UniqueID
  </code></pre>
  
  <p>Right now this takes a horrifically slow 7 minutes to complete 1000 records. I've tested the Delete and the Insert, both taking approx. 3.5 minutes to complete, so its not necessarily one is drastically more inefficient than the other. Can anyone point out some optimization ideas in this? </p>
  
  <p>Thanks!</p>
  
  <p>This is SQL Server 2000.</p>
  
  <p>Edit: On the large table there is a clustered index on the ActionDate field. There are two other indexes, but neither are referenced in any of the queries. The Archive table has no indexes. On my test server, this is the only query hitting the SQL Server, so it should have plenty of processing power. </p>
  
  <p>Code (this does a loop in batches of 1000 records at a time):</p>
  
  <pre><code> DECLARE @NextIDs TABLE(UniqueID int primary key)
  DECLARE @TwoYearsAgo datetime
  SELECT @TwoYearsAgo = DATEADD(d, (-2 * 365), GetDate())
  
  WHILE EXISTS(SELECT TOP 1 UserName FROM [ISAdminDB].[dbo].[UserUnitAudit] WHERE [ActionDateTime] &lt; @TwoYearsAgo)
  BEGIN
  
  BEGIN TRAN
  
  --get all records to be archived
  INSERT INTO @NextIDs(UniqueID)
          SELECT TOP 1000 UniqueID FROM [ISAdminDB].[dbo].[UserUnitAudit] WHERE [UserUnitAudit].[ActionDateTime] &lt; @TwoYearsAgo
  
  --insert into archive table
  INSERT INTO [ISArchive].[dbo].[userunitaudit] 
  (&lt;Fields&gt;)
  SELECT  &lt;Fields&gt;
  FROM  [ISAdminDB].[dbo].[UserUnitAudit] AS a
          INNER JOIN @NextIDs AS b ON a.UniqueID = b.UniqueID
  
  --remove from Admin DB
  DELETE [ISAdminDB].[dbo].[UserUnitAudit] 
  FROM  [ISAdminDB].[dbo].[UserUnitAudit] AS a
  INNER JOIN @NextIDs AS b ON a.UniqueID = b.UniqueID 
  
  DELETE FROM @NextIDs
  
  COMMIT
  
  END
  </code></pre>