What are the best practices in pagination?

Question

Im doing a MVC web project in .NET, and kind of thinking best way to show a list and pagination. I have 3 options

1. I can show the table using Jquery Tables(Data Table JS).
2. I can use MVC pagination.
3. Manually handling pagination using a SP.

Option 1 will fetch all data at once using a AJAX, and table js will handle paging itself. We don't have to worry about paging,but if it's a huge data set to handle will it wise to have this ???, because every time it will load all the data.

Option 2 will get only the data which will need to show (Set by set), but each time a request may be triggered to the server side.(This will manage the pagination in controller level IPagedList)

Option 3 also will fetch only the data we need from the database level. Same as option 2 will const server request for each page changes.

So what would be the best option, we have to think about two scenarios may be. That will be if it is big data set and if it is small data set.

What are the best ways of doing this? Appreciate any explanation ?

Answer 1

An obvious solution is to go with option 2. But in which a user can able configure records per page so that if records per page are less, then it will load instant in the browser as well as pull data from server quickly. A user is never ready to see loading they want the result as soon as possible.

Here you need to also take care of sorting in the grid if you are providing sorting feature. Means you need to sort in entire dataset instead of just in the current dataset.

Further, you can also provide a filter on the grid. Same concern, you need to filter the entire dataset.

So this approach will work with both scenarios like small dataset as well as the large dataset.