The report that worked in March times out in December

The spinning wheel in December

Here's a composite. A small distributor has a custom web app for orders and invoices. In March, the finance lead opens the year-to-date sales report and it loads in a few seconds. In July it takes a bit longer. By December it spins for a minute and shows an error.

She needs it for year-end. So she starts running the report one month at a time and pasting the pieces into a spreadsheet. It works. It also takes an afternoon, and she's now the only person who knows how the year-end numbers were put together.

When she mentions it, the first suggestion is the obvious one: the server must be too small. Let's go through why that's usually the wrong first move, and what to do instead.

The wrong first move: a bigger server

Upgrading the server or the database plan is easy. It's one setting and a slightly bigger bill. Sometimes it even helps for a while.

The problem is what it hides. A report that got slower every month as the year went on isn't suffering from a small server. It's doing more work every month, because there's more data. Doubling the machine might get you through this December. The report keeps doing more work, and next year you're back where you started with a bigger bill. A bigger server buys you until next December. Knowing why buys you the next five.

Two other quick fixes have the same problem. Telling people to run the report at night just moves the wait. Quietly limiting the date range to ninety days makes the error go away and makes the report wrong for exactly the people who need a full year.

What the report is probably doing

Before anyone changes anything, find out what happens when someone clicks the button. Your developer can turn on query logging or use the database's own tools to see which queries run, how many, and how long each takes. It's usually one of a handful of things.

It reads everything, every time. The report adds up every order since the beginning of the year, or since the beginning of time, on every click. In March that was a small pile. In December it's the whole year.

It asks the same question many times. Instead of one query for all customers' totals, the code asks once per customer, or once per order line. With fifty customers nobody notices. With five thousand it's the whole wait.

There's no index for how it filters. If the report filters by date and status, but the database only has a quick way to find orders by number, every run means reading the whole table to find the right rows.

It builds the file while you wait. The export to spreadsheet is generated inside the same request as the click. Big file, long wait, and somewhere along the way a timeout cuts it off.

Often it's two of these together. The point is that each one has a specific fix, and none of them is "more server."

Fixes that last

Generate heavy reports in the background. The click starts a job. The person sees "we're preparing your report" and gets a link, or an email, when it's ready. No timeouts, no spinning, and the server does the work when it can. For year-end reports that people run a few times a year, this alone usually changes everything.

Keep totals ready. If the year-to-date report adds up the same closed months every time, store each month's totals once the month is closed. The report then adds twelve numbers instead of a year of orders. Recalculate a month only when something in it changes, like a late refund.

Add the index the report needs. If the report filters by date and status, the database should have a quick way to find orders by date and status. This is often a small change with a big effect, but test it, because indexes also cost something when data is written.

Ask once, not a thousand times. Rewrite the per-customer or per-line lookups as one query that gets everything it needs. Your developer will know this as fixing an "N plus one" problem.

Set a clear default, not a hidden one. It's fine for the report to open on the current month, as long as the range is visible and a full year is one click away.

We've argued that the admin screens are where your software earns its keep. Reports are the same. They're the screens the people who run the business depend on, and they deserve the same care as anything customers see.

How to know it's fixed, and stays fixed

Run the report on a copy of your data with a full year in it, or more, before calling it done. If you expect the business to grow, run it on a copy with twice the data.

Add a simple alert for slow reports, the same way you'd alert on a failed nightly job. Our note on batch failure alerts has the shape. If a report starts taking much longer than it used to, someone should hear about it in September, not at year-end close.

And check the exports people actually use. If finance has been pasting monthly pieces together, ask what they need and make that one download. Our note on CSV import as a feature makes the same case in the other direction.

Put this into practice before year-end

List the three or four reports your team relies on for year-end, and open each with a full year selected. Write down which are slow.

Ask your developer to log what the slowest one does when it runs: which queries, how many, how long. Decide the fix from that, not from a guess.

Move any report that takes more than a few seconds into a background job with a link when it's ready. Do that first. It's the change that ends the timeouts.

If your web app's reports, exports or admin screens are getting slower as the business grows, that's the kind of web application work we do. Talk to us.

Related work: Howcast.