Your month-end report takes 30 minutes and slows everyone down? Why it happens and how to fix it

The last working day of the month. At eight in the morning finance starts the monthly sales report. Five minutes later the warehouse can’t print delivery notes, and sales call IT to say the system is down. Nobody knows what is going on, so someone in finance clicks “Generate” again. Now two reports are running.

This scenario is invented, but almost every company with its own business application will recognise it. And it usually isn’t a problem with the server. It is a problem with the way reports are built.

1. 08:00Finance starts the monthly report
2. 08:05The warehouse can't print delivery notes
3. 08:20Sales report that the system is down
4. 08:25Someone runs the report again
5. 08:40The report times out, and it starts over

Why one report stops everyone

Imagine a shop that closes for a stocktake, while customers are still queuing at the till. Business applications do that all the time:

• Reports and daily work use the same database. The report reads millions of rows, and the same disk, memory and processor also have to serve the warehouse, sales and everyone else.
• Reading holds up writing. On SQL Server, by default a long read can hold up anyone who wants to change the same data, and the other way round. The report waits for the warehouse, the warehouse waits for the report.
• Everything is calculated from scratch. To get last month’s total, the report adds up five years of invoices every time. While there was little data, nobody noticed.
• The application pulls everything into memory. Instead of letting the database add up and return a hundred rows, the application loads all the data and adds it up itself.
• The user waits in front of the screen. The report runs while the browser waits. When the browser gives up, the person clicks again, and now the same work is running twice.

What it costs

A simple example with invented, but realistic numbers:

People who work in the application	40
Time lost each time a report slows everything down	30 minutes
How often that happens (month-end, VAT, management meeting, ad-hoc)	4 times a month
Hours lost per month	80
At €30 an hour	€2,400 a month, almost €29,000 a year

That is only what can be counted. On top of it: deliveries that go out late, finance staying in the evening so they don’t bother anyone, and decisions made on old numbers because nobody dares run the report during working hours.

Fixes, from the cheapest up

The good news is that you rarely need a new system. Most of the time the order is the same:

Fix	What it means	Effort
1. The query and indexes	The report asks the database the right way and gets back only what it needs	Days
2. Reading doesn’t block writing	A database setting so the report and the warehouse stop waiting for each other	Hours to days, with testing
3. The report runs in the background	The user clicks, carries on working, and gets an email with a link when the report is ready	Days
4. Pre-calculated totals	Totals per day, customer and item are calculated once, at night	Days to weeks
5. A separate copy for reports	Reports read from a copy of the database, daily work uses the original	Days, depending on the platform

1. The query and indexes. This is almost always the first step, and often the last. A report that reads the whole table because an index is missing, or a report that sends thousands of queries for one page, can drop from half an hour to under a minute. We described the most common causes in the articles on slow applications and EF Core performance.

2. Reading doesn’t block writing. SQL Server has an option (Read Committed Snapshot Isolation) that lets a report read a consistent picture of the data without stopping anyone who writes. In Azure SQL Database it is on by default, and on your own SQL Server it is off. It is one setting, but it changes how the database behaves, so it is tested before it goes live.

3. The report runs in the background. Instead of the person waiting in front of the screen, the report goes into a queue and is generated when there is capacity, one at a time. The person gets an email when it is ready. No more double clicks and timeouts, and the heaviest reports can wait until the evening.

4. Pre-calculated totals. Most reports ask the same thing: how much, by day, customer, item or region. These totals can be calculated once, at night or after each change, and the report then reads a few thousand rows instead of a few million.

5. A separate copy for reports. If you are on Azure SQL Database in the Premium or Business Critical tier, you are already paying for a read-only copy of the database that the application isn’t using. Reports are sent there with one setting in the connection string:

"ConnectionStrings": {
  "App":     "Server=tcp:company.database.windows.net;Database=Erp;...",
  "Reports": "Server=tcp:company.database.windows.net;Database=Erp;ApplicationIntent=ReadOnly;..."
}

Daily work uses App, reports use Reports, and they no longer compete for the same resources. Data on the copy can arrive a moment later than on the original, which doesn’t matter for a monthly report. On your own SQL Server something similar is possible, but it depends on the edition and on licensing, so that gets checked first.

In which order

1. MeasureWhich reports hurt, and why
2. QueriesIndexes and fewer calls
3. BackgroundNobody waits in front of the screen
4. Totals and a copyOnly if still needed

Measurement comes first, as always. It shows which three or four reports cause most of the trouble, and it is often less work than it seemed. Only when the queries are sorted out does it make sense to talk about background processing, pre-calculated totals and a separate database. A new reporting tool or a bigger server comes last, if at all.

How we work

ProCoding is a .NET studio from Split, Croatia. Slow reports and a database that holds everyone up are exactly what we fix: in .NET applications, SQL Server and Azure SQL, from queries and indexes to background processing and read-only copies of the database. More about what we do on Azure is on the page Azure for .NET applications.

We start by measuring your reports, and you get a list of causes and fixes ranked by what pays off most. After that we fix them, or your team does, whichever suits you better. The first step is a free 30-minute call.

Sources

• Read queries on replicas (read scale-out), Microsoft Learn
• SET TRANSACTION ISOLATION LEVEL, Microsoft Learn
• Background tasks with hosted services in ASP.NET Core, Microsoft Learn