Multi Table Dynamic Query Issues With -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 000026761

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

 

[Cringer]

Closing this as a dupe.