Filepro-list Digest, Vol 200, Issue 26
Stuart Werner
sllw32 at gmail.com
Tue Sep 22 18:47:27 PDT 2020
Please remove sllw32.gmail.com from your mailing list
|-----Original Message-----
|From: Filepro-list On Behalf Of filepro-list-request at lists.celestial.com
|Sent: Tuesday, September 22, 2020 3:22 PM
|To: filepro-list at lists.celestial.com
|Subject: Filepro-list Digest, Vol 200, Issue 26
|
|Send Filepro-list mailing list submissions to
| filepro-list at lists.celestial.com
|
|To subscribe or unsubscribe via the World Wide Web, visit
| http://mailman.celestial.com/mailman/listinfo/filepro-list
|or, via email, send a message with subject or body 'help' to
| filepro-list-request at lists.celestial.com
|
|You can reach the person managing the list at
| filepro-list-owner at lists.celestial.com
|
|When replying, please edit your Subject line so it is more specific
|than "Re: Contents of Filepro-list digest..."
|
|
|Today's Topics:
|
| 1. GetNext loops (Richard Veith)
| 2. RE: index rebuilds (Richard Kreiss)
| 3. Re: GetNext loops (Richard D. Williams)
|
|
|----------------------------------------------------------------------
|
|Message: 1
|Date: Tue, 22 Sep 2020 18:28:13 +0000
|From: Richard Veith <richard.veith at smrresearch.com>
|To: "filepro-list at lists.celestial.com"
| <filepro-list at lists.celestial.com>
|Subject: GetNext loops
|Message-ID:
| <MN2PR13MB3151B10F4352B8814690D57FEF3B0 at MN2PR13MB3151
|.namprd13.prod.outlook.com>
|
|Content-Type: text/plain; charset="utf-8"
|
|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 ex
| ample, 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>
|
|------------------------------
|
|Message: 2
|Date: Tue, 22 Sep 2020 18:59:37 +0000
|From: Richard Kreiss <rkreiss at gccconsulting.net>
|To: "'filepro-list at lists.celestial.com'"
| <filepro-list at lists.celestial.com>
|Subject: RE: index rebuilds
|Message-ID:
| <MN2PR04MB6767DAFCB74AC7E4A526A7CBB73B0 at MN2PR04MB67
|67.namprd04.prod.outlook.com>
|
|Content-Type: text/plain; charset="us-ascii"
|
|This may be an odd question to ask after over 30 years working with
filePro.
|
|Does anyone know if Auto processing runs during index rebuilds?
|
|I have a client who is trying to squeeze every last bit of performance out
of
|the SSD drive he just installed. I am not testing the environmental
variables
|associated with index rebuilds. FilePro support has not been very helpful
on
|this matter as I don't think they have a good understanding how these
|variables work.
|
|I am testing the following values:
|@echo off
|title Rebuld Marketing Indexes
|SET PFMAXalloc=="256"
|SET PfMAXTEMP="1500"
|SET PFNUMIXBUILD=500
|dxmaint l_patient_market -ra -e
|
|By the way he is running Windows Server 2012 and Window 10 clients using
|SSD drives on the server. He is using external storage connected to his
|backbone using a 10 GB/S connection. FilePro is 5.8.03.27.
|
|As I am not sure if the 4.1 indexes sorting is multi key or if this is a
waste as all
|of there indexes are multikey or have selection sets associated with the
auto
|index.
|
|PFBIXBUILD=2
|
|
|
|Tells dxmaint to use 4.1 style sorting during build whenever possible. Use
this
|on HUGE files to improve performance - on
|
|smaller files it will actually harm performance. (dxmaint)
|
|
|
|
|
|
|
|-------------- next part --------------
|A non-text attachment was scrubbed...
|Name: winmail.dat
|Type: application/ms-tnef
|Size: 13827 bytes
|Desc: not available
|URL: <http://mailman.celestial.com/pipermail/filepro-
|list/attachments/20200922/6f60adef/attachment.bin>
|
|
|------------------------------
|
|Message: 3
|Date: Tue, 22 Sep 2020 14:15:00 -0500
|From: "Richard D. Williams" <richard at appgrp.net>
|To: filepro-list at lists.celestial.com
|Subject: Re: GetNext loops
|Message-ID: <4cf467d0-b217-5921-03c5-26aa246c6729 at appgrp.net>
|Content-Type: text/plain; charset="utf-8"; Format="flowed"
|
|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>
|
|------------------------------
|
|Subject: Digest Footer
|
|_______________________________________________
|Filepro-list mailing list
|Filepro-list at lists.celestial.com
|http://mailman.celestial.com/mailman/listinfo/filepro-list
|
|
|------------------------------
|
|End of Filepro-list Digest, Vol 200, Issue 26
|*********************************************
--
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
More information about the Filepro-list
mailing list