GetNext loops

[Richard Veith]

In a lookup followed by a loop using GetNext, is there some limiting factor other than making sure all variables have a defined size, and that PFTOKSIZE is big enough?

Here is our situation.  We use FilePro 5.7 on a Windows PC (Windows 7 Professional, 64-bit OS, 16 GB ram), with no network involved.  Everything runs in a single machine.  We have a monthly process (executing from a batch file using DREPORT) that steps through some 19 million records, and for most of them does lookups to another FilePro database of nearly 30 million records, where the GetNext loop may find multiple, even hundreds, sometimes thousands, of relevant matches in the second DB.  Both files continually grow in size.  When we first ran into problems where the program would abort, after consulting this list, we made sure all the variables had defined sizes (which had not been true prior to that).  That solved our problem for a while.  Then as time went by, we ran into the problem again, and began increasing PFTOKSIZE (there is no automatic processing, and no CALLS or form processing in this program).  That worked up to a point, and then ceased to be effective.  For example, recently, the program would abort with a PFTOKSIZE of 200000 and about 298,400 records to go.  Increasing PFTOKSIZE to 250000 still resulted in an abort, but only about 270,000 records to go.  Increasing to 400000 resulted in an abort with only about 86,000 records to go.  And further increases had no effect.  And there is nothing wrong with the records near the end of the file, because if we split the 19 million records in half so to speak (i.e., two selection sets), each half runs fine.

To test the suspicion that it is related to the number of times GetNext is executed, we added a counter to the loop, and discovered that if, for any single record in the 19 million, we do no more than about 750 loops, the program would not abort.  But between that and 765 loops, it will still abort.

To test this even more, I wrote a simple program that was devoid of all else except the GetNext looping, and it is included below.  The program is executed using DREPORT.  Whether PFTOKSIZE is 600000 or 800000, with the maximum number of loops set at 800, it always aborts with about 16,900,400 records left to go out of about 19,500,000.  Assuming that on most records it really is doing 800 loops, it seems to do about 2 billion loops before dying.  The Windows ‘error’ message simply says “dreport.exe has stopped working.”

Any suggestions?

  1  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
       ◄ If: ' 2020-09-16  Test of excessive looping
       Then: ' -------------------------------------
  2  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
       ◄ If:
       Then: declare cnt(9,.0)  ' counter
  3  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
       ◄ If:
       Then: ma(20,*) = 7  'match string (w/ zip as a starting point)
  4  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
       ◄ If: '  for the purposes of this test, not requiring an exact match
       Then: lookup tnts = rt17  k=ma i=0 -NG
  5  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
loop   ◄ If: not tnts
      Then: END
  6  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
       ◄ If:
       Then: cnt = cnt + "1"
  7  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
       ◄ If: cnt GT "800"
       Then: END
  8  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
       ◄ If:
       Then: getnext tnts; goto loop
  9  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
       ◄ If:
       Then: END

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.celestial.com/pipermail/filepro-list/attachments/20200922/80569746/attachment.html>