Paging in a resultset with many many records

Posted by agent_008_nl on 06-Mar-2015 03:57

Let's say I have a resultset with a large number of records, f.e. about 100.000 pages with 10 records. Kendo has a very nice grid,, owned now by psc, see how paging works there with 10 items per page: If you press 'last record' you will see the label '...' of one of the paging buttons change to 10. The total number of records is shown also (how to count a number of say one million in a performant way on the oe backend?), plus the selected recordrange-numbers. Now imagine you have those 100.000 pages with 10 records and pressing on 'last record'. Would it be possible to keep the UI working in the same way with an oe backend and acceptable performance? If not, are there plans for improvement? How to change the functionality neatly?  
  Of course the problem of efficiently paging through large resultsets has been thought over by others, see f.e. (duration in the table in ms, see next link)

Kind regards,

Stefan Houtzager

Houtzager ICT consultancy & development


All Replies

Posted by egarcia on 06-Mar-2015 07:25

Hello Stefan,

Thank you for bringing this topic up.

Performance of paging depends on the implementation.

(For examples, the links that you provided show that an initial implementation, copying all the records to a temporary table, was not efficient and then could be optimized further.)

I think that having optimizations at the database and language level to perform common operations such as count records and paging are always welcome.

For example, the database engine knows / should know the total number of records in a table since it performs all the CRUD operations.

This could also be done at the application level, if all CRUD operations are performed via a service.

Caching can also be used to improve paging, by caching results of queries and frequent queries.

I did a quick test with the sample implementation of the JSON Filter Pattern with 1,000,000 records in the Customer table:


I did not use filtering nor sorting. I only tried to optimize the performance by quickly increasing -B.

A request with top: 10 (records), skip: 999900 (records) took 10 seconds.

This code uses REPOSITION-TO-ROW().

10 seconds is about the limit on keeping user's attention:

This could be optimized.

Regarding calculating the total number of records.

A way to do it today is by using NUM-RESULTS:






qh:QUERY-PREPARE("PRESELECT EACH Customer WHERE " + filter).        



This code took about 4.5 seconds on my machine.

The total number of records should not be calculated every time. Caching, calculating the total in advance could be used to optimize the performance.

I hope this helps.

Posted by Matt Baker on 06-Mar-2015 07:40

This isn’t a new problem.
Counting records like this is really inefficient.  This is why the Progress grid has “max data guess” property so you can make the scrollbar appear sane even though it is just a guess.
This is general problem not specific to kendo and there are lots of articles on the web.
First off, if you are using kendo you have to do server side paging and probably want to consider virtual paging.
and the demo:
Sencha ExtJS 4+ supports this as well for those not using kendo.

This thread is closed