I have 3000 records in an employee table which I have fetched from my database with a single query. I can show 20 records per page. So there will be 150 pages for with each page showing 20 records. I have two questions on pagination and sortable column approach:
1) If I implement a simple pagination without sortable columns, should I send all 3000 records to client and do the pagination client side using javascript or jquery. So if user clicks second page, call will not go to server side and it will be faster. Though I am not sure what will be the impact of sending 3000 or more records on browser/client side? So what is the best approach either sending all the records to client in single go and do the sorting there or on click of page send the call to server side and then just return that specific page results?
2) In this scenario, I need to provide the pagination along with sortable columns (6 columns). So here user can click any column like employee name or department name, then names should be arranged in ascending or descending order. Again I want to know the best approach in terms of time response/memory?
So if you're paginating for primarily cosmetic reasons, it makes more sense to handle it client side. And if you're paginating to reduce initial load time, server side is the obvious choice.
Server-side pagination is a way of controlling a subset of data requests that were fetched from a client-side server, and are useful if we don't want to display all of the data at once. Server-side pagination requires the client to pass in a parameter in order to fetch the relevant information.
Server-Side Paging (Angular) Server-side paging consists of making requests that bring in one page of data at a time. The actual commands used to retrieve the data depend on the API exposed by the server.
Pagination is a necessary evil when you have too many items to easily show them all on one screen. Linear content flows—such as articles like this—should almost never be broken up into multiple screens.
Sending data to your client is almost certainly going to your bottleneck (especially for mobile clients), so you should always strive to send as little data as possible. With that said, it is almost definitely better to do your pagination on the server side. This is a much more scalable solution. It is likely that the amount of data will grow, so it's a safer bet for the future to just do the pagination on the server.
Also, remember that it is fairly unlikely that any user will actually bother looking through hundreds of result pages, so transferring all the data is likely wasteful as well. This may be a relevant read for you.
I assume you have a bean class representing records in this table, with instances loaded from whatever ORM you have in place.
If you haven't already, you should implement caching of these beans in your application. This can be done locally, perhaps using Guava's CacheBuilder
, or remotely using calls to Memcached for example (the latter would be necessary for multiple app servers/load balancing). The cache for these beans should be keyed on a unique id, most likely the mapping to the primary key column of the corresponding table.
Getting to the pagination: simply write your queries to return only IDs of the selected records. Include LIMIT
and OFFSET
or your DB language's equivalent to paginate. The caller of the query can also filter/sort at will using WHERE
, ORDER BY
etc.
Back in the Java layer, iterate through the resulting IDs of these queries and build your List
of beans by calling the cache. If the cache misses, it will call your ORM to individually query and load that bean. Once the List
is built, it can be processed/serialized and sent to the UI.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With