fast method for getting total record count and counting facets within all records

[webguy]

If you do a preselect with a dynamic query is there away to feed all records scanned to a prodataset prior to the repeat?

We use dynamic queries to build our lookup and after doing various finds and counts we output the info to a temp table for display. The problem is that we stop the repeat on the dynamic query after we reach the number of results to display on each page. This is done to make things fast. Preselect scans all the records so we can get the total counts, but when we build our temp table we only populate it with the number of records we want to display at a selected reposition-to-row for paging.

For example in our scenario we do a lookup for shoes and shirts. I can get the total number of records found using preselect and query-items:num-results. Say we have a total of 1,000 record results. I can get that total but for speed we stop the repeat once we reach the total number of records we want to display per page. It works great but I cant get total counts for individual items within the lookup this way.

I was wondering if a prodataset can be populated with the records for the table scanned prior to the repeat so I can get total counts for indivdual items. yes, I know we can just populate the temp table with all records but it slows down large record results quite a bit. I was hoping since preselect is scanning all the records anyway we could possibly dump results just to get counts to a prodataset since all records are getting scanned anyway? Just tryign to figure out how to speed this up for large record results.

Simple example:

def var query-item  as handle no-undo.
def var num-records as char no-undo.
create query query-items.
query-items:set-buffers(buffer item:handle).
query-items:query-prepare("preselect each item .... no-lock").
query-items:query-open.
 
total-records = query-items:num-results.
 
/* can I feed scanned records to a prodataset prior
   to the repeat? */
 
if query-items:is-open then do:
 
    /* for paging */
    query-items:reposition-to-row(page).
 
    repeat:
       query-items:get-next.
       if query-items:query-off-end then leave.
 
       create temp table records
       /* here we do any finds etc to populate temp table */
 
       assign count = count + 1.
 
       /* stop and leave if our count reaches the number of results
           to display per page. helps speed up the display. problem
           is that I cant get total counts for specific items like shoes
           or shirts out of the total records found */
       if count >= records-per-page then leave.
 
    end.
 
    query-items:query-close().
    delete object query-items.
 
end.

 

[MaximMonin]

Query result is cached by DB server, not a process. I think there isn't any other method to fetch it except getfirst and get next

 

[MaximMonin]

Btw, try Dataset Fill Method

define dataset ds for temptablename.
define query q_items
Query q_items:query-prepare("preselect each item .... no-lock").
Define Data-source ds_items from query q_items.
buffer temptablename:Attach-data-source (ds_items:HANDLE).

hDs = Dataset DS:HANDLE.
hDs:Fill ().

 

[RealHeavyDude]

AFAIK there is no direct way to get the record count when populating a ProDataSet with the FILL method. Once I've been told by somebody from Progress that the reason for the FILL method being much faster than doing a manual buffer copy is that the the manual buffer copy is always round tripping between the ABL execution layer and the the database engine layer whereas the FILL is not.

The only way to get the record counts when using FILL is using a callback. But then - IMHO - you abandon the performance benefit of the FILL method.



Heavy Regards, RealHeavyDude.

 

[webguy]

Thanks for the input guys. I think I may play around with some json calls after the request completes. Not the best method but I want to return the first set of results as fast as possible. This is one area where sql really shines.