I have large OLTP ERP database with 10 million records in few tables. The database size is as large as 10GB. I have few critical transaction tables like this:
1. Payments
2. Cheques etc.,
and each of these tables have more than 30 columns and at least 10 million records each.

I have identity column as the primary key in all these tables and each of the tables is having the transaction date (which is as well non cluster indexed). Recently I found out that my INSERT, UPDATE and DELETE's on these tables is taking considerable time. The users are interested in accessing last 3 years of data (most likely, except for reporting). I would like to optimize this and came up with these two approaches:
1. Archiving all records and moving them to a new database for those records, which are more than 3 years old.
2. Creating staged tables for each of the years and moving the data into them and doing CRUD operations on an indexed view (with union of all the staged tables).

Since I'm not using SQL Server Enterprise I cannot create partitioned tables.

I'm thinking that the second method is appropriate and suitable than the first one since the maintenance is easy. Kindly suggest me if I'm correct. Also give your kind inputs on any other ideas.

Thanks in advance.

2 Answers 2


Use an archive table and a live table.

Don't use a "table per year": this quickly becomes messy and you'll end up needing dynamic SQL when you UNION + view becomes a bottleneck

10 million rows isn't a lot though, and 30 columns is quite wide. So, some options:

  • review data types to reduce data in disk.
    • Do you need nvarchar?
    • Can you use smalldatetime?
    • Do you have int where tinyint will do?
    • (n)varchar when you have fixed length data?
  • do you have regular index and statistics maintenance?
  • do you have incorrect or missing indexes?
  • have you looked at most expensive queries?
  • Thanks @gbn. You mean to say that I should have only two tables - live and archive and push all the archived data to archive table?
    – Nagesh
    Jan 31, 2012 at 8:59
  • 1
    @Nagesh: yes, but I'd also consider other things too as I mentioned. The difference between 1 million rows in the live table (9m archived) or 10 million rows in a single table isn't enough to give magical performance improvements
    – gbn
    Jan 31, 2012 at 9:12

You say inserts/updates/deletes are taking alot of time? Do you have any triggers on the tables in question? 10 million records is a tiny, tiny table and SQL server should be able to handle it easily. I don't think archiving is going to help with inserts/updates/deletes.

You may also be having trouble with updates and deletes if you are using cascading updates or deletes. You could be changing/delete thousands of records for every change or delete in the parent table.

Additionally if you have unnecessary indexes that will cause extra work.

Addtionally are you doing row-by-row work when you need to be doing set-based work? This could include using cursors or while loops, using correlated sub-queries and using scalar functions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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