E
egarcia
Guest
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.
Continue reading...
Continue reading...