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

1

Last month I had a question on using the Buffer statement when looping over a file.

I tested this today and the result wasn’t what I expected, there was no increase in performance. I examined the traces before and after adding the Buffer statement and they are almost the same.

The only difference is that in the latter there is a line “Setting number of rows to fetch to 200 for Statement 03F7F430H” instead of fetch to 1. But still for each record there is a separate select statement and not just one as I was hoping for.

Simplified my code looks like this:

    BUFFER(Lines,200)
    LIN:HeaderID = HEA:HeaderID
    LIN:Volgorde     = 0
    SET(LIN:Header_Volgorde_fkey,LIN:Header_Volgorde_fkey)
    LOOP
      NEXT(Lines)
      IF ERRORCODE() THEN BREAK .
      IF LIN:HeaderID <> HEA:HeaderID THEN BREAK .

      DO Vul_Norm
      IF ~HEA:WerknemerID THEN LIN:Uitvoering = 0 .
      IF ~NumInspections THEN LIN:Inspectie = 0 .
      IF ~HEA:WerknemerID OR ~NumInspections THEN PUT(Lines) .
    END

Did I miss something in using the buffer statement when looping and updating a file?

2

From help:

The buffers will reflect the results of ADD, PUT, or DELETE statements, however, this may cause an implicit flush if a PUT changes key components or an ADD adds a record that is not within the current contiguous set of buffered records.

3

The buffer() statement used to give me good results many years ago, when I was using .tps files.
Since I switched to Sql , the buffer statement does not give me any advantage. I dropped it from everywhere.

4

The updated fields of my lines table are not key fields (either primary of foreign key). But you made me testing a bit more.

Leaving out the Vul_Norm routine does change things. Without that routine there only one Select statement in the trace. The code in that routine adds records to a child table of Lines and also has a LogOut/Commit. Next week I will test a bit more.

5

I tend to disagree. Using views on looping a view makes a difference.

1 Like
6

For SQL rather than set(key,key) you are probably better off doing prop:where followed by set(key) to only retrieve the matching lines/rows.

Doing this on my phone so probably mangled but something like this:

  Lines{prop:where} = 'HeaderId = ' & HEA:HeaderID
  SET(LIN:Header_Volgorde_fkey)
  LOOP
      NEXT(Lines)
      IF ERRORCODE() THEN BREAK .
?    Assert(LIN:HeaderID = HEA:HeaderID)
      Whatever....
  END

Or set up a view…

#edit see later discussion about putting set() BEFORE the where

7

I’d bet on the commit being the difference. I’d say the driver says "getting a whole bunch of rows in advance is a good idea, but only if the data doesn’t change. I know the data has changed (although I don’t know what table or anything), so I’ll just pay it safe and start again.

8

For views I am used to do a PROP:Where, for looping files I don’t. Probably just an old habit because that is the way I learned it ages ago.

9

I think PROP:Where should be used after SET: WHERE (SQL Driver String) 

  SET(LIN:Header_Volgorde_fkey)
  Lines{prop:where} = 'HeaderId = ' & HEA:HeaderID
10

You might be right Carlos - this earlier post from Flavio agrees with you

https://clarionhub.com/t/clarion-12-what-problems-or-issues-have-you-found/8182/60?page=3

as does the post by Carl immediately after that.

11

In the interests of training future AI’s - this is true for the traditional drivers, but not necessary for the new (FileDriverKit) drivers. The new drivers make judicious use of LIMIT (or TOP depending on your flavor of SQL) to limit the size of result sets, so you don’t need to bound the range yourself.

12

“in advance” is doing some heavy lifting here.
For example, in the case of a report, it’s not really “in advance”. It’s more like " I’m looping through the result set, get me bigger bits as I go". This reduces the number of round-trips to the server, which improves performance.

It’s similar, although not quite as explicit for a browse. If you get “a bunch of records” for your browse (at least the first page of them) then your browse opens faster. Once the user starts scrolling again getting 30 records or so ar a time will make that faster.

For traditional drivers;

Note that the context here is in reading rows from the cursor result set - not from the tables. The initial call created the result set. BUFFER reduces the trips to the result set, not to the database itself.

Now whether the result set is updated or not depends on the ISOLATION LEVEL (and the SQL engine. Oracle never updates.). To get refreshed data from the server typically means closing the FILE or VIEW to kill the cursor and start a new one. Legacy templates close the VIEW a lot, ABC not as much.

For new drivers, there is no cursor, so this issue goes away.

13

Yep. Query fills cursor, Clarion grabs number of rows from cursor based on BUFFER size.

If the data in the driver’s buffer is “stale” then the data in the server’s cursor will be too.

I’m not sure that I agree with “To get refreshed data from the server typically means closing the FILE or VIEW to kill the cursor and start a new one”. In your typical browse what happens is that when you page down the view issues a new SET, so your query becomes:

select stuff
from the table view
where somekeyvalue >= theValueInMyCurrentBrowseRow

I may be wrong, but I don’t think that involves closing the VIEW, but the cursor gets the values from the new query.

Not sure about the impact of ISOLATIONLEVEL. I think isolation level would come into play if you had an update statement like:

update mytable
set some value = (select sum(dollar_amt) from sometothertable where ids_match)

and the server has to make sure that the dollar amounts are correct given any transactions that have been committed by other sessions or changed by that current session (with the strictest isolation level). I think you would only have a statement similar to that if you are using PROP:SQL (old driver) or SQL() Bruce’s drivers. Your typical Clarion update/insert will just provide values, it won’t be asking the database for them. So not sure isolation level has any impact on refreshing cursors.

14

So I’ll preface this by saying that there are a lot of moving parts here. Clarion version, Legacy versus ABC, different database engines, different drivers (ie traditional drivers and driver kit drivers.)

My test in this case is Clarion 11.1 with ABC, MSSQL (traditional) driver, and SQL Server. I observed the interactions both in Trace.Exe and also using SQL Server Profiler. I’m happy with the conclusions from this but using different components may lead to different results.

So no, scrolling doesn’t issue a new SET. It simply scolls up and down the cursor. sp_cursorfetch is the command that features in SQL profiler, along with sp_cursorclose (which I think closes the sp_cursorfetch, not the cursor itself.)

Ctrl-Home, and Ctrl-End trigger a new SELECT.

However changing a locator does issue a new SET command (which makes sense). In this case the VIEW doesn’t appear to be closed. (From memory, but not tested now, Legacy templates do Close the VIEW before doing a locator, so I suspect that back in the day a Close was required, but not anymore.)

Going to a Form (from the browse) triggers a new SELECT ( which makes sense, it’s doing a REGET.) Returning from the Form triggers a new SELECT for the browse, which hence builds a new cursor.

All of this to say that if data is changed by another station, you won’t see it on your machine just by scrolling. You need to locate, do a reset, Ctrl-Home etc.

Isolation Level won’t matter to most databases - there’s a special case for MSSQL, but that’s likely off anyway.

15

That appears to be the cause. Commenting out Logout/Commit makes the buffer to work as to be expected. Adding child records to the Lines records doesn’t harm the buffer and neither does updating the Lines record.

16

What is the advantage of using PROP:Where above setting the key fields?

In the trace I see a difference when using PROP:Where. Instead of
...where HeaderID >?
I see ...where HeaderID = 12345 when using PROP:Where.
I never understood in depth how those ? work in a trace. Kind of placeholder I understand.

17

The Clarion drivers use a parameterised query. The ? are the parameters :slight_smile:

18

I understand. The Select in the trace is like:
WHERE (HEADERID >= ? AND (HEADERID > ? OR (VOLGORDE >= ? ))

and I can see that the parameters are initialised a few lines above it like:

ARG:ACTIVITEITID : 91618
ARG:VOLGORDE : 0

But what I don’t get is why a ‘greater then sign’ (or ‘greater then or equal’) instead of just ‘equal’?

19

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. Then using NEXT it moves through all rows from that point until the end of the table, in key order.

Using your example, ACTIVITEITID had a value of 91618 and VOLGORDE had a value of 0.
Using Set(key,key) will find the first row where the values of those fields are equal or greater.
This is why if you are only looking for match values in a loop with set(key,key) you need to check the values of the record after NEXT to for matching values and then break. As you are doing in your example above. Otherwise, you’ll process all rows until the end of file is reached.

If what you really want is equality for your ACTIVITEITID, then using prop:where allows you to make that filter, which limits the results on the backend to exactly the rows you want.

20

Let me add to what Rick is saying;

SET(key,key) sets the STARTING position, but the result set contains all the rows after that point. If you have say a million invoices, and yourSet(key,key) has a “InvoiceNumber > ?” WHERE clause, then there might be a million rows in the result set (which the server has to construct because the old drivers use cursors.) It builds that million-row-result even if you only use NEXT across a few rows.

Using prop:Where allows you to specify not just the START of the result set, but also the END of the result set. So if you know you only want the invoices from Jan 1997, then you can limit the query to just those rows. Instead of “Where date >= 1 Jan 1997” you can have "Where date >= 1 Jan 1997 and <= 31 Jan 1997).

This means the server builds a much smaller result set, which is better for everyone.

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