Best way to archive sql data and show in web application whenever required

Question

I have around 10 tables containing millions of rows. Now I want to archive 40% of data due to size and performance problem.

What would be best way to archive the old data and let the web application run? And in the near future if I need to show up the old data along with existing.

Thanks in advance.

Answer 1

There is no single solution for any case. It depends much on your data structure and application requirements. Most general cases seemed to be as follows:

1. If your application can't be redesigned and instant access is required to all your data, you need to use more powerful hardware/software solution.
2. If your application can't be redesigned but some of your data could be count as obsolete because it's requested relatively rearely you can split data and configure two applications to access different data.
3. If your application can't be redesigned but some of your data could be count as insensitive and could be minimized (consolidated, packed, etc.) you can perform some data transformation as well as keeping full data in another place for special requests.
4. If it's possible to redesign your application there are many ways to solve the problem.In general you will implement some kind of archive subsystem and in general it's complex problem especially if not only your data changes in time but data structure changes too.
5. If it's possible to redesign your application you can optimize you data structure using new supporting tables, indexes and other database objects and algorythms.