Using BUFFER() on a loop over a file doesn't work?

[Daniel1]

@Bruce

(I’ll add the obligatory side note that the new drivers don’t have this problem because they don’t use cursors, and also have built-in paging support so limit the size of the result set the server needs to generate.)

Hello Bruce
does that means that new drivers can use such code

clear(my:record)
my:id = o:id
set(my:projKey,my:projKey)
loop until access:myfile.next() <> level:benign
  if my:id <> o:id then break.
  my:client_id = o:client_Id
  put(myfile)
end

safely? In other words, it won’t process all records but only those set by the key?
If so, then installing a new driver on the existing code would already bring significant benefits in terms of speed, especially when working through a local network and the program uses set(key,key) and the database is on the server.
Can you confirm that?

[Bruce]

@Daniel1

In other words, it won’t process all records but only those set by the key?

The short answer is “correct”.

( You’ve used my:projkey, but set and tested my:id, so I’m assuming my:id is the field in my:ProjKey)

The SET starts at the starting position (just like it always has) and then (internally) fetches a “page” of rows. If you NEXT past the end of that page it (internally) gets the next page, and so on.

Since you have an “end point” to the rows you are looping through, at most you’ll end up fetching only a page of records you don’t use.

@Daniel1

If so, then installing a new driver on the existing code would already bring significant benefits in terms of speed, especially when working through a local network and the program uses set(key,key) and the database is on the server.

Yes, Maybe. There’s no actual difference between how many rows travel across the lan (give or take a Page / Buffer). And of course if the table is small (not too many rows after where you BREAK) then the code is going to be pretty fast either way.

The main difference is actually on the server side, not so much on the client side. With the old driver the server has to build a cursor of the whole result set (maybe lots of extra rows), and that consumes time and memory.

The new driver does not make a cursor, so it consumes fewer resources on the server. Also because the server knows it only has to build the first page, browses open faster.

To sum up, where it is currently fast, it’ll likely be the same, or marginally quicker. Where it is currently slow it is likely to be a LOT quicker.

[Koen]

@Rick_UpperPark

That’s what set(key,key) does.
It takes the current values of the columns in the key and positions to the first row in the table that is equal to or greater than those key values.

Of course I knew that, but apparently I was to tired to realise how set(key,key) works yesterday

@Bruce

Using prop:Where allows you to specify not just the START of the result set, but also the END of the result set.

Now finally the coin drops why using PROP:Where is a better choice.

Thank you both for clarifying this.

[Rick_UpperPark]

I almost always declare a VIEW on my table and then use prop:order and prop:filter to set exactly what I want. BUFFER works on a VIEW, too.

[Bruce]

New drivers let you do that at the file level, no need to make the View.
They also support prop:project, so you can limit the fields being retrieved