Multi Table Dynamic Query. -rereadnolock causing read issues.

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

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:

TomBascom

Curmudgeon
The only way to actually guarantee that you "always get the latest version of the records" and keep it that way is to read them with a lock. And if you think you had performance problems before... :(

Your static code already has at least some opportunity for drift from "the latest version" between the fetching of the data and the user acting on it (an interval that is likely vastly longer than the difference between the static and dynamic queries) so you are either somehow validating that no change occurred, or you are handling the ensuing errors, or you have simply accepted the possibility and ignored it.

Thus, IMHO, you're making a mountain out of a molehill. Add the CACHE option.

Also -- you should strongly consider moving to oe12 mid-year rather than 117. Server side joins do not initially support dynamic queries (they are initially only supported for static FOR EACH statements) but I expect that would be one of the first enhancements to see. And that should have a significant impact on this issue.
 

Cringer

ProgressTalk.com Moderator
Staff member
Hi Andy,
When preparing a query tuning talk a few years ago I also found that I had to set the CACHE property to get the expected reads on some queries. I did do some research then but didn't find another solution. Are you able to ascertain that the CACHE property is definitely causing issues with accuracy?

By the way, just a little tip I learnt recently that makes the handling of the results a lot easier, you can do the following:

Code:
QueryResult:
DO WHILE lvQueryHandle:GET-NEXT():

    IF lvSomeCriteria THEN 
        NEXT QueryResult.

END.

In your noddy example it makes little difference, but in a real example, it means you don't have to implicitly GET-NEXT every time you want to go to the NEXT result in the query.
 

Andy Whitcombe

New Member
Hi JP,

Its been a while, hope all is well with you and yours.

I did a whole host of testing, and for the application being designed, the better solution appeared to be the :FORWARD-ONLY attribute on the query.

It was consistently quicker that the :CACHE option and resolved the reads.

Its not something that our development teams have used consistently (as you probably remember) and is certainly worth a review going forward.

Thanks
Andy
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
can we discuss on more topic of progress like OOPs , memory management, array ,etc . plz help
If you have a new question unrelated to this thread, please start a new thread. Though if you are looking for meaningful help, I suggest you use more than one sentence to describe what you are looking for.
 
Top