"Showing page 1 of many" Server-Side Pagination

jonahsimpson

Hey Folks,

Just wondering if anyone has done a "Showing page 1 of many" style implementation for the paging functionality before I go the route of building my own.

Our scenario is that we use DataTables to surface results for queries that could theoretically return 10s or 100s of thousands of rows, or more. Our queries to get a "page" of data return relatively quickly, but as soon as we ask for an exact count of rows the query plan blows up and we end up with a query that takes 10-50x longer to complete. Without going down the rabbit hole of why this is the case, we have tried different syntax, indexes, tuning, etc on the SQL side and ultimately, return the "total" is not going to be an efficient operation for a couple of the cases where we want to utilize DataTables.

To address this, I was imagining doing something like requesting (5* page size) + 1 and if we get that many rows back just saying that there is many pages and have the pagination control show:
Page 1 of Many |1| |2| |3| |4| |5| |Next|
If less than equal 5*page size rows
Page 1 of 3 |1| |2| |3| |Next|

From a UX perspective I think this should be sufficient because most users either zero in on a specific couple rows using filters or just work off the top set of results returned. Having a query that returns an approximate number of rows in <1 second is also a better user experience than getting an exact number of rows and having the results take 30 seconds to come back.

My initial research has shown that there is different ways to do render the pagination controls, but also that the Total result count is pretty integral in the pagination functionality. Any guidance is most appreciated.

Thanks in advance!
Jonah

colin

If you're using serverSide, and I'm guessing you are, you could really set the recordsTotal to be anything in your scenario - just make it a big number. You could remove, or modify, the information summary with dom or info so it wouldn't confuse the users (see language.info to modify).

Would that work?

Colin

jonahsimpson

I woke up thinking I was going to do something similar today if I didn't hear back on this question. Thanks for replying back with the properties that will get me there the fastest!