The Dev World – Sergio Lema

Continuing the pursuit to optimize the response time of an API, in this article I describe how to use pagination to reduce the data handled by the API.

The pagination is simply split the response data into several pages, several blocks of data. Having less data to handle, means faster response times.

But the pagination has some tricks that must be taken into account to fully take advantage of it.

Reproduce It Locally

The first step is always trying to run the application and endpoint locally. I need this to obtain the response time of my local machine. As it’s in my local machine where I will do the modifications.

Reproducing it locally means having a complete dataset. It’s not just a matter of unit tests. It’s reproducing it with real-life conditions.

For some endpoints, I connect directly my local application to a staging database. If that’s not possible (because of security concerns or because the endpoint performs modifications in the database), I make a copy of a database on my laptop.

The goal is to make several tests in my machine to get an average value of the execution times, to compare with at the end.

Determine The Sort Order And Page Limit

I must specify the sort order very clearly at the beginning.

If it’s for a back-office application (to list clients or order), it’s enough to have a factual sort order like by ID or Name.

If it’s for a website, e-commerce or focus on the user experience, a more advanced sort order should be required. A sort order like relevance or best sales.

About the page limits, it’s a matter of testing. Test the load time with 20 items, 50 items and 100 items. Is it good enough? Keep the one that returns the best response time.

I have to keep in mind that:

• if the frontend can modify the sort order or page limit, there must be a default sort order.
• if the frontend won’t be able to modify the sort order or page limit, this must be hard-coded in the backend.

At the end, the backend and the frontend will have 3 new parameters to exchange: the sort order, the page size and the page index.

public ResponseEntity<List<Vehicle>> search(
    @RequestBody SearchCriteriaDto searchCriteria,
    @RequestParam SortOrder sortOrder,
    @RequestParam int pageSize,
    @RequestParam int pageIndex
  ) {
    ...
}

Fetch The Database

Once the pagination criteria are chosen, it’s time to apply them to the database queries.

I must apply those criteria to main database queries.

• The sort order will be transformed into ORDER BY;
• The page size will be transformed into LIMIT;
• And the page index will be transformed into OFFSET.

SELECT * FROM vehicle
  WHERE (...)
  ORDER BY vehicle.sale_price
  LIMIT 20
  OFFSET 40;

This reduces the amount of data handled by the application.

Reducing the amount of data reduces the time spent to transform it and return it.

Adapt the Frontend

The backend accepts now 3 new parameters: the sort order, the page size and the page index.

The frontend must send those parameters when loading the results.

The frontend must also create a new component to display to the user the selection of the sort order, page size and how to navigate over the pages.

Conclusion

Once all is developed, I must measure again the times of my backend. And compare them with the times of the beginning.

If the response times are fast enough, I can consider the solution satisfactory. Otherwise, I can play a little bit with the page size.

But if the response times aren’t fast enough, I must look for another solution to optimize my API response times.