0

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
  • 4
    Why do inserts take long? That is what you need to find out first. Is not because size. Commented Jan 5, 2014 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. Commented Jan 5, 2014 at 8:55
  • Have a look on a similar post: dba.stackexchange.com/questions/56162/…
    – bjnr
    Commented Jan 6, 2014 at 14:54
  • @RemusRusanu: when a table becomes "big"? I know it counts the hardware architecture, but as a curiosity...
    – bjnr
    Commented Jan 6, 2014 at 14:56

1 Answer 1

2

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

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