Archiving Large table

Hi,

I am having problems with archiving. Any help will be greatly appreciated.

I have a table with 12 million records, and it needs to be archived to another table.

Since there is a primary key(let's say, SessionID), so I tried to copy 1000 records at a time, and delete those records afterwards.

Problem is, it used to take 1 second to process 1000 records. Now, it takes anywhere from 2 minutes to 14 minutes!!!

Does anyone have a better idea of doing this? I am really stuck...RE: I am having problems with archiving. Any help will be greatly appreciated. I have a table with 12 million records, and it needs to be archived to another table. Since there is a primary key(let's say, SessionID), so I tried to copy 1000 records at a time, and delete those records afterwards. Problem is, it used to take 1 second to process 1000 records. Now, it takes anywhere from 2 minutes to 14 minutes!!! Does anyone have a better idea of doing this? I am really stuck...

Q1 [It used to take 1 second to process 1000 records. Now, it takes 2 minutes to 14 minutes. Why?]

A1 There may be many different issues, (insufficient information to suggest a reasonably good guess and / or answer).

Q2 [Does anyone have a better, i.e.(FASTER?) idea of doing this?]

A2 Archiving may be accomplished efficiently. What is "Better" really depends on existing overall constraints and designs, available resources, and the details of the circumstances. An example that should be fairly quick (but not overly 'user friendly') would be Archiving a Test table in a Demo DB to an ArchiveDB database table named ArchiveTest.

Demo..Test To ArchiveDB..ArchiveTest

Use Demo
Go

INSERT INTO
[ArchiveDB].[dbo].[ArchiveTest]
([Parent], [Child])
SELECT
[Parent], [Child]
FROM
[Demo].[dbo].[Test]
GO

Alter Database Demo
Set Restricted_User
With
RollBack Immediate
Go

Alter Database Demo
Set Single_User
With
RollBack Immediate
Go

Alter Database Demo
Set Recovery Simple
With
RollBack Immediate
Go

-- drop and recreate, or truncate, delete, etc.
Drop TABLE [Test]
Go
CREATE TABLE [Test] (
[Parent] [varchar] (50) NOT NULL ,
[Child] [varchar] (50) NOT NULL)

Alter Database Demo
Set Recovery Full
With
RollBack Immediate
Go

Alter Database Demo
Set Multi_User
With
RollBack Immediate
Go|||how many indexs do you have on this table? Are any of them clustered?

I would suggest
1. copying all data to your archive table
2. script out all indexes and then drop them
3. build one non clustered index that would allow you to join to the archive table.
4. begin a transaction, delete a few thousand records, commit the transaction.
5. adjust the number of deleted records for best performance
6. restore indexes from step 2.|||Originally posted by Paul Young
how many indexs do you have on this table? Are any of them clustered?

I would suggest
1. copying all data to your archive table
2. script out all indexes and then drop them
3. build one non clustered index that would allow you to join to the archive table.
4. begin a transaction, delete a few thousand records, commit the transaction.
5. adjust the number of deleted records for best performance
6. restore indexes from step 2.

Thank you for your replies,

Actually, there is only primary index with identity on. That's it.
The only problem is that, this table should be on-line all the time. i cannot restrict the access to this table.

Somehow, the records don't seem to be sorted at all when I open the table. I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse. If I open the table again, it is again a mess. I don't see sorted order in this table.

Once it is properly sorted, the performance is great. What can I do to keep the old record + new records sorted at all times? I cannot manually sort the table, and this process hurts the server badly.|||RE: Thank you for your replies, Actually, there is only primary index with identity on. That's it. The only problem is that, this table should be on-line all the time. i cannot restrict the access to this table. Somehow, the records don't seem to be sorted at all when I open the table. I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse. If I open the table again, it is again a mess. I don't see sorted order in this table. Once it is properly sorted, the performance is great. What can I do to keep the old record + new records sorted at all times? I cannot manually sort the table, and this process hurts the server badly.

Q1 [I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse.]
A1 You are probably not updating your indexes at a suitable interval (to ensure optimal performance).

Q2 What can I do to keep the old record + new records sorted at all times?
A2 Cluster both TABLES on the desired column.|||my first inclination is that you have a corrupted index. the overall sorting should not change (aside from changes in data) due to inserting, updateing or deleting data.

During a one week prieod I rebuilt all my index once if not twice on very dynamic tables. Are you doing this?

If your primary index is clustered, you are reordering some part of your data everytime you insert, update or delete. This can lead to slow performance at times. If you must have this index then you just live with it, if you don't need it then change to non-clustered.|||Thank you, Paul Young.

Actually, I never rebuilt indexes on any of the tables. My bad...

What do I have to do to rebuild indexes? I tried DBCC DBREINDEX, and it didn't improve the performance of the archiving.

Can you guide me step-by-step what has to be done?

Thank you again.|||Generally I use maintinance plans to rebuild indexes and statistics along with other things however to answer your question DBCC DBREINDEX will do the trick.

Even if you haven't EVER rebuilt your index(s) they still should produce a result set correctly sorted. Again My hunch is that you have a corrupt index. To fix this you will need to drop the index an re-create it. You can do this while other are using the system but I would NOT advise it.|||One more thing, once you rebuild your index you probably want to update statistics so the correct optimization plans will be used.|||Thank you, Paul.

I tried to rebuild the index using 'DROP EXISTING'. It took about 5 minutes, and I opened the table, and it still looks messy.

But now, the index seems to be functioning faster. The problem is that, I don't use the primary key as query condition. Usually, my query condition is the 'CreationTime' which gets filled with default values getdate().

Basically, I query all the data created during a time period.

Should I create another index on CreationTime?

Thank you,|||If this table is used in an OLTP environment you want to keep the number of indexes to a minimum because evryting you inset/update/delete a row you also have to update ALL indexes. In your case you only have one index so adding one more shouldn't cause you a noticable slowdown and will GREATLY improve the prformance of your select.

before adding any indexes drop your select statement into Query Analyzer, turn on Show Execution Plan, Show Server Trace and Show Client Statistics and execute your select. Look at the "Execution Plan" tab and you will get a diagram representing what your select is doing.

Next click on Index Tunning Wizard and let SQL server suggest indexes to be built. Concider the suggestions and implament whatever you tinks looks good. Now rerun your select and look at the differences on the "Execution Plan" tab.

All of this is covered in Books Online, an excelent source of info once you know what to look up.|||Q1 Usually, my query condition is the 'CreationTime' Should I create another index on CreationTime?

A1 If you are looking for good performance, Yes. Generally, one wants a (well maintained) index available for the query parser to take advantage of for any column that is frequently queried.