GetNext loops
Richard Kreiss
rkreiss at verizon.net
Tue Sep 22 16:13:09 PDT 2020
I have a report that does the does the same thing. In you NOT section make sure you are checking the same key value as well as any other fields which should cause a record to not be included.
My clients report runs against a file with 1,700 records each item does a lookup to see if there are order details. If so, the process looks at each detail ( any where from 1 to NNNNNN and the does some calculations. When run from a workstation, the report takes over 30 minutes to run. When run from the server(Windows Server 2012 to a Win 10 )4 bit pro) the report takes just under 21 minutes
Richard
Sent from my iPhone
> On Sep 22, 2020, at 3:15 PM, Richard D. Williams via Filepro-list <filepro-list at lists.celestial.com> wrote:
>
> You are not re-checking your key field match on each getnext instances.
>
> In order to control the looping lookup you must check the value of "ma" against the related field value in "tnts"
>
> i.e.
>
> 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: tnts(3) ne ma****Then: END*
>
>
> That is the piece you are missing.
>
> Richard D. Williams
>
>
>> On 9/22/2020 1:28 PM, Richard Veith via Filepro-list wrote:
>> 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>
>> _______________________________________________
>> Filepro-list mailing list
>> Filepro-list at lists.celestial.com
>> Subscribe/Unsubscribe/Subscription Changes
>> http://mailman.celestial.com/mailman/listinfo/filepro-list
>
>
>
> --
> This email has been checked for viruses by AVG.
> https://www.avg.com
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <http://mailman.celestial.com/pipermail/filepro-list/attachments/20200922/48beafeb/attachment.html>
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> Subscribe/Unsubscribe/Subscription Changes
> http://mailman.celestial.com/mailman/listinfo/filepro-list
More information about the Filepro-list
mailing list