I will start by writing a multi session test suite with harness to better understand the extent of the message buffer usage in OE, the documentation states that it is used for for each no-lock, but I suspect there are corner cases OE's documentation omits.

I also found some extra information on this aspect:

• prefetchDelay, prefetchFactor, prefetchPriority are 3 other options beside prefetchNumRecs. Not sure if relevant at all.
• record phrases can be set with NO-PREFETCH.

How fit is this model for FWD from the POV of performance (scrolling)¶

I am not entirely sure how this OE prefetch would map to FWD ProgressiveResults. OE technique is to bring in fixed sized batches, while FWD brings exponentially bigger sized batches.
Presuming that OE is prefetching 16 records in 1 call, 32 records in 2 calls and 48 records in 3 calls, FWD is gathering 16 records in 3 calls (1 + 10 + 100), 32 records in 3 calls and 48 records also in 3 calls. In other words, queries in >=48 records should be faster in FWD unless invalidation kicks in.

Switching to a batched model like OE is wrong. OE is using a cursor that can gather 16 records at once very fast. In FWD, gathering N records can be very slow if the OFFSET is high (e.g. OFFSET 1000 LIMIT 16). So, the FWD model for progressively larger results is fit for OFFSET/LIMIT query method. I am strongly in favor of not honoring the prefetch for scrolling-mode queries

How fit is this model for FWD correctness¶

There is a certain edge case here. FWD is eagerly providing updated records, whereas OE presents stale data. This is a functional mismatch. What if the NO-LOCK session relies on not seeing the latest data? It sets a high prefetch window so that the session is basically preselecting with each FOR EACH NO-LOCK. It seems quirky, but possible (e.g. a reporting process that starts with a high -MM just to make FOR EACH NO-LOCK consistent).

How fit is this model for FWD from the POV of performance (dynamic)¶

This is the most important bullet. Invalidated queries in FWD gather records 1 by 1, whereas in OE the prefetch is honored so that records are gathered in batches (e.g. 16). If NO-LOCK, the buffer will be populated with a stale DMO (one we mark as COPY in FWD when gathered from dirty-share).

More exploration¶

• How does current session affect the prefetch? I understand that new/updated/deleted records from other sessions are invalidating the ProgressiveResults, but not seen by OE prefetch. However, what if the same session is actually creating/deleting/updating records on that table. Will the "prefetch" be dropped? I am quite convinced that yes.
  • I wonder if we can prefetch from persistent database and use that prefetch against the intra-session dirty-share database. In other words, if we have 2 records in dirty-share are 16 records prefetched (so 18 records in memory), can we rely on the fact that it is enough to work on that 16 pre-fetched records? Or are only 14 records pre-fetched?
  • What if we continue to gather new records with that query after create/update/delete in the same session? Will it "revalidate" the prefetching in OE?
  • TL;DR does creating/updating/deleting records in a table in the same-session invalidate the prefetch?
• I wonder how multi-table queries are honoring the pre-fetch. The NO-PREFETCH is a record phrase setting, which means that query components are independent and prefetching happens per component. This would be nice as it is straight-forward to implement in FWD.
• What happens in UNDO cases? I think this has the same answer as the first bullet. If the same session did create/update/delete records in that table, the prefetch is dropped, so that undo doesn't affect the prefetch.
• Does the prefetch happen for FOR EACH blocks only, or for OPEN QUERY as well? If it happens for OPEN QUERY, we need to understand the implication of PREVIOUS, FIRST, LAST as well + if the query is scrolling, what happens with REPOSITION (in SCROLLING or INDEXED-REPOSITION modes).

Idea sketch¶

I think we can add an intermediary PrefetchedResults that is an implementation of Results or sub-class of SimpleResults (maybe similar to BatchResults). It stays in between the query and the results implementation. When doing a next, it will automatically "cache" the next N results in a List. I guess it can simply mark the DMO kind as COPY, to avoid attaching to a live image of a record.

• For ScrollingResults it should work straight-forward. It will be used just to ensure correctness.
• For ProgressiveResults it should work quite easy, but there is a caveat: when changing the bracket, the ProgressiveResults might invalidate, so there might be some state we need to ensure is preserved.
  • For example, we prefetch 5 records, but on the 6th the query invalidates and we can't tell what will the 6th record actually be.
  • A trivial fix would be to make ProgressiveResults use base N, so that all brackets are multiples of N, so that prefetching would never occur across brackets. I am not entirely sure how this would work with PREV or reposition however.
  • PS: what happens if another session commits right in the mean-time of this whole ProgressiveResults prefetching?
• For DynamicResults, using a PrefetchedResults would be straight-forward, as long as the revalidation doesn't affect the prefetcing time. However this will still fetch records one-by-one from database.

Invalidation improvement idea - active bundle¶

The fix by now is only functional. In order to improve dynamic query performance, we would have to change RAQ so that it prefetches N records instead of 1 and resolves all these N records at once against the dirty database, instead of fetching them 1 by 1. For an index with (f1, f2), we can have the following situation:

• f1 = ? and f2 = ? and recid > ? -> finds 2 records
• f1 = ? and f2 > ? -> finds 10 records
• f1 > ? -> finds 10.000 records.

We would need to execute f1 = ? and f2 = ? and recid > ? with limit N, f1 = ? and f2 > ? with N - (number of records from first query e.g. 2) and f1 > ? with N - (number of records from first query e.g. 2) - (number of records from second query e.g. 10). In this specific case, the revalidation should have occurred after the first 12 records, so the prefetching here is tricky as we would need to stop after first 12 records, revalidate and get next 4 records to complete the prefetch.

PS: what happens if another session commits right in the mean-time of this whole process of fetching the active bundle and revalidation?

Invalidation improvement idea - dirty-share implication¶

We would need to only extract the first 16 and resolve against dirty-share. The though part would be to understand if prefetching N would be enough, considering that some of these N may have been updated/deleted in the dirty-share, so more than N would have been needed in the prefetch.

Invalidation improvement idea - #8388¶

I think the cleanest way to handle this would be to fix #8388 first, as that eliminated the need of intertwining the persistent database data with the dirty-share data at server-side and simply let the DB prefetch the next N records using a LIMIT 16. The UNION solution will properly resolve the updated/deleted records problem at the DB side and get us the correct next 16 records.

I think this last point is also subject to DB synchronization and will atomically get us the right prefetch in respect to other sessions work.

Conclusion¶

I think we will need more exploration first, especially in regard to intra-session changes visibility in prefetch and its invalidation.

Please also consider that the OE behavior might differ based on the reread-nolock startup parameter.

My understanding is that rereadnolock affects which version of cached records will be served, it does not fetch the latest true version from the DB. I confirmed with a test case that the message buffer is still used regardless of rereadnolock. Another point is that these parameters are for 2 distinct layers of the application, -Mm is a database parameter, any client connected to it will use the message buffer, whereas rereadnolock is a client parameter which affects only the client's management of the already fetched records.

Alex, I committed the batch fetching for invalidated AQ to 11723a.

Prefetched records in the window/batch are frozen against other sessions' commits; the next batch sees those commits; this session's own changes are always visible regardless of DML.

This is safe because RAQ navigation is keyset-based, not offset-based. Each single-record fetch was already its own keyset step at its own point in time, so batching widens an existing staleness window from 1 record to N. It introduces no anomaly class that record-at-a-time retrieval did not already permit.

• PrefetchWindow - holds frozen rows, delivery cursor, ramp and generation counter. Implements RecordChangeListener and registers once. ChangeBroker is context-local, so it reports only this session's changes, which is exactly the split the contract needs. Discards outright rather than overlaying the session's version of each record, because a same-session create landing inside the frozen range cannot be surfaced from records already fetched.
• canPrefetch() - gate: NEXT bundle, LockType.NONE, not unique, no join, no external buffers, not multiplexed, cross-session dirty-share off. Opt-in via setPrefetchEnabled, set only by createSimpleQuery(), so FindQuery and FOR FIRST/LAST are untouched.
• fillPrefetch() - greedy fill across the bundle cascade using Persistence.list(fql, parms, want - fetched, 0). A generation guard refuses to publish a batch if this session changed the table while the fetch was in flight.
• Any retrieval that bypasses the window discards it, so a partially drained window cannot sit behind the walk and replay records.
• The window is owned by the AdaptiveQuery, not the worker. createCompoundQuery() is deliberately left unbatched.

Alex, regarding the prefetched window in OE. When a reader walks a batch and another session alters records in that batch or inserts/deleted records in that batch, the reader does not see it. When the reader alters the prefetched records, the changes are visible, but if another record is inserted in the bracket in the same session, that record is not visible, it is never read. If the record is inserted in a batch that will be read, it will be visible. Basically the whole window is a snapshot that cannot be changed by other sessions in any way and can only be updated by the same session, no deletes or inserts.

Artur, please let me know if you can create a unit test suite or, better, a 2 sessions harness to illustrate all the findings from #11723. I want to confirm with solid tests that:

• this behavior is happening only for NO-LOCK FOR EACH queries and blocks. for SHARE-LOCK and EXCLUSIVE-LOCK it does not.
  • is this happening for SCROLLING queries that can eventually do PREVIOUS? If so, doe the previous prefetch in descending order?
• the changes done in current transactions are reflected or not in the prefetch:
  • modifications are visible
  • inserts are not visible in the prefetch window
  • deletes are not visible in the sense that the prefetch window yields stale data.
• does transactions/rollback affect the prefetch?
  • If I do a full transaction while using a query with prefetch, does the prefetch still hold?
  • What if I do a prefetch over a transaction with changes (inserts/deletes/updates) and I rollback; does the prefetch still see the data it prefetched? I guess inserts/deletes are not affecting prefetch anyway, but what about updates?
• How is prefetch size window computed. I am interested on an approximation at least. Is prefetchNumRecs or is it computed in a more complex fashion.

Please also consider that the OE behavior might differ based on the reread-nolock startup parameter.

• how does this affect the findings on the prefetch?

TL;DR If the findings on prefetch are correct, it means that we don't need to invalidate eagerly the AdaptiveQuery. As long as the query uses a small preselected set of records, we can defer invalidation once every N records, instead of 1-by-1. This was implemented in #11652, but I wonder if I can regard it as a complete replacement of invalidation mechanism? In other words, I wonder if I can simply use PagedResults when query is invalidated instead of RAQ. If yes, then I can cut out the whole preselect/dynamic paradigm and use one single PagedResults that can increase the bracket size exponentially as ProgressiveResults and just reduce its size to 16 when an invalidation occurs. Also, I would need to understand if this happens only for forward-only no-lock queries.

Alexandru Lungu wrote:

Artur, please let me know if you can create a unit test suite or, better, a 2 sessions harness to illustrate all the findings from #11723.

Sure.

or, better, a 2 sessions harness to illustrate all the findings from #11723.

See Writing Multi-User Testcases.

OpenEdge record prefetch (message buffer): measured behaviour¶

On a client/server connection (-H/-S), a NO-LOCK read does not fetch one row at a time. The server packs a batch of rows into a network message and ships it ahead of the
  reader, which then serves iterations from that message without contacting the server. Changes another session commits to rows already in that batch are invisible until the batch is
  exhausted. This does not exist in shared-memory mode.

Measured on OpenEdge 11.6.3, -Mm 1024, otherwise default prefetch parameters. 89 two-session scenarios.

The mechanism¶

• The first response carries one row. Every message after it carries W rows, so boundaries fall at 1 + n·W.
• The client keeps exactly one message of read-ahead.
• W is a byte budget equal to -Mm:

  W = floor( -Mm / (wire_record_size + ~20) )
  

Verified across six record widths (61, 69, 78, 87, 96, 107 bytes gave W = 12, 11, 10, 9, 8, 7).

• The budget is spent on the wire record, not the stored row. Adding FIELDS(...) to project two narrow fields raised W from 8 to 26.
• -prefetchNumRecs (default 16, documented as a minimum) is not honoured against -Mm — every W above is below 16.

What switches it off completely¶

Locks are an absolute gate, not a reduction. Index uniqueness is irrelevant — unique secondary, non-unique secondary, unique primary and OpenEdge's own choice all gave an identical
  window.

What you see inside a window¶

Four things to be aware of¶

1. Sorted and preselected queries are the freshest, not the stalest. A query needing a results list fetches rows one at a time with no window at all. We predicted the opposite.
2. CURRENT-CHANGED cannot detect staleness. On a provably stale row it returns no, and only flips to yes after an explicit FIND CURRENT re-read. It compares against the last
   read, not the database.
3. The stale image is shared across buffers. A second, independent buffer doing its own FIND ... NO-LOCK on the same row also gets the old value. The window belongs to the
   connection, not the record buffer.
4. REPOSITION TO ROWID leaves the buffer unavailable with no error raised. You must GET NEXT afterwards. Repositioning also discards the window and can duplicate or truncate
   rows.

FIND CURRENT at any lock type refreshes a stale row. RELEASE does not. The reader's own transaction scoping makes no difference.

Tests added to support/harness/msgbuf/.

The stale image is shared across buffers. A second, independent buffer doing its own FIND ... NO-LOCK on the same row also gets the old value. The window belongs to the

connection, not the record buffer.

This is interesting. Does this mean that doing FIND FIRST, FIND NEXT, FIND NEXT would also prefetch? In other words, should we apply this prefetching for RAQ as well? For FOR EACH it is clear that yes and for PRESELECT is clear that no (as it preselect all rowids and then fetches each row either from session cache or database).

What about other kind of queries: compound query or presort query? Anything special about queries with OF keyword?

REPOSITION TO ROWID leaves the buffer unavailable with no error raised. You must GET NEXT afterwards. Repositioning also discards the window and can duplicate or truncate rows

I think we need more exploration on this. But before reaching REPOSITION, we need to understand if prefetch applies to SCROLLING. REPOSITION is the next question after understanding if SCROLLING prefetches (with previous and next repositioning).

After we confirm that scrolling is also subject to prefetching, then it means that browse queries may also prefetching (mind that queries attached to a browse are automatically NO-LOCK). So, if we scroll in a browse, OE is prefetching N records at once to be displayed by the browse.

• how will reposition work in the next N records?
  • If a record in the prefetch is deleted and we reposition to it, will it work finding the stale record?
  • If a new record is inserted within the prefetch window and we reposition to it, will it work? If so, if we do a NEXT afterward, we will get data from prefetch or is the prefetch dropped.
• how will reposition work outside the N records?
  • will it go to the target records and prefetch N rows afterward?
  • when repositioning, will the query run next, next, next until the target row is reached? If so, does this next, next, next iteration happen with prefetch.
    • if the target row is 100th one, will there be 100/16 network calls when repositioning?

After we find the answers for this, we can also ask myself about INDEXED-REPOSITION for #9724.

All measured on OpenEdge 11.6.3, client/server, -Mm 1024. New tests are in support/harness/msgbuf/ (TestFollowupReader / TestFollowupWriter, 7 scenarios, all passing).

Does FIND FIRST, FIND NEXT, FIND NEXT prefetch? Should we apply prefetching for RAQ?

No. A FIND NEXT sequence shows zero stale rows — every record after the writer's commit read the new value, both bare and with an index bracket. Each FIND is a server round trip, so prefetching should not be applied to RAQ.

What about compound query or presort query? Anything special about OF?

Joins prefetch normally — a two-buffer join gives the same 20-row stale window as a plain scan. Presort (an unindexed BY, i.e. a client-side sort) gives zero — like PRESELECT,it fetches row by row.

OF is not special at runtime: an OF join measured identically to the same join written with WHERE. It is special at compile time — it only compiles when it resolves to exactly one index. Against a two-index table it is rejected outright:

  More than one index found for bJoin OF dsUnique -- use WHERE, not OF. (446)
  

Before REPOSITION, does prefetch apply to SCROLLING?

Yes — and SCROLLING turns out to be the deciding factor. Same code, one keyword:

Before REPOSITION, does prefetch apply to SCROLLING?

Yes — and SCROLLING turns out to be the deciding factor. Same code, one keyword:

Correction to note #13: I reported the forward-only case as 1 stale row. It is 0 — a plain query handle does not prefetch at all. It behaves like FIND; only SCROLLING behaves like FOR EACH.

Does that mean browse queries prefetch?

By that result, yes — browse queries are NO-LOCK and scrolling. I could not measure a real browse: that needs an interactive or GUI session and these tests run CHUI batch. So strongly implied, not confirmed.

If a record in the prefetch window is deleted and we reposition to it, will it find the stale record?

No. The buffer comes back unavailable, with no error raised. The stale copy is not reachable by rowid.

If a record is inserted within the window and we reposition to it, will it work? And does a following NEXT come from prefetch?

No, and this is the one case that raises an error. The reader can find the new row through a separate buffer, but repositioning onto its rowid gives available=no and error=yes, and the following GET NEXT returns nothing — the scan ends there. Repositioning onto a row that is not in the query's result set kills the query rather than dropping the window.

Repositioning outside the N records — does it go to the target and prefetch N rows afterward?

It reaches the target, and no new window forms. After repositioning past the window the landed row was fresh (old window discarded), and a second concurrent update was then visible on the very next row — one stale row, not a window. So prefetch does not re-establish after a reposition.

When repositioning, does the query walk next-next-next to the target? If the target row is the 100th, will there be 100/16 network calls?

The observation logs show record contents, not round trips, so this needs server-side message counters (_ActServer VSTs sampled before and after, in a single session so the counts are clean) comparing a reposition to row 2 against row 100. If the delta scales with distance it walks; if it is constant it seeks. Will investigate.

Yes — and SCROLLING turns out to be the deciding factor. Same code, one keyword:
forward-only DEFINE QUERY + GET NEXT 0
SCROLLING + GET NEXT 20

I am not sure I understand. I thought the forward-only QUERY + GET NEXT should work like a FOR EACH, thus it should use prefetching (and have stale records). But It seems like it does not and SCROLLING does that? This is a bit confusing.

Correction to note #13: I reported the forward-only case as 1 stale row. It is 0 — a plain query handle does not prefetch at all. It behaves like FIND; only SCROLLING behaves like FOR EACH.

• "a plain query handle" (i.e. dynamic) is scrolling by default. You need to make it forward-only explicitly.
  • on contrast, a static query is forward-only by default. You need to make it scrolling explicitly.

If you confirm that SCROLLING is prefetching, then I will need way more consideration (i.e. get prev, get first I guess are still served by the in-memory cursor).

By that result, yes — browse queries are NO-LOCK and scrolling. I could not measure a real browse: that needs an interactive or GUI session and these tests run CHUI batch. So strongly implied, not confirmed.

Strongly implied is not enough.

Repositioning onto a row that is not in the query's result set kills the query rather than dropping the window.

What do you mean by "kills the query"? I doubt it closes it.

It reaches the target, and no new window forms. After repositioning past the window the landed row was fresh (old window discarded), and a second concurrent update was then visible on the very next row — one stale row, not a window. So prefetch does not re-establish after a reposition.

I hoped the opposite. A reposition of a scrolling query does one-by-one next operation, so I am not sure why this design would be chosen by OE. I mean, it would be more convenient to use a bunch of nexts than use REPOSITION in this case, because you will benefit from prefetching at least. Then, there is the INDEXED-REPOSITION question that I am more intrigued in. If you reposition using INDEXED-REPOSITION query, will a prefetch window occur?

The observation logs show record contents, not round trips, so this needs server-side message counters (_ActServer VSTs sampled before and after, in a single session so the counts are clean) comparing a reposition to row 2 against row 100. If the delta scales with distance it walks; if it is constant it seeks. Will investigate.

Please do. Apply the same technique if possible to REPOSITION with and without INDEXED-REPOSITION.

• Is _meta subject to prefetching?

This is based on testcases in OE

Working on testcases for the other questions.

Can you post the examples used for:

• static DEFINE QUERY without SCROLLING + GET NEXT NO
• dynamic CREATE QUERY (FORWARD-ONLY yes or no) YES
• static DEFINE QUERY ... SCROLLING + GET NEXT YES

Here is the data from the round trip counter

So yes, meta is prefetched in the same way.

Mind that:

def temp-table tt field f1 as int.
def query q for tt.
open query q for each tt.
message query q:forward-only. // no

You need:

query q:forward-only = true

I know it is weird, but if you don't do this, then query is neither forward-only nor scrolling; it is simply "non-scrolling".

So, it is safe to assume that FORWARD-ONLY and SCROLLING static queries prefetches, while non-scrolling (no SCROLLING and no FORWARD-ONLY) is not prefetched.
Dyanmic queries always prefetch because they are SCROLLING by default and you can only make it forward-only.

So the practical rule is: prefetch is on unless the query has never committed to a mode, and only a static DEFINE QUERY can be in that condition.

So the practical rule is: prefetch is on unless the query has never committed to a mode, and only a static DEFINE QUERY can be in that condition.

This makes sense.

I didn't commit these to testcases yet.

Artur, please wrap up the effort for harness and unit tests. 11652a is close to an end and I will need something to test against to ensure there are no obvious problems before delivering.

FWD (trunk) versus OpenEdge 12.8 - record prefetch suite¶

Both engines run the same tests, the same database schema (tstcasesdb) and the same driver (harness.jar). Only the engine differs. OpenEdge: proserve tstcasesdb -S 20991 -Mm 1024. FWD: PostgreSQL on 5433, MSGBUF_ENGINE=fwd.

"Stale" is the number of consecutive rows that still read the OLD marker after the reader paused and the writer committed. Stale > 0 means the rows came out of a batch assembled before the writer ran.

Summary¶

One further class did not compile at all, so its 13 scenarios are not part of the 113 above - see the next section.

Did not compile¶

Every scenario¶

Tests added to support/harness/msgbuf.
Alex, please let me know when you have a branch to be tested.

Alex, please let me know when you have a branch to be tested.

For these tests, are they dependent upon prefetch size? If so, this is quite opaque to FWD - for some tables it is may be 2, for others 20. In FWD, I made it configurable and defaults to 16. I guess, as you mention, there is a formula intaking Mm (W = floor( -Mm / (wire_record_size + ~20) )), but wire_record_size is unknown to FWD.

Alexandru Lungu wrote:

For these tests, are they dependent upon prefetch size?

Mostly yes, but we can compare the prefetch window and decide for ourselves, a fail does not mean a functional issues necessarily, in most cases just the nr of stale records is different. More important is the presence (or not) of stale records.

Mostly yes, but we can compare the prefetch window and decide for ourselves, a fail does not mean a functional issues necessarily, in most cases just the nr of stale records is different. More important is the presence (or not) of stale records.

Is it possible to set -Mm to a very high value (e.g. 1M) and have prefetchNumRecs set to 16? In other words, can we replicate the FWD technique in OE to keep it deterministic?

I found that setting -Mm is more reliable than prefetchNumRecs, and if we compare round trips or row data, it will always be different in FWD compared to OE due to differences in architecture. Setting the prefetched records to 16 as default is not what OE does, -Mm is what dictates the prefetched data, since other factors can influence the actual nr of records, like records width or fields projection. Bottom line is that we need to prefetch when OE does, imo the actual nr of prefetched records can differ slightly.

Also I think -Mm has precedence above prefetchNumRecs, meaning that the size of the buffer will dictate how much data will be prefetched. prefetchNumRecs is not a hard limit.

Also I think -Mm has precedence above prefetchNumRecs, meaning that the size of the buffer will dictate how much data will be prefetched. prefetchNumRecs is not a hard limit.

Can you identify what is the role of prefetchNumRecs? To me it seems that it is a no-op is Mm dictates the number of prefetched records. I may have misunderstood.

Sure, I will do it with testcases since the documentation is not precise on this.

I found out why prefetchNumRecs was misbehaving

The argument -prefetchNumRecs is valid for Enterprise RDBMS licenses only. (14070)

I prefer to have a common ground between FWD and OE and that can be 64. But for default, I suspect OE users will have Mm set to default which will make the number of prefetched records somewhere like 12 as average (considering #11723-21, averaging the numbers there), which is within the complexity of a 16 upper-bounded. In other words, I prefer to do tests with a custom configuration in directory.xml as 64 as long as OE harness tests baseline are confirmed to have a 64 fixed prefetch if possible.