Picture a spare parts wholesaler. Four years ago they had a web application built for orders and customer accounts. In the first year everyone is happy: screens open instantly. Today the order list in that same application arrives in fifteen seconds, the sales team cannot enter orders while the month-end report is running, and the customer search freezes the screen with every letter typed. Nobody has touched the code. The only thing that changed is the number of rows in the tables: from a few thousand orders to a few hundred thousand.
The first fix that comes to mind here is a more powerful server. Sometimes it helps, but most of the time it postpones the problem by a few months and raises the monthly bill for good. The cause of the slowdown is usually not hardware; it is a handful of habits in the software that cause no trouble with little data and grow heavy with a lot of it. The seven points below are things you can ask your developer about, in order.
1. Measure first: which screen, how long, for whom?
"The system is slow" is a complaint, not a job description. Before any fixing starts, put three things in writing: which screens are slow, how many seconds they take, and whether it happens to everyone or only to certain users.
Ask users to note the screens they find slow over one week. Have the developer pull the longest-running requests and queries from the server logs as well. The two lists usually point at the same three or four screens. Most of the slowness comes from a few spots; there is no need to go through everything at once.
You will need this measurement at the end of the work too. The answer to "is it faster?" should be the seconds before and after, not a feeling.
2. Do lists fetch everything at once?
This is the most common cause. When the order list screen was first built, fetching all orders and laying them out on the screen was easy; with two hundred rows nobody noticed. With two hundred thousand rows the same screen tries to pull records the user will never look at out of the database and carry them to the browser.
The fix is pagination: on first load the screen fetches only the latest fifty records, and the rest arrive as the user asks for them. A default date range does the same job; most users look at this month's orders anyway.
The same problem shows up in dropdowns. A select box containing every customer slows down the opening of every form as the customer count grows. A box that searches as you type removes that load.
3. Is there an index for the searches and filters?
A database index is like the subject index at the back of a book. Without one, the database reads every row of the table from start to finish to find the order you are looking for. While the table is small this goes unnoticed; as it grows, every search takes a little longer.
In a first version, indexes usually go only on the most obvious fields. Filters added later (by region, by rep, by status) are often left without one. The list of slow queries from point one shows directly which fields need an index.
This is the fix that makes the biggest difference for the least effort. That said, putting an index on every field is not right either; each index makes adding records slightly slower. The decision is made by looking at which fields are actually searched.
4. How many queries run when one screen opens?
If the order list shows the customer name, the rep name and the last payment date next to each row, the software can fetch these in two ways: all in one go, or separately for each row. On the second path a fifty-row list means a hundred and fifty separate queries.
During development, working with a handful of records, the difference is invisible. In production, even if each query is short, the total adds up to seconds. The fix is to fetch the related data in bulk with a single query; the screen looks the same, only the work behind it shrinks.
The simple question to ask your developer is: "How many times do we go to the database when this screen opens?" If the answer is in the hundreds, the cause has been found.
5. Do reports run on top of the live system?
A month-end report adds up and groups millions of rows. If that work is done on the database where orders are entered, and during working hours, everyone waits until the report finishes. The wholesaler's complaint that "orders can't be entered while the report runs" is exactly this.
There are three routes, and they do not exclude each other. Heavy reports can run at night with the result stored; whoever opens it in the morning sees the ready result. Totals that are looked at often (daily revenue, sales by rep) can be kept in summary tables instead of being calculated from scratch every time. If the data is truly large, reports are fed from a read-only copy of the live database.
You are the one who knows how fresh each report needs to be. There is no point in straining the live system for a report that is fine with yesterday's data.
6. Is old data still inside every query?
Closed orders from four years ago do not have to be part of today's order list. But in most systems they sit in the same table, and every search scans them too.
Archiving does not mean deleting data. Closed records older than a set period are moved to a separate table; daily screens work with current data only, and when old records are needed they are reached through a "search the archive" option. The period should be decided together with accounting and your legal retention obligations.
Tables that grow by themselves, such as activity logs, notification history and error logs, are reviewed here as well. These often swell faster than the actual business data.
7. Where do files and images live?
If product photos, scanned delivery notes and PDF quotes are kept inside the database or on the same disk as the application, backups take longer and pages get heavier. Showing a photo of several megabytes taken on a phone for every row of the product list locks the screen for a user on a slow connection.
Keeping files in a separate storage area and using reduced images in lists is enough. The original photo opens when the user clicks on it.
When is the server truly not enough?
If the slowness continues after the points above are fixed, and measurements show the server's memory or processor running at the limit all day, growing the hardware is the right decision. The order matters: first reduce the unnecessary work the software does, then buy the hardware the remaining work needs. Done the other way round, the same unnecessary work keeps running on a more expensive machine.
In short
- Write down the slow screens and their timings; measure before and after the fix.
- Have lists arrive page by page, with a default date range.
- Have someone check whether searched and filtered fields have an index.
- Ask how many queries a screen runs when it opens.
- Run heavy reports at night, and feed them from summary tables or a read replica.
- Move closed old records to an archive; do not delete them.
- Store files separately and use small images in lists.
Most of these seven points do not require rewriting the software; a few weeks of targeted work resolves them. The good news is that data growth is predictable. Taking the same measurement once a year is enough to catch the slowdown before users complain.
In our software maintenance and support service we measure slow pages, queries and screens, then find and remove their bottlenecks.
Our work on this: Custom Software Development
Planning a project like this?
Plan it in 10 steps