Hi

See attached program as an example and the see screenshot of timings when using a preselect compared to a each in the query.

These queries are used in combination with a Kendo Grid and so we need to have a record count for the query results set to enable the Grid object to provide forward and backwards scrolling.

Using a stored proc to do the SQL COUNT(*) is very quick and is essentially what we have had to make this functional although it is time consuming and requires building two query strings one OE and one native T-SQL, why can you not add this functionality into the Query object to be able to quickly get a record count of your query and thus prevent you having to use a preselect which is slow!

The Dataserver converts the query into SQL already it just needs to do this as a COUNT(*).

Thanks

Christian.

def var lv-A as int64 no-undo.
lv-a = ETIME(yes).

/* DEFINE QUERY qryTest FOR RM-JOB FIELDS(rm-job.org-code rm-job.job-no), RM-JOB-COMPONENTS FIELDS(RM-JOB-COMPONENT.org-code RM-JOB-COMPONENTS.job-no), RM-LOC-COMPONENT FIELDS(RM-LOC-COMPONENT.LOCCOMP-SEQ), RM_COMPONENTS FIELDS(RM_COMPONENT.ORG-CODE RM_COMPONENTS.COMPONENT),
                         CO-PLACE FIELDS(co-place.place-ref), CO_STREET FIELDS(co_street.street) SCROLLING. */


DEFINE QUERY qryTest FOR RM-JOB, RM-JOB-COMPONENTS, RM-LOC-COMPONENT, RM_COMPONENTS, CO-PLACE, CO_STREET  SCROLLING.

OPEN QUERY qryTest FOR EACH RM-JOB  NO-LOCK WHERE RM-JOB.ORG-CODE = '01' 
                                                   AND (rm-job.action-taken-code = '' OR rm-job.action-taken-code = '3rd' OR rm-job.action-taken-code = 'ADH' OR rm-job.action-taken-code = 'ADR' OR rm-job.action-taken-code = 'ADT' OR rm-job.action-taken-code = 'ASS' OR rm-job.action-taken-code = 'BAT' OR rm-job.action-taken-code = 'CFH' OR rm-job.action-taken-code = 'CFR' OR rm-job.action-taken-code = 'COB' OR rm-job.action-taken-code = 'CON' OR rm-job.action-taken-code = 'FLG' OR rm-job.action-taken-code = 'FLO' OR rm-job.action-taken-code = 'FUS' OR rm-job.action-taken-code = 'INT' OR rm-job.action-taken-code = 'KN' OR rm-job.action-taken-code = 'KNA' OR rm-job.action-taken-code = 'LAM' OR rm-job.action-taken-code = 'LCC' OR rm-job.action-taken-code = 'MCB' OR rm-job.action-taken-code = 'MFA' OR rm-job.action-taken-code = 'MRE' OR rm-job.action-taken-code = 'MTR' OR rm-job.action-taken-code = 'NFF' OR rm-job.action-taken-code = 'OT' OR rm-job.action-taken-code = 'PCB' OR rm-job.action-taken-code = 'PRD' OR rm-job.action-taken-code = 'REP' OR rm-job.action-taken-code = 'RES' OR rm-job.action-taken-code = 'REU' OR rm-job.action-taken-code = 'RPH' OR rm-job.action-taken-code = 'SCR' OR rm-job.action-taken-code = 'SUB' OR rm-job.action-taken-code = 'TMC' OR rm-job.action-taken-code = 'TXF')                           
                                                   AND RM-JOB.JOB-TYPE = 'FAUL',
          EACH RM-JOB-COMPONENTS WHERE RM-JOB-COMPONENTS.ORG-CODE = RM-JOB.ORG-CODE AND RM-JOB-COMPONENTS.JOB-NO = RM-JOB.JOB-NO NO-LOCK,
          EACH RM-LOC-COMPONENT WHERE RM-LOC-COMPONENT.LOCCOMP-SEQ = RM-JOB-COMPONENTS.LOCCOMP-SEQ NO-LOCK,
          EACH RM_COMPONENTS WHERE RM_COMPONENTS.ORG-CODE = RM-LOC-COMPONENT.ORG-CODE AND RM_COMPONENTS.COMPONENT = RM-LOC-COMPONENT.COMPONENT NO-LOCK,     
          EACH CO-PLACE WHERE CO-PLACE.PLACE-REF = RM-JOB.PLACE-REF NO-LOCK,
          EACH CO_STREET WHERE CO_STREET.STREET = CO-PLACE.STREET NO-LOCK INDEXED-REPOSITION QUERY-TUNING(NO-FIREHOSE-CURSOR).
          
GET FIRST qryTest.

DISPLAY ETIME RM-JOB.JOB-NO QUERY qryTest:NUM-RESULTS WITH FRAME A.      

/* Try same query with a preselect over 1.9 million rows - Takes a much longer time */
lv-a = ETIME(yes).
OPEN QUERY qryTest PRESELECT EACH RM-JOB  NO-LOCK WHERE RM-JOB.ORG-CODE = '01' 
                                                   AND (rm-job.action-taken-code = '' OR rm-job.action-taken-code = '3rd' OR rm-job.action-taken-code = 'ADH' OR rm-job.action-taken-code = 'ADR' OR rm-job.action-taken-code = 'ADT' OR rm-job.action-taken-code = 'ASS' OR rm-job.action-taken-code = 'BAT' OR rm-job.action-taken-code = 'CFH' OR rm-job.action-taken-code = 'CFR' OR rm-job.action-taken-code = 'COB' OR rm-job.action-taken-code = 'CON' OR rm-job.action-taken-code = 'FLG' OR rm-job.action-taken-code = 'FLO' OR rm-job.action-taken-code = 'FUS' OR rm-job.action-taken-code = 'INT' OR rm-job.action-taken-code = 'KN' OR rm-job.action-taken-code = 'KNA' OR rm-job.action-taken-code = 'LAM' OR rm-job.action-taken-code = 'LCC' OR rm-job.action-taken-code = 'MCB' OR rm-job.action-taken-code = 'MFA' OR rm-job.action-taken-code = 'MRE' OR rm-job.action-taken-code = 'MTR' OR rm-job.action-taken-code = 'NFF' OR rm-job.action-taken-code = 'OT' OR rm-job.action-taken-code = 'PCB' OR rm-job.action-taken-code = 'PRD' OR rm-job.action-taken-code = 'REP' OR rm-job.action-taken-code = 'RES' OR rm-job.action-taken-code = 'REU' OR rm-job.action-taken-code = 'RPH' OR rm-job.action-taken-code = 'SCR' OR rm-job.action-taken-code = 'SUB' OR rm-job.action-taken-code = 'TMC' OR rm-job.action-taken-code = 'TXF')                           
                                                   AND RM-JOB.JOB-TYPE = 'FAUL',
          EACH RM-JOB-COMPONENTS WHERE RM-JOB-COMPONENTS.ORG-CODE = RM-JOB.ORG-CODE AND RM-JOB-COMPONENTS.JOB-NO = RM-JOB.JOB-NO NO-LOCK,
          EACH RM-LOC-COMPONENT WHERE RM-LOC-COMPONENT.LOCCOMP-SEQ = RM-JOB-COMPONENTS.LOCCOMP-SEQ NO-LOCK,
          EACH RM_COMPONENTS WHERE RM_COMPONENTS.ORG-CODE = RM-LOC-COMPONENT.ORG-CODE AND RM_COMPONENTS.COMPONENT = RM-LOC-COMPONENT.COMPONENT NO-LOCK,     
          EACH CO-PLACE WHERE CO-PLACE.PLACE-REF = RM-JOB.PLACE-REF NO-LOCK,
          EACH CO_STREET WHERE CO_STREET.STREET = CO-PLACE.STREET NO-LOCK INDEXED-REPOSITION QUERY-TUNING(NO-FIREHOSE-CURSOR).
          
GET FIRST qryTest.

DISPLAY ETIME RM-JOB.JOB-NO QUERY qryTest:NUM-RESULTS WITH FRAME B.      


/* Using a stored proc to do the SQL COUNT(*) is very quick why can you not add this functionality into the Query object to be able to quickly get a record count of your query and thus prevent you having to use a preselect which is slow!
E.g.

    ASSIGN lv-mySQLCountStatement = SUBSTITUTE("SELECT COUNT(*) FROM &1 WHERE &2", lv-mySQLCountTableList, lv-mySQLCountWhereClause).
    IF {&DEBUGMSGS} THEN MESSAGE "SQL: " lv-mySQLCountStatement.    
    ASSIGN lv-SqlCountResult = "0".    
    RUN STORED-PROC ih.send-sql-statement hProc-Handle = PROC-HANDLE NO-ERROR (lv-mySQLCountStatement).
    
    IF ERROR-STATUS:ERROR THEN     
    DO iNumErrors = 1 TO ERROR-STATUS:NUM-MESSAGES:         
        MESSAGE "error" ERROR-STATUS:GET-NUMBER(iNumErrors) ERROR-STATUS:GET-MESSAGE(iNumErrors).
    END. 
    ELSE DO:        
        FOR EACH ih.proc-text-buffer WHERE PROC-HANDLE = hProc-Handle.
          ASSIGN lv-SqlCountResult = proc-text.
        END.
    END.
    CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = hProc-Handle.    
    
*/