Filepro-list Digest, Vol 200, Issue 26
Bill Randall
breadran01 at gmail.com
Wed Sep 23 12:05:15 PDT 2020
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
More information about the Filepro-list
mailing list