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