Multi Table Dynamic Query Issues Using -rereadnolock - Forum - OpenEdge General - Progress Community

Multi Table Dynamic Query Issues Using -rereadnolock

 Forum

Multi Table Dynamic Query Issues Using -rereadnolock

This question is answered

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

[/CODE]


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 = ?.

[/CODE]


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

Verified Answer
  • You're comparing a FOR EACH loop with a query. Those are two different things with different levels of functionality etc.

    What happens if you set lvQueryHandle:FORWARD-ONLY = YES before opening the query ? That'll avoid building a result list etc. and *should* make the query behave more like the FOR EACH.

All Replies
  • You're comparing a FOR EACH loop with a query. Those are two different things with different levels of functionality etc.

    What happens if you set lvQueryHandle:FORWARD-ONLY = YES before opening the query ? That'll avoid building a result list etc. and *should* make the query behave more like the FOR EACH.

  • Hi,

    Thanks for the reply; I appreciate the difference in functionality, but I didn't expect such a difference in performance for such a basic query predicate.

    I did consider forward-only, but didn't think I could use it, coming back to this with a fresh pair of eyes this morning, I have tried (as per your suggestion) that and the initial results look very promising.

    Thanks.

  • Also I think that using REPEAT has an overhead that using a DO does not. Don't know whether the difference would be noticeable but might be worth a try.

    Instead of

    REPEAT:

     lvQueryHandle:GET-NEXT().

     IF lvQueryHandle:QUERY-OFF-END THEN

        LEAVE QueryResult.

    END.

    Try this (it's also less code)

    DO WHILE  lvQueryHandle:GET-NEXT():

    END.

  • Andy - have you discovered any more?

  • 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.

    Thanks to frank.meulblok ; appreciate the input, we have used that in the past, but not consistently... perhaps going forwards it could be one for the development team to review.

  • Interesting. I shall experiment with FORWARD-ONLY myself.

    Did you notice any performance difference using DO WHILE instead of REPEAT?

  • It was actually slower in all instances;  

  • Interesting.

    I've just tried this with 11.2 client and 11.7 database.

    For me FORWARD-ONLY and DO or REPEAT make no measurable difference.

  • We are still, for the moment running 102.B, until the summer. What volume of records are you returning? mine was just shy of 50,000 records for the speed tests.

  • Probably about half that.

    Measuring in milliseconds using ETIME.

  • First query is always the slowest, so that one ignored, then ran a further about 9 queries. 3 with one setting and then 3 with the other then repeat with first setting.

  • Yes, also using ETIME; are you using the -readreadnolock start-up parameter? when that is missing then we don't have the problem...  

  • Ah yes - that was the point of your post wasn't it!

    I focussed on the idea FORWARD-ONLY improving performance and forgot about the rereadnolock :-)

    I have rerun the tests using -rereadnolock.

    I can confirm that -rereadnolock slows it down and using FORWARD-ONLY gets it back to the speed (or very nearly) that you would have without -rereadnolock.

    FORWARD-ONLY appears to make no difference unless -rereadnolock is being used.

    DO vs REPEAT - I see no difference in speed.