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

Gordon,

A few questions that could affect your performance.
1 - Do you have indexes to support the TOP 5000 select? If not, create an
index to support the select and delete.
2 - Are your statistics up-to-date? If not you should update those?
(sp_updatestats)
3 - Is your data becoming fragmented as you delete rows? If so you should
defragment the table?
(SS2000 & 2005 DBCC DBREINDEX, SS2005 ALTER INDEX)

Another observation is that your TOP 5000 (in the sample) does not have an
ORDER BY. Therefore, the 5000 rows being deleted are an undefined selection
from the qualifying rows. TOP makes much more sense with an ORDER BY.

How are you deleting the rows? If you are deleting by joining
gbdb_arch..tests_to_archive to gbdb..tests you join set it getting bigger
and bigger as you build up the archive table. For example
DELETE ts
FROM gbdb..tests ts JOIN gbdb_arch..tests_to_archive ar
ON ts.test_id = ar.test_id
If this is the problem, then you might need an index on tests_to_archive to
support the join.

RLF


"Gordon" <Gordon@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:30051553-ADFA-456C-9D77-D89F0AD0A406@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: ADSI script implement in C#
    ... I'm not quite sure about a pure LDAP syntax query since AD is not my ... Microsoft MSDN Online Support Lead ... I'm not quite good at ADSI, however, based on my research, the problem ... DirectoryEntry de = new DirectoryEntry; ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Extending SQL LINQ Table Class
    ... extended properties work fine so long as you don't try to use them in a query. ... It seems the custom property will not be ... Microsoft MSDN Online Support Lead ... Extending SQL LINQ Table Class ...
    (microsoft.public.dotnet.framework)
  • RE: read sql record into memory, use/modify fields then save changes
    ... As for the data query & updating scenario you mentioned, ... Microsoft MSDN Online Support Lead ... where an initial response from the community or a Microsoft Support ... How is the best way to return a single record via sql query that will ...
    (microsoft.public.dotnet.languages.vb)
  • RE: WaitForChildren Process Error question
    ... Product Support Services. ... recommend that you open a Support incident with Microsoft Product Support ... hint that instructs that specific query to only use 1 processor. ... the UNION. ...
    (microsoft.public.sqlserver.server)
  • RE: Detecting empty CheckBoxList without data
    ... Is this issue and continous one from the previous checkbox list query one? ... code after check whether the returned DataTable is empty. ... Microsoft MSDN Online Support Lead ...
    (microsoft.public.dotnet.framework.aspnet)