title: Efficient SQL Server stored procedure
body: <p>I am using SQL Server 2008 and running the following stored procedure that needs to "clean" a 70 mill table from about 50 mill rows to another table, the <code>id_col</code> is <code>integer</code> (primary identity key)</p>
  
  <p>According to the last running I made it is working good but it is expected to last for about 200 days:</p>
  
  <pre><code>SET NOCOUNT ON
  
      -- define the last ID handled
      DECLARE @LastID integer
      SET @LastID = 0
      declare @tempDate datetime
      set @tempDate = dateadd(dd,-20,getdate())
      -- define the ID to be handled now
      DECLARE @IDToHandle integer
      DECLARE @iCounter integer
      DECLARE @watch1 nvarchar(50)
      DECLARE @watch2 nvarchar(50)
      set @iCounter = 0
      -- select the next  to handle    
      SELECT TOP 1 @IDToHandle = id_col
      FROM MAIN_TABLE
      WHERE id_col&gt; @LastID and DATEDIFF(DD,someDateCol,otherDateCol) &lt; 1
          and datediff(dd,someDateCol,@tempDate) &gt; 0 and (some_other_int_col = 1745 or some_other_int_col = 1548 or some_other_int_col = 4785)
      ORDER BY id_col
  
      -- as long as we have s......    
      WHILE @IDToHandle IS NOT NULL
      BEGIN
          IF ((select count(1) from SOME_OTHER_TABLE_THAT_CONTAINS_20k_ROWS where some_int_col = @IDToHandle) = 0 and (select count(1) from A_70k_rows_table where some_int_col =@IDToHandle )=0)
          BEGIN
              INSERT INTO SECONDERY_TABLE
              SELECT col1,col2,col3.....
              FROM MAIN_TABLE WHERE id_col = @IDToHandle
  
              EXEC    [dbo].[DeleteByID] @ID = @IDToHandle --deletes the row from 2 other tables that is related to the MAIN_TABLE and than from the MAIN_TABLE
              set @iCounter = @iCounter +1
          END
          IF (@iCounter % 1000 = 0)
          begin
              set @watch1 = 'iCounter - ' + CAST(@iCounter AS VARCHAR)
              set @watch2 = 'IDToHandle - '+ CAST(@IDToHandle AS VARCHAR)
              raiserror ( @watch1, 10,1) with nowait
              raiserror (@watch2, 10,1) with nowait
          end
          -- set the last  handled to the one we just handled
          SET @LastID = @IDToHandle
          SET @IDToHandle = NULL
  
          -- select the next  to handle    
          SELECT TOP 1 @IDToHandle = id_col
          FROM MAIN_TABLE
          WHERE id_col&gt; @LastID and DATEDIFF(DD,someDateCol,otherDateCol) &lt; 1
              and datediff(dd,someDateCol,@tempDate) &gt; 0 and (some_other_int_col = 1745 or some_other_int_col = 1548 or some_other_int_col = 4785)
          ORDER BY id_col
      END
  </code></pre>
  
  <p>Any ideas or directions to improve this procedure run-time will be welcomed </p>