"Gordon" <Gordon@discussions.microsoft.com> wrote in message
news:30051553-ADFA-456C-9D77-D89F0AD0A406@microsoft.com...
> We have a stored procedure that removes old data from a very large
> (millions
> of rows) table and puts it into an archive table. It grabs the top 5000
> rows
> and moves them.
>
> B/C the database is in production and is heavily used, the job is run
> every
> 5 minutes
> to avoid performance issues. When first started, the query ran quickly
> b/c
> it would find 5000 rows fast. now that it has been running for a week it
> takes longer for the query to execute and is locking the tables for too
> long.
>
> Is there a way to query only a specific set of rows in the query? We
> could
> store the last known row that was deleted in a temp table and start the
> query
> over from that last known row. The problem is we don't know how to do
> this
> with code in the stored procedure. Our code is below. Can anyone help us
> with this or point us in the right direction to get this accomplished?
>
>
> INSERT INTO gbdb_arch..tests_to_archive
>
> select top 5000 p.test_id from gbdb..tests p (NOLOCK) LEFT OUTER JOIN
> gbdb_arch..tests_to_archive a (NOLOCK) ON
>
> p.test_id = a.test_id where var_id in (select var_id from gbdb..variables
> (NOLOCK) where pu_id <> 0)
>
> AND Result_On < DATEADD(year, -2, getdate()) AND a.test_id IS NULL
>
> go
>
> gbdb_arch..bow_ArchiveHistoricalTestData 5000

On Tue, 9 Oct 2007 08:39:00 -0700, Gordon wrote:

>We have a stored procedure that removes old data from a very large (millions
>of rows) table and puts it into an archive table. It grabs the top 5000 rows
>and moves them.
>
>B/C the database is in production and is heavily used, the job is run every
>5 minutes
>to avoid performance issues. When first started, the query ran quickly b/c
>it would find 5000 rows fast. now that it has been running for a week it
>takes longer for the query to execute and is locking the tables for too long.
>
>Is there a way to query only a specific set of rows in the query? We could
>store the last known row that was deleted in a temp table and start the query
>over from that last known row. The problem is we don't know how to do this
>with code in the stored procedure. Our code is below. Can anyone help us
>with this or point us in the right direction to get this accomplished?
>
>
>INSERT INTO gbdb_arch..tests_to_archive
>
>select top 5000 p.test_id from gbdb..tests p (NOLOCK) LEFT OUTER JOIN
>gbdb_arch..tests_to_archive a (NOLOCK) ON
>
>p.test_id = a.test_id where var_id in (select var_id from gbdb..variables
>(NOLOCK) where pu_id <> 0)
>
>AND Result_On < DATEADD(year, -2, getdate()) AND a.test_id IS NULL
>
>go
>
>gbdb_arch..bow_ArchiveHistoricalTestData 5000