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: http://demos.telerik.com/kendo-ui/grid/index. 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.
http://www.4guysfromrolla.com/webtech/042606-1.shtml (duration in the table in ms, see next link)
http://www.4guysfromrolla.com/webtech/041206-1.shtml
--
Kind regards,
Stefan Houtzager
Houtzager ICT consultancy & development
www.linkedin.com/in/stefanhoutzager
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:
Link: community.progress.com/.../2279.business-entities-using-the-json-filter-pattern-code-samples.aspx
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:
www.nngroup.com/.../website-response-times
This could be optimized.
Regarding calculating the total number of records.
A way to do it today is by using NUM-RESULTS:
DEFINE VARIABLE filter AS CHARACTER NO-UNDO.
DEFINE VARIABLE qh AS HANDLE NO-UNDO.
ETIME(YES).
CREATE QUERY qh.
qh:SET-BUFFERS(BUFFER Customer:HANDLE).
qh:QUERY-PREPARE("PRESELECT EACH Customer WHERE " + filter).
qh:QUERY-OPEN ().
MESSAGE qh:NUM-RESULTS ETIME.
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.