[Progress Communities] [Progress OpenEdge ABL] Forum Post: Multi Table Dynamic Query Issues Using -rereadnolock

[Andy Whitcombe]

Issue regarding a multi table dynamic query and –rereadnolock against a session (defined in the .pf) Environment, 10.2B (but moving to 11.7 Mid year) I have a situation where the maximum number of results returned for a specific query is 47986

FOR EACH TRADING_SEASON NO-LOCK WHERE TRADING_SEASON.TRADING_SEASON_CODE EQ '18', EACH AREA_ASSIGNMENT NO-LOCK WHERE AREA_ASSIGNMENT.TRADING_SEASON_NUMBER EQ TRADING_SEASON.TRADING_SEASON_NUMBER

Run as a static query, performance is good and the table reads are as per expectations at 47987 Running as a dynamic query, I get two different sets of results

 DEFINE VARIABLE lvQueryHandle AS HANDLE NO-UNDO. DEFINE VARIABLE lvIndex AS INTEGER NO-UNDO. CREATE QUERY lvQueryHandle. lvQueryHandle:SET-BUFFERS(BUFFER TRADING_SEASON:HANDLE, BUFFER AREA_ASSIGNMENT:HANDLE). /*lvQueryHandle:CACHE =1.*/ lvQueryHandle:QUERY-PREPARE("FOR EACH TRADING_SEASON NO-LOCK WHERE TRADING_SEASON.TRADING_SEASON_CODE EQ '18', EACH AREA_ASSIGNMENT NO-LOCK WHERE AREA_ASSIGNMENT.TRADING_SEASON_NUMBER EQ TRADING_SEASON.TRADING_SEASON_NUMBER"). ETIME(TRUE). lvQueryHandle:QUERY-OPEN. QueryResult: REPEAT: lvQueryHandle:GET-NEXT(). IF lvQueryHandle:QUERY-OFF-END THEN LEAVE QueryResult. END. MESSAGE ETIME SKIP lvQueryHandle:NUM-RESULTS VIEW-AS ALERT-BOX INFO BUTTONS OK. DO lvIndex = 1 TO lvQueryHandle:NUM-BUFFERS: Message lvQueryHandle:GET-BUFFER-HANDLE(lvIndex):NAME SKIP ENTRY(lvIndex,lvQueryHandle:PREPARE-STRING) skip lvQueryHandle:INDEX-INFORMATION(lvIndex) view-as alert-box. END. lvQueryHandle:QUERY-CLOSE(). IF VALID-HANDLE(lvQueryHandle) THEN DELETE OBJECT lvQueryHandle. ASSIGN lvQueryHandle = ?.

If the session doesn’t have –rereadnolock specified, index reads and performance remain good. Execution time 3642ms, reads Trading Season Table 1, Area Assignment Table 49787, Total Results 49786 Index Selection – As expected If the session does have –rereadnolock specified, index reads and performance go to the wall. Execution time 49820ms, reads Trading Season Table 49787 , Area Assignment Table 99573, Total Results 49786 Index Selection – As expected I have found the following KB entry ? knowledgebase.progress.com/.../P147331 And using the cache option on the query handle does fix reads and performance. When I deploy the query will run on the Appserver However, due to the nature of the screen being produced, it’s imperative that I always get the latest version of the records in question, so I am naturally cautious to use the cache and risk that the results are incorrect; Does anyone have any thoughts/ suggestions, Cheers Andy

Continue reading...