ERP and databases
Slimming down
and speeding up databases
Your sales system's database holds documents from every year of trading, including those that daily work no longer needs. Moving them to an archive shortens reports, backups and month-end closing, and the documents can still be found.
Where the problem comes from
Warehouse and sales systems record every document and do not automatically delete those from years ago. Over time the operational tables come to hold the company's entire history, although day-to-day work needs only the most recent part.
The effects show up in many places: reports take longer and longer to generate, backups take hours, stocktaking blocks work for half a day and the document list opens with a delay. Companies often take this as a sign of an old server and plan to replace the hardware, although the cause lies in the data.
The second mechanism is fragmentation. Tables grow, indexes become fragmented and the statistics describe the state of years ago. The database engine then picks poor query execution plans, and an operation that once took seconds starts taking minutes.
Archiving old documents
Slimming down does not mean deleting. We move old documents to a separate archive database, which remains accessible but does not slow down daily work. We agree how the archive will be accessed before work begins.
Where the archiving cut-off falls depends on how long documents must remain available in the system and on record retention requirements, so we set it together with the company and its accounting team.
Before we change anything in production, we run the whole operation on a copy and check that the system works correctly on the archived data.
- Analysis of which tables grow fastest and what fills them
- Setting the archiving cut-off together with the accounting team
- Moving data to the archive database with relationships preserved
- System tests on the slimmed-down data
- Execution in production during a maintenance window, with a rollback plan
- Recurring archiving, so the problem does not come back next year
What changes after slimming down
A shorter backup can be run more often, so in the event of a failure the company loses less data. Reports and summaries generate faster, and month-end closing puts less strain on the accounting team.
A smaller database also fits more easily in the server's memory. The system then reads data from disk less often, and reading from memory is much faster.
| Operation | What makes it slow | What slimming down gives you |
|---|---|---|
| Full backup | the size of the whole database with its history | less data to copy, backups can be run more often |
| Month-end closing | summaries calculated on large tables | fewer records to go through |
| Annual report | searching tables that span many years | smaller tables and tidy indexes |
| Stocktaking | recalculating stock levels on a large database | shorter recalculations and a shorter work stoppage |
Indexes and queries
The second step after archiving is tidying up the indexes: rebuilding fragmented ones, removing unused ones and adding missing ones where the system regularly scans entire tables.
Too many indexes also come at a cost, because each one has to be updated whenever data is written. In databases that have passed through the hands of several companies, we find indexes that duplicate one another and ones the system has not used for months.
Finally there are the queries. Those in a closed system often cannot be changed, but the right index or up-to-date statistics usually improve how they perform.
When it is worth dealing with this
It is better to deal with the database before the system grinds to a halt in the middle of the season. Warning signs usually appear earlier:
- The backup takes longer than the overnight window
- Reports that used to generate instantly take longer and longer
- Month-end closing takes more and more days
- The database grows faster than the company
- Low disk space warnings appear
- The company is considering replacing the server because the system has slowed down
What we do not touch
We do not change the ERP system's table structure or its code. This limits the risk of conflicts with the vendor's future updates.
We work at the level of the database and its configuration: indexes, statistics, the maintenance plan and data archiving.
If it turns out that the problem lies in the application itself, for example in a report once written to order, we say so and point out who should deal with it, because changes to someone else's undocumented code can cause a system failure.
What the cost depends on
Analysis and an initial assessment usually take 1–2 working days. The cost of the slimming down itself depends on the amount of data to move, the complexity of the relationships between tables and the number of stages the work has to be split into.
We give a quote for the work after the analysis, because only then do we know what we are dealing with. The analysis has a fixed price and does not commit you to further cooperation. Its result is a report with figures: how big the database is, what fills it and how much can be gained.
Outside the season and before deciding to replace the server. Slimming down the database changes the hardware requirements, so it is better to plan hardware purchases after it.
Working together
How we work together, step by step
Analysis
We check table sizes, growth rate and which operations take longest.
Plan and tests
We set the archiving cut-off, run the operation on a copy and test the system.
Implementation
Archiving and optimisation in production, during a maintenance window, with a rollback plan.
Cycle
We set up recurring archiving, so you do not have to start from scratch next year.
Questions
Frequently asked questions
No. The data goes to the archive database and remains accessible; it simply stops weighing down daily work.
Analysis and testing usually take a few days. Execution in production on simpler systems takes one maintenance window, usually overnight or at the weekend, while on more complex ones we split the work into several stages.
Before and after the work we measure the database size, the time of a full backup and the time of the longest operations, such as reports and month-end closing. The results are in the report, so you can see what changed after slimming down.
Sometimes, yes. It is best to start with slimming down and check the results. If the system is still too slow afterwards, a conversation about hardware makes sense.
Yes. We run the operation on a copy first and test the system before making the change in production. We write more about working with this program under InsERT software support.
Usually once a year, after the financial year is closed. With a large number of documents it is worth setting up a quarterly cycle, so that the database does not grow to a size at which the operation becomes risky.
Related services
What else may be useful
Your city
Database optimisation in your city
For each of these cities we have a separate page describing the local market and how we work with companies there.
Let's check how much you can gain without replacing hardware
Tell us which system you use and how large the database is. We will send back an initial assessment and the estimated effect of archiving.