Filepro-list Digest, Vol 200, Issue 26
Jay R. Ashworth
jra at baylink.com
Wed Sep 23 20:02:34 PDT 2020
Oh dear. My apologies for an earlier reply, whomever got it...
Cheers,
-- jra
----- Original Message -----
> From: "Bill Randall via Filepro-list" <filepro-list at lists.celestial.com>
> To: filepro-list at lists.celestial.com, "Stuart Werner" <sllw32 at gmail.com>
> Sent: Wednesday, September 23, 2020 3:05:15 PM
> Subject: Re: Filepro-list Digest, Vol 200, Issue 26
> We send our condolences to the family and friends of Stuart Werner.
> His many years of educating filePro users at our conferences was invaluable.
> We have lost a wonderful man and a great friend to the entire filePro
> community.
>
> Bill
>
> On 9/22/2020 9:47 PM, Stuart Werner via Filepro-list wrote:
>> 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
>
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> Subscribe/Unsubscribe/Subscription Changes
> http://mailman.celestial.com/mailman/listinfo/filepro-list
--
Jay R. Ashworth Baylink jra at baylink.com
Designer The Things I Think RFC 2100
Ashworth & Associates http://www.bcp38.info 2000 Land Rover DII
St Petersburg FL USA BCP38: Ask For It By Name! +1 727 647 1274
More information about the Filepro-list
mailing list