Re: Query for only specific rows in a table? Need Help with Query

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

Hi Gordon,

In addition to Russell's reply, some more points.

1. What version of SQL Server are you using? SQL 2005 has a new option
(the OUTPUT option) that you can leverage for a tremendous performance
boost.

2. Why are you using (NOLOCK). Are you aware of the risks of reading
dirty data, missing rows, or reading rows twice? Will you really risk
archiving dirty data for a performance gain?

3. I assume that the stored proc bow_ArchiveHistoricalTestData does the
actual delete. That means that copying to archive and purging the
original are not only in seperate transactions; they are even in
seperate batches. You run the risk that the insert succeeds, but the
delete fails - and you even run the risk that the insert fails and the
delete succeeds, causing you to lose data permanently!!

4. I agree with Russell that the real problem is probably in the
bow_ArchiveHistoricalTestData procedure. Can you please post that code?

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
.

Relevant Pages

  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Indexing Service, Openquery and sp_executesql
    ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem using Access or Query Designer to run queries in SQL Serve
    ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
    (microsoft.public.sqlserver.odbc)
  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... This process runs very quickly if run through Query ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)