Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2002
    Posts
    11

    Unanswered: 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...

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Exclamation

    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

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Sep 2002
    Posts
    11
    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.

  5. #5
    Join Date
    Oct 2002
    Posts
    369

    Post

    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.

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Sep 2002
    Posts
    11
    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.

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Last edited by Paul Young; 10-29-02 at 14:16.
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    One more thing, once you rebuild your index you probably want to update statistics so the correct optimization plans will be used.
    Paul Young
    (Knowledge is power! Get some!)

  10. #10
    Join Date
    Sep 2002
    Posts
    11
    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,

  11. #11
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

  12. #12
    Join Date
    Oct 2002
    Posts
    369

    Post

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •