Databases
StackOverflow2013
Postsdbo
POEfficient SQL Server stored procedure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEfficient SQL Server stored procedure
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USMatan L
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POEfficient SQL Server stored procedure
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POEfficient SQL Server stored procedure
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POEfficient SQL Server stored procedure
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHmmm... this seems like a very procedural approach to what should be a set-based problem. You need to stop thinking in terms of row-by-row, and instead make use of Sql's ability to work very efficiently against sets of data.
      singulars
      1. POEfficient SQL Server stored procedure
      1. USMichael Fredrickson
    2. CODo you have any triggers on the tables involved? If you do, these triggers could be making everything take much longer.
      singulars
      1. POEfficient SQL Server stored procedure
      1. USDaniel Allen Langdon
    3. COIt looks like the only thing that is forcing you to do this [row-by-agonizing-row](https://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/) is the `DeleteByID` stored procedure... could you include this stored procedure's definition, so it could then be incorporated into a set-based solution?
      singulars
      1. POEfficient SQL Server stored procedure
      1. USMichael Fredrickson
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload