<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal">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?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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.”<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Any suggestions?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"> 1 ------- - - - - - - - - - - - - - - - -<o:p></o:p></p>
<p class="MsoNormal"> <span style="font-family:"Arial",sans-serif">◄</span> If: ' 2020-09-16 Test of excessive looping<o:p></o:p></p>
<p class="MsoNormal"> Then: ' -------------------------------------<o:p></o:p></p>
<p class="MsoNormal"> 2 ------- - - - - - - - - - - - - - - - -<o:p></o:p></p>
<p class="MsoNormal"> <span style="font-family:"Arial",sans-serif">◄</span> If:<o:p></o:p></p>
<p class="MsoNormal"> Then: declare cnt(9,.0) ' counter<o:p></o:p></p>
<p class="MsoNormal"> 3 ------- - - - - - - - - - - - - - - - -<o:p></o:p></p>
<p class="MsoNormal"> <span style="font-family:"Arial",sans-serif">◄</span> If:<o:p></o:p></p>
<p class="MsoNormal"> Then: ma(20,*) = 7 'match string (w/ zip as a starting point)<o:p></o:p></p>
<p class="MsoNormal"> 4 ------- - - - - - - - - - - - - - - - -<o:p></o:p></p>
<p class="MsoNormal"> <span style="font-family:"Arial",sans-serif">◄</span> If: ' for the purposes of this test, not requiring an exact match<o:p></o:p></p>
<p class="MsoNormal"> Then: lookup tnts = rt17 k=ma i=0 -NG<o:p></o:p></p>
<p class="MsoNormal"> 5 ------- - - - - - - - - - - - - - - - -<o:p></o:p></p>
<p class="MsoNormal">loop <span style="font-family:"Arial",sans-serif">◄</span> If: not tnts<o:p></o:p></p>
<p class="MsoNormal"> Then: END<o:p></o:p></p>
<p class="MsoNormal"> 6 ------- - - - - - - - - - - - - - - - -<o:p></o:p></p>
<p class="MsoNormal"> <span style="font-family:"Arial",sans-serif">◄</span> If:<o:p></o:p></p>
<p class="MsoNormal"> Then: cnt = cnt + "1"<o:p></o:p></p>
<p class="MsoNormal"> 7 ------- - - - - - - - - - - - - - - - -<o:p></o:p></p>
<p class="MsoNormal"> <span style="font-family:"Arial",sans-serif">◄</span> If: cnt GT "800"<o:p></o:p></p>
<p class="MsoNormal"> Then: END<o:p></o:p></p>
<p class="MsoNormal"> 8 ------- - - - - - - - - - - - - - - - -<o:p></o:p></p>
<p class="MsoNormal"> <span style="font-family:"Arial",sans-serif">◄</span> If:<o:p></o:p></p>
<p class="MsoNormal"> Then: getnext tnts; goto loop<o:p></o:p></p>
<p class="MsoNormal"> 9 ------- - - - - - - - - - - - - - - - -<o:p></o:p></p>
<p class="MsoNormal"> <span style="font-family:"Arial",sans-serif">◄</span> If:<o:p></o:p></p>
<p class="MsoNormal"> Then: END<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>