filter pagination sorting at frontend or backend

Question

I have an application mostly display table data from database. but those tables need to be applied pagination, sorting, filter. for sorting and filter, they needs to be applied on the whole list, not only 1st. page. I have 3 ideas in my head:

• retrieve the whole list from backend, and then apply pagination, sorting, filter on frontend using javascript. (this way is very easy and I found a lot libs can do it).
• do sorting, pagination, filter on hibernate level.(ie. create dynamic SQL to get result and then using AJAX call render to frontend.)
• do sorting, pagination, filter on java level. (ie. get the whole list from database and then using Spring PagedListHolder do pagination and sorting, maybe other framework for filter. and then again using AJAX call render to frontend.)

which way is best to achieve this amount those three? or there is other better way that I didn't think.

Thanks for any help or tip.

Answer 1

You don't say how much data is involved. If it is a lot you certainly don't want to be sending that around e.g. Don't send 20GB to a javascript in a browser to render.

Generally it's better to pare down your results set and do all the manipulation you can as close to the database as possible. But you don't want to get too wild with the dynamic SQL. You don't want a user to be able to cause some giant query on non-indexed data.

I am a big fan of spring and think if you could combine it's paging capability with hand optimized Queries in your Repository classes that would be "best".

Answer 2

Server side pagination will almost always win. And not just server side in the app layer, pagination via the database. From a performance point of view, asking the database for a small limited subset and returning that will be the most performance.

Years ago I worked on an app where the server side code was pulling all data from the DB (about 200k rows - which in the industry isn't a lot) and then paging it via code. It took 30-50 seconds to render the page. When I switched to DB pagination, it dropped to 1.5 seconds.

Now that's server side pagination not even even front-end back-end. Doing it via jquery (on a large dataset) or even a medium sized data set means having to send EVERYTHING to the browser. That's an expensive operation.