Display large amount of data to client through pagination

Question

I have a web application in which i need to show a big number of data or records for clients. Now i 'll use pagination but i was wondering should I:

1. Load all the data once then pagination, sorting and sarching 'll be easy..But it 'll takes big time(using local DB it takes up to 9 sec.)
2. Or each time i show new page(from the pagination) i make a new request to server and then new request to DB to get the next records..But then what if the client click on Prev button, i 'll make a new request to get data that I had previously..Should i cach data that are loaded before and how if that's good technique?

So load all data once or make a new request every time i need data that maybe have been loaded before.

I'm using ASP.NET MVC SPA with durandaljs and knockoutjs

Answer 1

It's a tradeoff between speed, memory requirements in the cache, and programming required to keep the cache fresh. A lot depends on your use cases. For example

• If, like Google search results, most users only ever read page 1, just a few carrying on down to page 2 and beyond, then cacheing may not even be necessary.

• If users tend to repeatedly sort, search etc. then you can't cache individual pages because the number of combinations will overwhelm your storage. But if you have huge datasets, cacheing whole resultsets will do the same.

• If the data in the background is liable to change a lot even during reading one page, and that is critical to the business, then the risk of displaying stale data may turn you against cacheing.

Personally, it might be a better investment of time to look at refactoring your db to squeeze more life out of it. If your queries are taking 9s it may be because the data model is too complex, you are missing important indexes, or you're keeping hold of old data in your main tables that should be archived away instead.

Answer 2

You can use caching pagination, i.e. generate the first few pages, and as the user scrolls through the pages, load more pages in the background.

This should give you a good trade-off between initial respond speed and lag between pages.

Answer 3

Loading all the data at once is not necessarily all-bad in the sense that it has a lot of performance advantages. However, I would only use this method if:

1. I was 100% certain that this list would not grow to large amounts
2. I would store data not currently shown in javascript objects (in other words, don't put the burden on the browser other than the actual memory required to hold it).

Most often than not, I can't guarantee that the list would not grow, so I usually have to stick with the second solution you mentioned. Load by page on demand. This also means you cannot search using the items you have available unless searching for items on the current page only is acceptable. Searching must perform its own query on the database separate from the cache you have.

I would not bother to cache pages you've already loaded, since how often do you return to previous pages after you've seen them? It is not worth the loss of memory in my humble opinion. Rather, I would advise you to load several pages at once rather than just one at a time. When the user clicks next and nothing is loaded, you fetch the next 4 pages. This way, nothing must be fetched for the following 3. This is already a solution that would meet most of your requirements and still be 4 times as efficient than fetching one page at a time.

If you want to get even more sophisticated, you would use AJAX to load the next page once the user has loaded the current page. You only have to perform a check that the next page has already been loaded before showing the next page, which hopefully enough time will elapse that it will have already been loaded. And in the case in which it has not yet been loaded, you can show some sort of load animation to provide feedback to the user that you're currently working on it. This has the best performance of all solutions, however it is also the most difficult to implement.

Hope that helps!

Answer 4

Generally I prefer the number 2 solution, with regards of mixed approach

Paged queries:
Using a paged query when showing a list of data to end user is the normal way.
Following the approach of creating paged queries with the template query bellow:

SELECT TOP pageSize columns  
  FROM Table    
  WHERE IDColumn NOT IN ( SELECT TOP pageSize*(pageNumber-1) IDColumn    
  FROM Table    
  ORDER BY uniqueOrPrimaryKey)    
  ORDER BY uniqueOrPrimaryKey;

The key points will be:

• Designing efficient Data Transfer-er Objects.
• Choosing effective page size, based on entity size, bandwidth limitation, none functional testing ...
  

About cashing:
Using cashing strategy depends on the specification and business logic of you application.
Some data can be cashed and cashing some data will case inconsistency.

Think about an Internet-Banking software, where the user will see his accounts listed data, It will be a good approach to cash the accounts data when the user is logging in (as account creating is not normally done from internet-bank application).
Now the user can do some debit and credit actions and the balance of his accounts will be modified, we need to update the balance field and update the cash.

In any case using cashed data will confront you with application design questions like when to clear or update cashed data, using client side or server side cashing, data security on client side cashing, etc.

Conclusion
I will use paging when ever possible, that will optimize the performance.
Will use cashing on static data that are less update-able.
Will not use cashing on concurrent data and scenario.
Caveat-> Will not try to alter stateless nature of HTTP protocol by cashing.

Answer 5

You can have combined approach by taking best of both the worlds.

How? -->

Few weeks back I came across a library - oboe.js which loads the data in stream by giving parsed objects before the response completes.

So rather than waiting for 9 seconds to load the data you can stream it and use the parsed objects as soon as they are available. By this way, you will be able to load the whole data and also don't have to wait for long.