START WITH A PROBLEM
Slow ERP and back-office reports
The month-end report that takes minutes runs on the same database that runs the business. That is the problem, not the report.
The symptom
Reports that run for minutes. Listing screens that time out once a table passes a few million rows. A rule, usually unwritten, that nobody runs the big one before 6pm. And the symptom people notice last, because it looks unrelated: order entry slows down while the report is running.
The report gets blamed. Someone rewrites the query, adds an index, and it is fast again for a quarter. Then the data grows and the same report is slow again, and the next index does less than the last one did.
Why it happens
One database is serving two workloads that want opposite things. Transactions want small, fast, consistent writes: insert an order, update a status, read one record by its id. Reports want scans, joins and group-bys over everything: every order this month, grouped by region, summed by category. Both run on the same CPU, the same memory and the same locks, so when the month-end report starts scanning, the transactions queue behind it.
Adding indexes helps the first report. It also makes every write slower, because each index has to be updated on every insert. By the fourth or fifth reporting index the database is spending a measurable share of its day maintaining structures that exist only for a report that runs once a week. That is the point at which "add an index" stops being a fix.
The database is not at fault. It is doing the job it was designed for, which is being correct about every transaction. Reporting is a different job.
What changes with an indexed layer
An indexed copy of the reporting data, refreshed on a schedule, takes the second job off the database. The copy is organized for exactly the questions reports ask: filter by period, region and category, then count and total what is left. In an index those counts and totals are facets, returned in one request beside the matching records. Four regions become four counts - North 395 · West 247 · East 168 · South 142 - computed on the filtered set, without a scan.
The database keeps doing transactions. Nothing is written back to it. Freshness becomes a setting rather than a fight: orders refreshed hourly, the product master once a day, each collection on its own schedule. The report says as of 09:00 and is honest about it, which most reporting screens can live with and most order-entry screens never needed.
The month-end report is still the month-end report. It just runs against a copy built for it, in milliseconds, while order entry carries on as if nothing were happening.