I have a table that stores some audit information and currently has 25 million records. There is a process that inserts to this table every 30 minutes. This is historical data and is not used for any processing.

Since this table already has huge number of records so these 30 minutes inserts are taking considerable long time to complete.

According to me I should archive this huge table and only have a day or so data depending on the business conditions and move rest of the data to a archival table.

One possible strategy that I thought was renaming the existing table to something else and creating a new table with the same name as the old one. Can somebody comment on this strategy or suggest a better solution?

  • 4
    Why do inserts take long? That is what you need to find out first. Is not because size. – Remus Rusanu Jan 5 '14 at 8:33
  • 2
    Could you add a table definition (including indexing) script and the insert script please? I agree with Remus that size shouldn't really matter. – Edward Dortland Jan 5 '14 at 8:55
  • Have a look on a similar post: dba.stackexchange.com/questions/56162/… – Mihai Bejenariu Jan 6 '14 at 14:54
  • @RemusRusanu: when a table becomes "big"? I know it counts the hardware architecture, but as a curiosity... – Mihai Bejenariu Jan 6 '14 at 14:56

Please see this answer: SQL Server database design for "archived but available" data

As Remus comments, you are probably barking up the wrong tree here. 25M rows is a very small table and the INSERT really shouldn't take long. Perhaps it is worth investigating this first..

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.