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.
*/