An example of where the time can go
Imagine an internal application that has a page showing a list of customer orders. The page worked well when it was first built, but over time the business has added more orders and people have started to notice that it takes longer to load. During the busiest part of the day, some users refresh the page because they are not sure whether it is still working.
The team considers adding another application server. First, they look at the time spent on a slow request. As an example, let's say it breaks down like this:
- Total response time: 1,800 milliseconds, or 1.8 seconds.
- Database query: 1,400 milliseconds.
- Application processing: 250 milliseconds.
- Other overhead: 150 milliseconds.
Most of the wait is in the database query. Another application server could handle more requests at once, but it would not make that query finish sooner. It might even allow more requests to reach the database while it is already busy.
The team inspects the query timings and its execution plan, which shows how the database intends to find the requested data. They find that the query fetches far more orders than the page needs, and the application then sorts and filters them before showing the first page of results.
The team tests a revised query that lets the database handle the filtering, sorting and pagination, returning only the orders needed for the page. They also check whether an index would help with the filters and ordering. Using representative data and realistic traffic, they compare response times and database load with the original query, while checking that other operations aren't affected. They can then decide whether to release the change and keep an eye on those measurements afterwards.
The right database optimisation depends on the application. An index can speed up a query, but it takes up storage and needs maintaining when data changes. Pagination can reduce the amount of data returned, although the team still needs to make sure it behaves sensibly as records are added or updated. Testing the effects on other important work helps avoid improving one page at the expense of another.