Question Anatomy Of A Static Query

Andy_S

New Member
Hi this is a very basic question and I should know this, but I can't see the clear answer in any manual I have read.

Take a fairly simple static query in the 4GL. This was from the sports2000 db.

FOR EACH Customer
WHERE comments CONTAINS "hold" NO-LOCK:

/* Point A */

END.


Now lets say there are 1,000 records that match. The word index brackets nicely.

At Point A on the first iteration, what work has been done on the server in regard to records brought into the database buffer pool?
Specifically, have all the matching records been called into the database buffer pool by now?
Have all the index blocks been read, so all the results of the search identified by the database manager?
Are we just waiting now for each iteration of the loop to send the record to the client AVM?

Or at point A on each iteration, is that where the record is located in the db buffer pool/read from disk as needed?

I'm fine about the I/O and scanning of the buffer pool work, I'll take that for granted it happens.
And for now I'm not fussed about using a dynamic query. Although it would be nice to know how many results I have before pulling records over to the client.

Thanks in advance
 

zerovian

New Member
That isn't really a query. That is a for each block, which is going to read records from db blocks one at a time. The database loads blocks of records, not single records. So if two records are the same block you won't get two physical reads, you'll get one physical read, and the next will simply read the next record. You've also get index blocks to think about too.

But that isn't a query. A query looks like this:

define query q for customer.
open query q for each customer where ...

But in your example...no, the database is only going to read records when it needs to based on which blocks are pointed to by the index. Running either of these examples multiple times will have different results based on how much -B is available. If the result set is small enough, then the second run won't have any physical reads, since the database buffer pool will already have those blocks in memory.

This is much more comparable to a dynamic query. You also have to account for the preselect phrase, -Mm setting or direct memory connection.

--MB fixed a typo in second setence
 
Last edited:

Andy_S

New Member
So on iteration 2 of the for each loop, in this example, it implies that the index blocks still have to be examined for all the other records in the match.....

Is that correct?

And at this point on iteration 2, the only records that have been requested to be available in the buffer pool are the first two in the match....

Correct here?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
All reads and writes go through the buffer pool(s). But the unit of I/O is a block, not a record or an index key.

Let's say the customer table is in a Type II area configured for a maximum of 64 records per block. And for the sake of simplicity let's say the customer records are small enough that that many customer records fit in each record block, and their physical order more or less matches the index order (not always the case obviously).

To resolve a query, the runtime determines which index(es) are required to retrieve the record(s) and determines the required bracket on that index. It reads the index, starting at the root block and going down to the leaf level of the index B-tree, to the starting point of that bracket. Each of the index blocks visited in turn is read into the appropriate buffer pool, primary or alternate. (I won't discuss alternate buffer pool any further to avoid going off track.) The first index block at the leaf level contains the first set of ROWIDs of the records to be retrieved.

In the first iteration of the FOR block, the record block containing the first customer record in the bracket is accessed, reading it into the buffer pool if it isn't already there. At Point A in the second iteration, the next index key is read to determine the ROWID of the next record and that record is read. Very likely neither of these logical reads resulted in a physical read. A single index block may contain dozens or hundreds of index keys or more, depending on key size and compression ratio. So the second index key read would likely have been in the same index block as the first, and that's already in the buffer pool. And as per the stated assumptions, the second record is likely in the same record block as the first one, and that's already in the buffer pool too. As to when the record buffer is provided to the client, as zerovian said it depends on many factors including client configuration (shared memory versus client/server), database configuration (e.g. use of -prefetch* startup parameters), and how the code is written (whether the query is a prefetch query or not).

Often times the above assumptions do not hold true. A table may have many indexes but its records have only one physical order on disk. So sequential reads in index order may not come from the same or adjacent blocks. But as more record blocks are read into the buffer pool, assuming it is reasonably sized and recently-read data isn't being evicted, the probability increases that subsequent block accesses result in cache hits. But no, the database server doesn't prefetch all of the index blocks that the query may require into the buffer pool and then start accessing the records. They are accessed on demand, as the FOR block iterates; index block, record block, index block, record block, etc.
 

Andy_S

New Member
Okay thanks that's helped. It was the point about the index keys for required records being read one at a time, retrieve the record, next index key etc .

As opposed to read all the index keys first and now start retrieving one record after the other.
Thx
 
Top