Andy Whitcombe
New Member
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
using the cache option on the query handle does fix reads and performance. (as per KB entry 000026761 )
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
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
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
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
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 = ?.
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
using the cache option on the query handle does fix reads and performance. (as per KB entry 000026761 )
Code:
lvQueryHandle:CACHE = XYZ
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
Last edited: