A
Andy Whitcombe
Guest
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
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
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...
Code:
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
Code:
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 = ?.
Continue reading...