Slowness inside a company’s own system is the least-reported problem there is. Nobody opens a support ticket that says “this takes eleven seconds”. They sigh, they wait, they switch to another tab, and they absorb it. Multiply by every member of staff and every working day, and it is one of the more expensive things a business quietly pays for.
This system had it everywhere, for one reason, repeated in six places.
What the software was doing
Almost every screen a member of staff uses is a list of their own things. Their clients. Their tasks. Their bookings. The software does that by storing, on each record, who is responsible for it, and then asking the database for the records belonging to the person who is logged in.
A database can answer that question in two ways. If it has been told to keep a quick lookup for that column, it goes straight to the matching rows. If it has not, it reads the entire table from beginning to end and checks each row as it goes.
Nobody had told it. On six of the busiest tables in the system — the ones behind the screens people open all day — there was no quick lookup on that column. So every one of those screens read the whole table, every time it was opened, to show twenty or thirty rows.
On the copy we measured, that meant reading 100,000 rows to display a page of work, and doing it again the moment somebody changed a filter. In the live system those tables are larger.
The one that was worse
One list did something else on top.
Next to each row it showed a number: how many events were attached to that record. The software worked that number out by counting, in a table holding 829,000 events — and it did that separately for every row on the page. Thirty rows on screen meant thirty full passes over 829,000 rows, on top of the scan that produced the rows in the first place.
That is not a mistake anyone made deliberately. It is what you get when a page grows one column at a time over several years, each addition looking harmless on its own, and nobody ever opens the page as a whole and asks what it now costs to draw.
There was a third, smaller version of the same thing in the search box. Searching a name was written in a way that can never use a shortcut, no matter what the database has been told to keep — so name search read the whole table too.
What we changed
Nothing anyone can see. That is worth saying plainly, because it is the reason this work gets deferred: it produces no new features and no visible difference except that things stop taking as long.
We added the quick lookups for the columns the lists actually filter and sort by. We replaced the per-row counting with a single grouped lookup that answers the whole page at once. And we left the search alone but wrote down exactly why it is slow and what the two options are, because that one is a product decision — either searches match from the beginning of a name, which is fast, or they match anywhere in it, which needs a different kind of index and a real conversation about whether it is worth it.
The order matters here. We measured each screen first, on a copy of the real data rather than a small test set, because with a few hundred rows every version of this code looks instant. Then we applied the changes to the copy and measured the same screens again. Only then did they go to the live system, and the larger ones went in with a method that does not lock the table while it works — you do not want the fix for a slow system to be the thing that stops it during business hours.
The jobs that ran overnight
The same pattern existed away from the screens, where it was more dangerous.
Several routines run every night to keep totals and states up to date. One of them loaded every record in a set into memory before doing anything, which on the current volume of data needed about 16 gigabytes and simply died on anything smaller. Another issued around 1,400 separate small queries in a loop and held the results in memory while it worked.
Those were rewritten to work in batches and to let the database do the grouping, which is what a database is for. The output was compared against the old version’s output and matched exactly, which is the only acceptable proof for a change like this — the whole point is that nobody notices, and “nobody complained” is not evidence.
What we would do differently
One of those overnight routines is still switched off. It behaves differently on the new hosting than on a local copy and needs roughly eight gigabytes to complete, and rather than guess we left it off and wrote down what it needs: its own scheduled run with more memory, separate from everything else. It is the one honestly unfinished item here and it is written into the system’s own notes rather than living in somebody’s head.
We would also have gone looking for the search problem earlier. It was the thing staff mentioned first in conversation, and it was the last thing we looked at, because it was the one where the fix is a decision rather than a change.
If this sounds like your system
Ask for one number: how long does our busiest internal screen take to open, measured, with real data on it?
If nobody knows, that is normal. If the answer is more than two or three seconds, it is almost never the hosting, and it is almost never something that needs a rebuild. It is usually four or five specific pieces of work like the ones above, and knowing which ones takes a couple of days.
What that costs is published here, along with everything else.