Just got the Stats for February, 2024 and when the ProFox List Statistics for January is one of the top posts for the month of February, I realized I've been busy to comment and I assume everyone else has been busy too, so consider this a kick-off post for March to help improve or at least stimulate some conversation in here!
So, last month, I took the plunge and purchased a copy of VFPA 10.2. I have a software app I wrote a long time ago to help telemarketers comply with the Telemarketing Sales Rule by scrubbing their call lists against the National Do Not Call List sold by the Federal Trade Commission. The program (which I creatively named Call List Scrubber) was originally written in VFP6 and just like VFP, it is a mature and stable product requiring little maintenance, is easy to use and is easy to support.
That having been said, the full National Do Not Call List has grown to almost 3Gb in size and while we changed several years ago from using FREADs to using the Scripting.FileSystemObject, up until now we could import and store the information in a DBF and didn't exceed 2Gb by using several tricks not the least of which was storing the area codes as a 3 byte text field and the 7 digit number as an 4byte integer. Oh, and I built the original program to use IDX files instead of compound NDX files so that was another trick that helped us over the years as we dealt with the unceasing growth in the National Do Not Call List.
While most of our users only purchase individual area code DNC lists from the FTC, in January we had a new client who did purchase the full national list and you guessed it, Call List Scrubber could no longer import the full National DNC list and function. So, I helped the client split the file into two roughly equal portions, set up two installs for them and they are able to function and use the product for now. But..That meant I needed to find some alternative and permanent solution. One choice and the one that most of you would probably recommend would be for me to upsize to a SQL database or perhaps use SQLite. But that would require almost a full re-write of the product and I don't really have time for that at the moment and more importantly, I don't have the motivation to do a full re-write at this time (perhaps later), so I started casting about for solutions and VFPAdvanced came to mind.
I reached out to Chen via email and asked a few questions about how to implement this product which he graciously answered and I ultimately purchased version 10.2 from him to test and to play with to see if the large file fix would work in my application. The installation went very smoothly after I upgraded my installation per his instructions. I easily set up the program to run side by side with VFP9 and I have continued working in VFP9 for the majority of my work and I simply setup a new startup shortcut to use when I want to use VFPA10 instead. Works completely as advertised and does not crossover into my standard installation in any way.
Now that I've completed my initial testing I'd like to share some of my results and invite some comments:
First, to answer the obvious question: Yes, when I use VFPA, I can import the full National DNC file with 250Million + telephone numbers into my program without any other changes beyond recompiling with VFPA 10.2. After importing the NDNC text file (~3Gb) and stripping off the not used characters like commas, spaces, etc; the resulting finished DBF is 2.004Gb, the Area Code IDX is 1.083Gb and the full 10 digit number index is 1.750GB.
Second, I did several tests with VFPA and found if I split the list into 10 separate area code files (0-100, 100-200, 200-300, etc), it took about twice as long to import and populate those tables as it did to import the full file into one DBF. Though I may need to do more testing on this idea but it seems like a convoluted solution to me so I didn't go too far with it.
Third, the best performance came from a buffered import where I import the text data in batches. Each record in the NDNC file is 12 bytes long and looks like this: "999,9999999LF", so using the Scripting.FileSystemObject, I'm importing 12^6 characters (248,832 records at a time) and then I use ALINES to convert the buffered characters to an array and then insert each of the array "rows" into the DBF. This method allows me to import a grand total of 250 Million telephone numbers on the Do Not Call List, populate a DBF in excess of 2GB and update two IDX files at the same time in just under 4 hours. Some of that work was done across my internal network so I might be able to pick up some extra time by running solely on an SSD, but if someone has other ideas of how to import, populate and update IDX files any faster, please let me know.
Fourth, VFPA performs very well in my tests. I have found nothing that isn't compatible with the original VFP6 code and I suspect I won't find any major differences in VFP9 code because I'm using both VFP6 and VFP9 class libraries in my tests and everything works as expected. Chen has been responsive to my questions and released an update even as I was writing this. I'm not sure I'm ready to cut over to VFPA for my primary development work, but this is looking like a great option in this particular situation.
Finally, there is one issue that concerns me at this point and that is with memory usage. During an import test like the ones I've described here, I see memory usage by VFP and the Large Memory module that Chen provides grow from the 500Mb I usually see when running one of my VFP applications to 2 to 2.5 GB. I have a suspicion this is caused by the two IDX files I have open while doing the import. I'm opening the IDX files with the DBF file so that they are updated as the data is inserted into the DBF. This may not be the best way to do things because perhaps recreating the indexes after the import is complete may be faster, BUT, I'm not sure how to provide user feedback on the progress of the indexing process and maybe I just need someone to guide me on this point. I'll have to test doing the import without the IDX files open to confirm my thoughts about the memory usage during the import, but the most important issue is that the memory that gets allocated during the import process isn't released when I close the table and the indexes. In fact, it isn't released at all until I close VFPA and restart it. Does anyone have an insight into this memory usage weirdness I'm seeing?
Ok, I know this is a long email, but I'm trying to start some conversation and there are several points I think y'all can help me with so have at it.
Any feedback is helpful for this situation let me know what you know!
Paul H. Tarver Tarver Program Consultants, Inc.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
Hey there Paul,
Interesting write-up, and it was a good read this morning.
Will admit - I have NOT Touched VFP in a while. AS I've had my head down in the 3DP world for a while now. In fact, life kinda went to hell again for me - as I lost my job last July - co. imploded - letting almost EVERYONE go from the Essentium site in Irvine, CA - whilst only letting a couple folks go from HQ in TX. And, it was miserable - a little over 6 months unemployed. But, luckily I got a new gig - also in Irvine - and I am actually doing 3D Design work for the job, as well as heavily working with 3D Printers. In fact, I had thrown the idea around about doing a small database application for them. But, so far, I have not really dived into that...
Yeah, I suspect that every time you import a record - it has to do a Re-org of that IDX file - and the building of it would probably be Much faster after importing all data.
I'm curious...
Doesn't VFP normally give some kinda of a screen updating when building an IDX file? And, if so - maybe you could use THAT as a way to show the user what is happening!
It's just a thought - and mostly a WAG.
Goodluck!
-Kurt
On Fri, Mar 1, 2024 at 10:52 AM Paul H. Tarver paul@tpcqpc.com wrote:
Just got the Stats for February, 2024 and when the ProFox List Statistics for January is one of the top posts for the month of February, I realized I've been busy to comment and I assume everyone else has been busy too, so consider this a kick-off post for March to help improve or at least stimulate some conversation in here!
So, last month, I took the plunge and purchased a copy of VFPA 10.2. I have a software app I wrote a long time ago to help telemarketers comply with the Telemarketing Sales Rule by scrubbing their call lists against the National Do Not Call List sold by the Federal Trade Commission. The program (which I creatively named Call List Scrubber) was originally written in VFP6 and just like VFP, it is a mature and stable product requiring little maintenance, is easy to use and is easy to support.
That having been said, the full National Do Not Call List has grown to almost 3Gb in size and while we changed several years ago from using FREADs to using the Scripting.FileSystemObject, up until now we could import and store the information in a DBF and didn't exceed 2Gb by using several tricks not the least of which was storing the area codes as a 3 byte text field and the 7 digit number as an 4byte integer. Oh, and I built the original program to use IDX files instead of compound NDX files so that was another trick that helped us over the years as we dealt with the unceasing growth in the National Do Not Call List.
While most of our users only purchase individual area code DNC lists from the FTC, in January we had a new client who did purchase the full national list and you guessed it, Call List Scrubber could no longer import the full National DNC list and function. So, I helped the client split the file into two roughly equal portions, set up two installs for them and they are able to function and use the product for now. But..That meant I needed to find some alternative and permanent solution. One choice and the one that most of you would probably recommend would be for me to upsize to a SQL database or perhaps use SQLite. But that would require almost a full re-write of the product and I don't really have time for that at the moment and more importantly, I don't have the motivation to do a full re-write at this time (perhaps later), so I started casting about for solutions and VFPAdvanced came to mind.
I reached out to Chen via email and asked a few questions about how to implement this product which he graciously answered and I ultimately purchased version 10.2 from him to test and to play with to see if the large file fix would work in my application. The installation went very smoothly after I upgraded my installation per his instructions. I easily set up the program to run side by side with VFP9 and I have continued working in VFP9 for the majority of my work and I simply setup a new startup shortcut to use when I want to use VFPA10 instead. Works completely as advertised and does not crossover into my standard installation in any way.
Now that I've completed my initial testing I'd like to share some of my results and invite some comments:
First, to answer the obvious question: Yes, when I use VFPA, I can import the full National DNC file with 250Million + telephone numbers into my program without any other changes beyond recompiling with VFPA 10.2. After importing the NDNC text file (~3Gb) and stripping off the not used characters like commas, spaces, etc; the resulting finished DBF is 2.004Gb, the Area Code IDX is 1.083Gb and the full 10 digit number index is 1.750GB.
Second, I did several tests with VFPA and found if I split the list into 10 separate area code files (0-100, 100-200, 200-300, etc), it took about twice as long to import and populate those tables as it did to import the full file into one DBF. Though I may need to do more testing on this idea but it seems like a convoluted solution to me so I didn't go too far with it.
Third, the best performance came from a buffered import where I import the text data in batches. Each record in the NDNC file is 12 bytes long and looks like this: "999,9999999LF", so using the Scripting.FileSystemObject, I'm importing 12^6 characters (248,832 records at a time) and then I use ALINES to convert the buffered characters to an array and then insert each of the array "rows" into the DBF. This method allows me to import a grand total of 250 Million telephone numbers on the Do Not Call List, populate a DBF in excess of 2GB and update two IDX files at the same time in just under 4 hours. Some of that work was done across my internal network so I might be able to pick up some extra time by running solely on an SSD, but if someone has other ideas of how to import, populate and update IDX files any faster, please let me know.
Fourth, VFPA performs very well in my tests. I have found nothing that isn't compatible with the original VFP6 code and I suspect I won't find any major differences in VFP9 code because I'm using both VFP6 and VFP9 class libraries in my tests and everything works as expected. Chen has been responsive to my questions and released an update even as I was writing this. I'm not sure I'm ready to cut over to VFPA for my primary development work, but this is looking like a great option in this particular situation.
Finally, there is one issue that concerns me at this point and that is with memory usage. During an import test like the ones I've described here, I see memory usage by VFP and the Large Memory module that Chen provides grow from the 500Mb I usually see when running one of my VFP applications to 2 to 2.5 GB. I have a suspicion this is caused by the two IDX files I have open while doing the import. I'm opening the IDX files with the DBF file so that they are updated as the data is inserted into the DBF. This may not be the best way to do things because perhaps recreating the indexes after the import is complete may be faster, BUT, I'm not sure how to provide user feedback on the progress of the indexing process and maybe I just need someone to guide me on this point. I'll have to test doing the import without the IDX files open to confirm my thoughts about the memory usage during the import, but the most important issue is that the memory that gets allocated during the import process isn't released when I close the table and the indexes. In fact, it isn't released at all until I close VFPA and restart it. Does anyone have an insight into this memory usage weirdness I'm seeing?
Ok, I know this is a long email, but I'm trying to start some conversation and there are several points I think y'all can help me with so have at it.
Any feedback is helpful for this situation let me know what you know!
Paul H. Tarver Tarver Program Consultants, Inc.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
There is a status line that displays the indexing progress, but that doesn't show up in my compiled app where I rely on progress bars to give the user feedback. I tried building the IDX without the COMPACT option and while the indexes are huge, there was no improvement on the speed of building them, so I'm probably going to go back to using the COMPACT option as I think Rushmore likes that better. Since there is no time benefit either way, I'll go with the smaller indexes.
I also had a chance to test building the indexes after the import. The import took less than 30 minutes, but the index process is the definitely the slow part. But this also proved that the Indexes were NOT the cause of the large allocation of memory used during the import. I'm not sure what is causing that since I'm releasing the memory variable and the array I'm importing the text into after writing the data to the DBF, but the process ends up allocating between 3-4Gb of RAM before it finishes and it only released when I shut VFP down. It works, but is weird since I'm not using that much to process each batch and that is with the Indexes closed so I'm sure they aren't the problem.
Working in this environment with such large chunks of data has been an experience for sure!
Glad you are back at work albeit in something exciting like 3DP! :)
Paul H. Tarver Tarver Program Consultants, Inc.
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Kurt Wendt Sent: Monday, March 04, 2024 9:55 AM To: profoxtech@leafe.com Subject: Re: VFPAdvanced - My Problem, My Tests & Some Results
Hey there Paul,
Interesting write-up, and it was a good read this morning.
Will admit - I have NOT Touched VFP in a while. AS I've had my head down in the 3DP world for a while now. In fact, life kinda went to hell again for me - as I lost my job last July - co. imploded - letting almost EVERYONE go from the Essentium site in Irvine, CA - whilst only letting a couple folks go from HQ in TX. And, it was miserable - a little over 6 months unemployed. But, luckily I got a new gig - also in Irvine - and I am actually doing 3D Design work for the job, as well as heavily working with 3D Printers. In fact, I had thrown the idea around about doing a small database application for them. But, so far, I have not really dived into that...
Yeah, I suspect that every time you import a record - it has to do a Re-org of that IDX file - and the building of it would probably be Much faster after importing all data.
I'm curious...
Doesn't VFP normally give some kinda of a screen updating when building an IDX file? And, if so - maybe you could use THAT as a way to show the user what is happening!
It's just a thought - and mostly a WAG.
Goodluck!
-Kurt
On Fri, Mar 1, 2024 at 10:52 AM Paul H. Tarver paul@tpcqpc.com wrote:
Just got the Stats for February, 2024 and when the ProFox List Statistics for January is one of the top posts for the month of February, I realized I've been busy to comment and I assume everyone else has been busy too, so consider this a kick-off post for March to help improve or at least stimulate some conversation in here!
So, last month, I took the plunge and purchased a copy of VFPA 10.2. I have a software app I wrote a long time ago to help telemarketers comply with the Telemarketing Sales Rule by scrubbing their call lists against the National Do Not Call List sold by the Federal Trade Commission. The program (which I creatively named Call List Scrubber) was originally written in VFP6 and just like VFP, it is a mature and stable product requiring little maintenance, is easy to use and is easy to support.
That having been said, the full National Do Not Call List has grown to almost 3Gb in size and while we changed several years ago from using FREADs to using the Scripting.FileSystemObject, up until now we could import and store the information in a DBF and didn't exceed 2Gb by using several tricks not the least of which was storing the area codes as a 3 byte text field and the 7 digit number as an 4byte integer. Oh, and I built the original program to use IDX files instead of compound NDX files so that was another trick that helped us over the years as we dealt with the unceasing growth in the National Do Not Call List.
While most of our users only purchase individual area code DNC lists from the FTC, in January we had a new client who did purchase the full national list and you guessed it, Call List Scrubber could no longer import the full National DNC list and function. So, I helped the client split the file into two roughly equal portions, set up two installs for them and they are able to function and use the product for now. But..That meant I needed to find some alternative and permanent solution. One choice and the one that most of you would probably recommend would be for me to upsize to a SQL database or perhaps use SQLite. But that would require almost a full re-write of the product and I don't really have time for that at the moment and more importantly, I don't have the motivation to do a full re-write at this time (perhaps later), so I started casting about for solutions and VFPAdvanced came to mind.
I reached out to Chen via email and asked a few questions about how to implement this product which he graciously answered and I ultimately purchased version 10.2 from him to test and to play with to see if the large file fix would work in my application. The installation went very smoothly after I upgraded my installation per his instructions. I easily set up the program to run side by side with VFP9 and I have continued working in VFP9 for the majority of my work and I simply setup a new startup shortcut to use when I want to use VFPA10 instead. Works completely as advertised and does not crossover into my standard installation in any way.
Now that I've completed my initial testing I'd like to share some of my results and invite some comments:
First, to answer the obvious question: Yes, when I use VFPA, I can import the full National DNC file with 250Million + telephone numbers into my program without any other changes beyond recompiling with VFPA 10.2. After importing the NDNC text file (~3Gb) and stripping off the not used characters like commas, spaces, etc; the resulting finished DBF is 2.004Gb, the Area Code IDX is 1.083Gb and the full 10 digit number index is 1.750GB.
Second, I did several tests with VFPA and found if I split the list into 10 separate area code files (0-100, 100-200, 200-300, etc), it took about twice as long to import and populate those tables as it did to import the full file into one DBF. Though I may need to do more testing on this idea but it seems like a convoluted solution to me so I didn't go too far with it.
Third, the best performance came from a buffered import where I import the text data in batches. Each record in the NDNC file is 12 bytes long and looks like this: "999,9999999LF", so using the Scripting.FileSystemObject, I'm importing 12^6 characters (248,832 records at a time) and then I use ALINES to convert the buffered characters to an array and then insert each of the array "rows" into the DBF. This method allows me to import a grand total of 250 Million telephone numbers on the Do Not Call List, populate a DBF in excess of 2GB and update two IDX files at the same time in just under 4 hours. Some of that work was done across my internal network so I might be able to pick up some extra time by running solely on an SSD, but if someone has other ideas of how to import, populate and update IDX files any faster, please let me know.
Fourth, VFPA performs very well in my tests. I have found nothing that isn't compatible with the original VFP6 code and I suspect I won't find any major differences in VFP9 code because I'm using both VFP6 and VFP9 class libraries in my tests and everything works as expected. Chen has been responsive to my questions and released an update even as I was writing this. I'm not sure I'm ready to cut over to VFPA for my primary development work, but this is looking like a great option in this particular situation.
Finally, there is one issue that concerns me at this point and that is with memory usage. During an import test like the ones I've described here, I see memory usage by VFP and the Large Memory module that Chen provides grow from the 500Mb I usually see when running one of my VFP applications to 2 to 2.5 GB. I have a suspicion this is caused by the two IDX files I have open while doing the import. I'm opening the IDX files with the DBF file so that they are updated as the data is inserted into the DBF. This may not be the best way to do things because perhaps recreating the indexes after the import is complete may be faster, BUT, I'm not sure how to provide user feedback on the progress of the indexing process and maybe I just need someone to guide me on this point. I'll have to test doing the import without the IDX files open to confirm my thoughts about the memory usage during the import, but the most important issue is that the memory that gets allocated during the import process isn't released when I close the table and the indexes. In fact, it isn't released at all until I close VFPA and restart it. Does anyone have an insight into this memory usage weirdness I'm seeing?
Ok, I know this is a long email, but I'm trying to start some conversation and there are several points I think y'all can help me with so have at it.
Any feedback is helpful for this situation let me know what you know!
Paul H. Tarver Tarver Program Consultants, Inc.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Thank you Paul for your kind words.
One suggestion - why not suggest that the Import process is run at the End of the Day - and have the User start it up and just walk away for the night. Then - NO NEED to have any kind of Status updates on the screen - as nobody is watching it!
Also, an alternate option - suggest client run this on a machine not in use - and it can run unattended.
The idea behind both suggestions is simply then you need not truly worry about screen updating and a status.
Anyway - its just a thought...
Yeah - my new job is truly my Dream job - its FUN - and I enjoy going to work every day - and I get to run printers, and even get to do 3D Design work!
When work is FUN -- Then LIFE is GOOD!!!
My Motto - Trademark Pending...
-K
On Mon, Mar 4, 2024 at 12:35 PM Paul H. Tarver paul@tpcqpc.com wrote:
There is a status line that displays the indexing progress, but that doesn't show up in my compiled app where I rely on progress bars to give the user feedback. I tried building the IDX without the COMPACT option and while the indexes are huge, there was no improvement on the speed of building them, so I'm probably going to go back to using the COMPACT option as I think Rushmore likes that better. Since there is no time benefit either way, I'll go with the smaller indexes.
I also had a chance to test building the indexes after the import. The import took less than 30 minutes, but the index process is the definitely the slow part. But this also proved that the Indexes were NOT the cause of the large allocation of memory used during the import. I'm not sure what is causing that since I'm releasing the memory variable and the array I'm importing the text into after writing the data to the DBF, but the process ends up allocating between 3-4Gb of RAM before it finishes and it only released when I shut VFP down. It works, but is weird since I'm not using that much to process each batch and that is with the Indexes closed so I'm sure they aren't the problem.
Working in this environment with such large chunks of data has been an experience for sure!
Glad you are back at work albeit in something exciting like 3DP! :)
Paul H. Tarver Tarver Program Consultants, Inc.
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Kurt Wendt Sent: Monday, March 04, 2024 9:55 AM To: profoxtech@leafe.com Subject: Re: VFPAdvanced - My Problem, My Tests & Some Results
Hey there Paul,
Interesting write-up, and it was a good read this morning.
Will admit - I have NOT Touched VFP in a while. AS I've had my head down in the 3DP world for a while now. In fact, life kinda went to hell again for me - as I lost my job last July - co. imploded - letting almost EVERYONE go from the Essentium site in Irvine, CA - whilst only letting a couple folks go from HQ in TX. And, it was miserable - a little over 6 months unemployed. But, luckily I got a new gig - also in Irvine - and I am actually doing 3D Design work for the job, as well as heavily working with 3D Printers. In fact, I had thrown the idea around about doing a small database application for them. But, so far, I have not really dived into that...
Yeah, I suspect that every time you import a record - it has to do a Re-org of that IDX file - and the building of it would probably be Much faster after importing all data.
I'm curious...
Doesn't VFP normally give some kinda of a screen updating when building an IDX file? And, if so - maybe you could use THAT as a way to show the user what is happening!
It's just a thought - and mostly a WAG.
Goodluck!
-Kurt
On Fri, Mar 1, 2024 at 10:52 AM Paul H. Tarver paul@tpcqpc.com wrote:
Just got the Stats for February, 2024 and when the ProFox List Statistics for January is one of the top posts for the month of February, I realized I've been busy to comment and I assume everyone else has been busy too,
so
consider this a kick-off post for March to help improve or at least stimulate some conversation in here!
So, last month, I took the plunge and purchased a copy of VFPA 10.2. I
have
a software app I wrote a long time ago to help telemarketers comply with the Telemarketing Sales Rule by scrubbing their call lists against the
National
Do Not Call List sold by the Federal Trade Commission. The program
(which I
creatively named Call List Scrubber) was originally written in VFP6 and just like VFP, it is a mature and stable product requiring little maintenance, is easy to use and is easy to support.
That having been said, the full National Do Not Call List has grown to almost 3Gb in size and while we changed several years ago from using
FREADs
to using the Scripting.FileSystemObject, up until now we could import and store the information in a DBF and didn't exceed 2Gb by using several tricks not the least of which was storing the area codes as a 3 byte text field and the 7 digit number as an 4byte integer. Oh, and I built the original program to use IDX files instead of compound NDX files so that was another trick that helped us over the years as we dealt with the unceasing growth in
the
National Do Not Call List.
While most of our users only purchase individual area code DNC lists from the FTC, in January we had a new client who did purchase the full
national
list and you guessed it, Call List Scrubber could no longer import the
full
National DNC list and function. So, I helped the client split the file
into
two roughly equal portions, set up two installs for them and they are
able
to function and use the product for now. But..That meant I needed to find some alternative and permanent solution. One choice and the one that most of you would probably recommend would be for me to upsize to a SQL database
or
perhaps use SQLite. But that would require almost a full re-write of the product and I don't really have time for that at the moment and more importantly, I don't have the motivation to do a full re-write at this
time
(perhaps later), so I started casting about for solutions and VFPAdvanced came to mind.
I reached out to Chen via email and asked a few questions about how to implement this product which he graciously answered and I ultimately purchased version 10.2 from him to test and to play with to see if the large file fix would work in my application. The installation went very
smoothly
after I upgraded my installation per his instructions. I easily set up
the
program to run side by side with VFP9 and I have continued working in
VFP9
for the majority of my work and I simply setup a new startup shortcut to use when I want to use VFPA10 instead. Works completely as advertised and
does
not crossover into my standard installation in any way.
Now that I've completed my initial testing I'd like to share some of my results and invite some comments:
First, to answer the obvious question: Yes, when I use VFPA, I can import the full National DNC file with 250Million + telephone numbers into my program without any other changes beyond recompiling with VFPA 10.2.
After
importing the NDNC text file (~3Gb) and stripping off the not used characters like commas, spaces, etc; the resulting finished DBF is
2.004Gb,
the Area Code IDX is 1.083Gb and the full 10 digit number index is 1.750GB.
Second, I did several tests with VFPA and found if I split the list into
10
separate area code files (0-100, 100-200, 200-300, etc), it took about twice as long to import and populate those tables as it did to import the full file into one DBF. Though I may need to do more testing on this idea but
it
seems like a convoluted solution to me so I didn't go too far with it.
Third, the best performance came from a buffered import where I import
the
text data in batches. Each record in the NDNC file is 12 bytes long and looks like this: "999,9999999LF", so using the
Scripting.FileSystemObject,
I'm importing 12^6 characters (248,832 records at a time) and then I use ALINES to convert the buffered characters to an array and then insert
each
of the array "rows" into the DBF. This method allows me to import a grand total of 250 Million telephone numbers on the Do Not Call List, populate
a
DBF in excess of 2GB and update two IDX files at the same time in just under 4 hours. Some of that work was done across my internal network so I might be able to pick up some extra time by running solely on an SSD, but if
someone
has other ideas of how to import, populate and update IDX files any
faster,
please let me know.
Fourth, VFPA performs very well in my tests. I have found nothing that isn't compatible with the original VFP6 code and I suspect I won't find any
major
differences in VFP9 code because I'm using both VFP6 and VFP9 class libraries in my tests and everything works as expected. Chen has been responsive to my questions and released an update even as I was writing this. I'm not sure I'm ready to cut over to VFPA for my primary
development
work, but this is looking like a great option in this particular situation.
Finally, there is one issue that concerns me at this point and that is
with
memory usage. During an import test like the ones I've described here, I see memory usage by VFP and the Large Memory module that Chen provides grow from the 500Mb I usually see when running one of my VFP applications to 2 to
2.5
GB. I have a suspicion this is caused by the two IDX files I have open while doing the import. I'm opening the IDX files with the DBF file so that
they
are updated as the data is inserted into the DBF. This may not be the
best
way to do things because perhaps recreating the indexes after the import
is
complete may be faster, BUT, I'm not sure how to provide user feedback on the progress of the indexing process and maybe I just need someone to
guide
me on this point. I'll have to test doing the import without the IDX
files
open to confirm my thoughts about the memory usage during the import, but the most important issue is that the memory that gets allocated during
the
import process isn't released when I close the table and the indexes. In fact, it isn't released at all until I close VFPA and restart it. Does anyone have an insight into this memory usage weirdness I'm seeing?
Ok, I know this is a long email, but I'm trying to start some
conversation
and there are several points I think y'all can help me with so have at
it.
Any feedback is helpful for this situation let me know what you know!
Paul H. Tarver Tarver Program Consultants, Inc.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
I think that is still why I'm programming in VFP. It is fun to still be learning new stuff about this language and what I can do with it after all these years! :)
Enjoy your new role but don't leave VFP too far behind!
PS: I understand your comments about set and forget with no updates and if this were an in-house project that I was doing and not a retail product, I'd probably take your advice! But paying customers demand, so we try to achieve.
Paul H. Tarver Tarver Program Consultants, Inc.
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Kurt Wendt Sent: Tuesday, March 05, 2024 10:20 PM To: profoxtech@leafe.com Subject: Re: VFPAdvanced - My Problem, My Tests & Some Results
Thank you Paul for your kind words.
One suggestion - why not suggest that the Import process is run at the End of the Day - and have the User start it up and just walk away for the night. Then - NO NEED to have any kind of Status updates on the screen - as nobody is watching it!
Also, an alternate option - suggest client run this on a machine not in use - and it can run unattended.
The idea behind both suggestions is simply then you need not truly worry about screen updating and a status.
Anyway - its just a thought...
Yeah - my new job is truly my Dream job - its FUN - and I enjoy going to work every day - and I get to run printers, and even get to do 3D Design work!
When work is FUN -- Then LIFE is GOOD!!!
My Motto - Trademark Pending...
-K
On Mon, Mar 4, 2024 at 12:35 PM Paul H. Tarver paul@tpcqpc.com wrote:
There is a status line that displays the indexing progress, but that doesn't show up in my compiled app where I rely on progress bars to give the user feedback. I tried building the IDX without the COMPACT option and while the indexes are huge, there was no improvement on the speed of building them, so I'm probably going to go back to using the COMPACT option as I think Rushmore likes that better. Since there is no time benefit either way, I'll go with the smaller indexes.
I also had a chance to test building the indexes after the import. The import took less than 30 minutes, but the index process is the definitely the slow part. But this also proved that the Indexes were NOT the cause of the large allocation of memory used during the import. I'm not sure what is causing that since I'm releasing the memory variable and the array I'm importing the text into after writing the data to the DBF, but the process ends up allocating between 3-4Gb of RAM before it finishes and it only released when I shut VFP down. It works, but is weird since I'm not using that much to process each batch and that is with the Indexes closed so I'm sure they aren't the problem.
Working in this environment with such large chunks of data has been an experience for sure!
Glad you are back at work albeit in something exciting like 3DP! :)
Paul H. Tarver Tarver Program Consultants, Inc.
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Kurt Wendt Sent: Monday, March 04, 2024 9:55 AM To: profoxtech@leafe.com Subject: Re: VFPAdvanced - My Problem, My Tests & Some Results
Hey there Paul,
Interesting write-up, and it was a good read this morning.
Will admit - I have NOT Touched VFP in a while. AS I've had my head down in the 3DP world for a while now. In fact, life kinda went to hell again for me - as I lost my job last July - co. imploded - letting almost EVERYONE go from the Essentium site in Irvine, CA - whilst only letting a couple folks go from HQ in TX. And, it was miserable - a little over 6 months unemployed. But, luckily I got a new gig - also in Irvine - and I am actually doing 3D Design work for the job, as well as heavily working with 3D Printers. In fact, I had thrown the idea around about doing a small database application for them. But, so far, I have not really dived into that...
Yeah, I suspect that every time you import a record - it has to do a Re-org of that IDX file - and the building of it would probably be Much faster after importing all data.
I'm curious...
Doesn't VFP normally give some kinda of a screen updating when building an IDX file? And, if so - maybe you could use THAT as a way to show the user what is happening!
It's just a thought - and mostly a WAG.
Goodluck!
-Kurt
On Fri, Mar 1, 2024 at 10:52 AM Paul H. Tarver paul@tpcqpc.com wrote:
Just got the Stats for February, 2024 and when the ProFox List Statistics for January is one of the top posts for the month of February, I realized I've been busy to comment and I assume everyone else has been busy too,
so
consider this a kick-off post for March to help improve or at least stimulate some conversation in here!
So, last month, I took the plunge and purchased a copy of VFPA 10.2. I
have
a software app I wrote a long time ago to help telemarketers comply with the Telemarketing Sales Rule by scrubbing their call lists against the
National
Do Not Call List sold by the Federal Trade Commission. The program
(which I
creatively named Call List Scrubber) was originally written in VFP6 and just like VFP, it is a mature and stable product requiring little maintenance, is easy to use and is easy to support.
That having been said, the full National Do Not Call List has grown to almost 3Gb in size and while we changed several years ago from using
FREADs
to using the Scripting.FileSystemObject, up until now we could import and store the information in a DBF and didn't exceed 2Gb by using several tricks not the least of which was storing the area codes as a 3 byte text field and the 7 digit number as an 4byte integer. Oh, and I built the original program to use IDX files instead of compound NDX files so that was another trick that helped us over the years as we dealt with the unceasing growth in
the
National Do Not Call List.
While most of our users only purchase individual area code DNC lists from the FTC, in January we had a new client who did purchase the full
national
list and you guessed it, Call List Scrubber could no longer import the
full
National DNC list and function. So, I helped the client split the file
into
two roughly equal portions, set up two installs for them and they are
able
to function and use the product for now. But..That meant I needed to find some alternative and permanent solution. One choice and the one that most of you would probably recommend would be for me to upsize to a SQL database
or
perhaps use SQLite. But that would require almost a full re-write of the product and I don't really have time for that at the moment and more importantly, I don't have the motivation to do a full re-write at this
time
(perhaps later), so I started casting about for solutions and VFPAdvanced came to mind.
I reached out to Chen via email and asked a few questions about how to implement this product which he graciously answered and I ultimately purchased version 10.2 from him to test and to play with to see if the large file fix would work in my application. The installation went very
smoothly
after I upgraded my installation per his instructions. I easily set up
the
program to run side by side with VFP9 and I have continued working in
VFP9
for the majority of my work and I simply setup a new startup shortcut to use when I want to use VFPA10 instead. Works completely as advertised and
does
not crossover into my standard installation in any way.
Now that I've completed my initial testing I'd like to share some of my results and invite some comments:
First, to answer the obvious question: Yes, when I use VFPA, I can import the full National DNC file with 250Million + telephone numbers into my program without any other changes beyond recompiling with VFPA 10.2.
After
importing the NDNC text file (~3Gb) and stripping off the not used characters like commas, spaces, etc; the resulting finished DBF is
2.004Gb,
the Area Code IDX is 1.083Gb and the full 10 digit number index is 1.750GB.
Second, I did several tests with VFPA and found if I split the list into
10
separate area code files (0-100, 100-200, 200-300, etc), it took about twice as long to import and populate those tables as it did to import the full file into one DBF. Though I may need to do more testing on this idea but
it
seems like a convoluted solution to me so I didn't go too far with it.
Third, the best performance came from a buffered import where I import
the
text data in batches. Each record in the NDNC file is 12 bytes long and looks like this: "999,9999999LF", so using the
Scripting.FileSystemObject,
I'm importing 12^6 characters (248,832 records at a time) and then I use ALINES to convert the buffered characters to an array and then insert
each
of the array "rows" into the DBF. This method allows me to import a grand total of 250 Million telephone numbers on the Do Not Call List, populate
a
DBF in excess of 2GB and update two IDX files at the same time in just under 4 hours. Some of that work was done across my internal network so I might be able to pick up some extra time by running solely on an SSD, but if
someone
has other ideas of how to import, populate and update IDX files any
faster,
please let me know.
Fourth, VFPA performs very well in my tests. I have found nothing that isn't compatible with the original VFP6 code and I suspect I won't find any
major
differences in VFP9 code because I'm using both VFP6 and VFP9 class libraries in my tests and everything works as expected. Chen has been responsive to my questions and released an update even as I was writing this. I'm not sure I'm ready to cut over to VFPA for my primary
development
work, but this is looking like a great option in this particular situation.
Finally, there is one issue that concerns me at this point and that is
with
memory usage. During an import test like the ones I've described here, I see memory usage by VFP and the Large Memory module that Chen provides grow from the 500Mb I usually see when running one of my VFP applications to 2 to
2.5
GB. I have a suspicion this is caused by the two IDX files I have open while doing the import. I'm opening the IDX files with the DBF file so that
they
are updated as the data is inserted into the DBF. This may not be the
best
way to do things because perhaps recreating the indexes after the import
is
complete may be faster, BUT, I'm not sure how to provide user feedback on the progress of the indexing process and maybe I just need someone to
guide
me on this point. I'll have to test doing the import without the IDX
files
open to confirm my thoughts about the memory usage during the import, but the most important issue is that the memory that gets allocated during
the
import process isn't released when I close the table and the indexes. In fact, it isn't released at all until I close VFPA and restart it. Does anyone have an insight into this memory usage weirdness I'm seeing?
Ok, I know this is a long email, but I'm trying to start some
conversation
and there are several points I think y'all can help me with so have at
it.
Any feedback is helpful for this situation let me know what you know!
Paul H. Tarver Tarver Program Consultants, Inc.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Hey Paul,
Completely understood why my suggestion would not work in your case. And, WOW - I'm Thrilled to hear that Alan's suggestion blew you away - and put your data processing into Turbo Speed Mode!
And, yeah - I still may at some point create a VFP app for work.
-K
On Wed, Mar 6, 2024 at 2:55 PM Paul H. Tarver paul@tpcqpc.com wrote:
I think that is still why I'm programming in VFP. It is fun to still be learning new stuff about this language and what I can do with it after all these years! :)
Enjoy your new role but don't leave VFP too far behind!
PS: I understand your comments about set and forget with no updates and if this were an in-house project that I was doing and not a retail product, I'd probably take your advice! But paying customers demand, so we try to achieve.
Paul H. Tarver Tarver Program Consultants, Inc.
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Kurt Wendt Sent: Tuesday, March 05, 2024 10:20 PM To: profoxtech@leafe.com Subject: Re: VFPAdvanced - My Problem, My Tests & Some Results
Thank you Paul for your kind words.
One suggestion - why not suggest that the Import process is run at the End of the Day - and have the User start it up and just walk away for the night. Then - NO NEED to have any kind of Status updates on the screen - as nobody is watching it!
Also, an alternate option - suggest client run this on a machine not in use
- and it can run unattended.
The idea behind both suggestions is simply then you need not truly worry about screen updating and a status.
Anyway - its just a thought...
Yeah - my new job is truly my Dream job - its FUN - and I enjoy going to work every day - and I get to run printers, and even get to do 3D Design work!
When work is FUN -- Then LIFE is GOOD!!!
My Motto - Trademark Pending...
-K
On Mon, Mar 4, 2024 at 12:35 PM Paul H. Tarver paul@tpcqpc.com wrote:
There is a status line that displays the indexing progress, but that doesn't show up in my compiled app where I rely on progress bars to give the user feedback. I tried building the IDX without the COMPACT option
and
while the indexes are huge, there was no improvement on the speed of building them, so I'm probably going to go back to using the COMPACT
option
as I think Rushmore likes that better. Since there is no time benefit either way, I'll go with the smaller indexes.
I also had a chance to test building the indexes after the import. The import took less than 30 minutes, but the index process is the definitely the slow part. But this also proved that the Indexes were NOT the cause
of
the large allocation of memory used during the import. I'm not sure what
is
causing that since I'm releasing the memory variable and the array I'm importing the text into after writing the data to the DBF, but the
process
ends up allocating between 3-4Gb of RAM before it finishes and it only released when I shut VFP down. It works, but is weird since I'm not using that much to process each batch and that is with the Indexes closed so
I'm
sure they aren't the problem.
Working in this environment with such large chunks of data has been an experience for sure!
Glad you are back at work albeit in something exciting like 3DP! :)
Paul H. Tarver Tarver Program Consultants, Inc.
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Kurt Wendt Sent: Monday, March 04, 2024 9:55 AM To: profoxtech@leafe.com Subject: Re: VFPAdvanced - My Problem, My Tests & Some Results
Hey there Paul,
Interesting write-up, and it was a good read this morning.
Will admit - I have NOT Touched VFP in a while. AS I've had my head down
in
the 3DP world for a while now. In fact, life kinda went to hell again for me - as I lost my job last July - co. imploded - letting almost EVERYONE
go
from the Essentium site in Irvine, CA - whilst only letting a couple
folks
go from HQ in TX. And, it was miserable - a little over 6 months unemployed. But, luckily I got a new gig - also in Irvine - and I am actually doing 3D Design work for the job, as well as heavily working
with
3D Printers. In fact, I had thrown the idea around about doing a small database application for them. But, so far, I have not really dived into that...
Yeah, I suspect that every time you import a record - it has to do a Re-org of that IDX file - and the building of it would probably be Much faster after importing all data.
I'm curious...
Doesn't VFP normally give some kinda of a screen updating when building
an
IDX file? And, if so - maybe you could use THAT as a way to show the user what is happening!
It's just a thought - and mostly a WAG.
Goodluck!
-Kurt
On Fri, Mar 1, 2024 at 10:52 AM Paul H. Tarver paul@tpcqpc.com wrote:
Just got the Stats for February, 2024 and when the ProFox List
Statistics
for January is one of the top posts for the month of February, I
realized
I've been busy to comment and I assume everyone else has been busy too,
so
consider this a kick-off post for March to help improve or at least stimulate some conversation in here!
So, last month, I took the plunge and purchased a copy of VFPA 10.2. I
have
a software app I wrote a long time ago to help telemarketers comply
with
the Telemarketing Sales Rule by scrubbing their call lists against the
National
Do Not Call List sold by the Federal Trade Commission. The program
(which I
creatively named Call List Scrubber) was originally written in VFP6 and just like VFP, it is a mature and stable product requiring little
maintenance,
is easy to use and is easy to support.
That having been said, the full National Do Not Call List has grown to almost 3Gb in size and while we changed several years ago from using
FREADs
to using the Scripting.FileSystemObject, up until now we could import
and
store the information in a DBF and didn't exceed 2Gb by using several tricks not the least of which was storing the area codes as a 3 byte text
field
and the 7 digit number as an 4byte integer. Oh, and I built the original program to use IDX files instead of compound NDX files so that was another
trick
that helped us over the years as we dealt with the unceasing growth in
the
National Do Not Call List.
While most of our users only purchase individual area code DNC lists
from
the FTC, in January we had a new client who did purchase the full
national
list and you guessed it, Call List Scrubber could no longer import the
full
National DNC list and function. So, I helped the client split the file
into
two roughly equal portions, set up two installs for them and they are
able
to function and use the product for now. But..That meant I needed to
find
some alternative and permanent solution. One choice and the one that
most
of you would probably recommend would be for me to upsize to a SQL
database
or
perhaps use SQLite. But that would require almost a full re-write of
the
product and I don't really have time for that at the moment and more importantly, I don't have the motivation to do a full re-write at this
time
(perhaps later), so I started casting about for solutions and
VFPAdvanced
came to mind.
I reached out to Chen via email and asked a few questions about how to implement this product which he graciously answered and I ultimately purchased version 10.2 from him to test and to play with to see if the large file fix would work in my application. The installation went very
smoothly
after I upgraded my installation per his instructions. I easily set up
the
program to run side by side with VFP9 and I have continued working in
VFP9
for the majority of my work and I simply setup a new startup shortcut
to
use when I want to use VFPA10 instead. Works completely as advertised and
does
not crossover into my standard installation in any way.
Now that I've completed my initial testing I'd like to share some of my results and invite some comments:
First, to answer the obvious question: Yes, when I use VFPA, I can
import
the full National DNC file with 250Million + telephone numbers into my program without any other changes beyond recompiling with VFPA 10.2.
After
importing the NDNC text file (~3Gb) and stripping off the not used characters like commas, spaces, etc; the resulting finished DBF is
2.004Gb,
the Area Code IDX is 1.083Gb and the full 10 digit number index is 1.750GB.
Second, I did several tests with VFPA and found if I split the list
into
10
separate area code files (0-100, 100-200, 200-300, etc), it took about twice as long to import and populate those tables as it did to import the
full
file into one DBF. Though I may need to do more testing on this idea
but
it
seems like a convoluted solution to me so I didn't go too far with it.
Third, the best performance came from a buffered import where I import
the
text data in batches. Each record in the NDNC file is 12 bytes long and looks like this: "999,9999999LF", so using the
Scripting.FileSystemObject,
I'm importing 12^6 characters (248,832 records at a time) and then I
use
ALINES to convert the buffered characters to an array and then insert
each
of the array "rows" into the DBF. This method allows me to import a
grand
total of 250 Million telephone numbers on the Do Not Call List,
populate
a
DBF in excess of 2GB and update two IDX files at the same time in just under 4 hours. Some of that work was done across my internal network so I
might
be able to pick up some extra time by running solely on an SSD, but if
someone
has other ideas of how to import, populate and update IDX files any
faster,
please let me know.
Fourth, VFPA performs very well in my tests. I have found nothing that isn't compatible with the original VFP6 code and I suspect I won't find any
major
differences in VFP9 code because I'm using both VFP6 and VFP9 class libraries in my tests and everything works as expected. Chen has been responsive to my questions and released an update even as I was writing this. I'm not sure I'm ready to cut over to VFPA for my primary
development
work, but this is looking like a great option in this particular situation.
Finally, there is one issue that concerns me at this point and that is
with
memory usage. During an import test like the ones I've described here,
I
see memory usage by VFP and the Large Memory module that Chen provides grow from the 500Mb I usually see when running one of my VFP applications to 2 to
2.5
GB. I have a suspicion this is caused by the two IDX files I have open while doing the import. I'm opening the IDX files with the DBF file so that
they
are updated as the data is inserted into the DBF. This may not be the
best
way to do things because perhaps recreating the indexes after the
import
is
complete may be faster, BUT, I'm not sure how to provide user feedback
on
the progress of the indexing process and maybe I just need someone to
guide
me on this point. I'll have to test doing the import without the IDX
files
open to confirm my thoughts about the memory usage during the import,
but
the most important issue is that the memory that gets allocated during
the
import process isn't released when I close the table and the indexes.
In
fact, it isn't released at all until I close VFPA and restart it. Does anyone have an insight into this memory usage weirdness I'm seeing?
Ok, I know this is a long email, but I'm trying to start some
conversation
and there are several points I think y'all can help me with so have at
it.
Any feedback is helpful for this situation let me know what you know!
Paul H. Tarver Tarver Program Consultants, Inc.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
WRT large memory allocation, try throwing a sys(1104) in maybe.
"Purges memory cached by programs and data, and clears and refreshes buffers for open tables. You can improve performance by calling SYS(1104) after executing commands that make extensive use of memory buffers. This function also clears internal buffers for open tables, forcing the tables to be refreshed, which is useful with large tables and in multiuser environments."
<<try throwing a sys(1104) in>>
This is why you are my hero, Alan! With just a casual reply, you gave me information about a command I didn't know existed, and when I threw in a sys(1104) my memory issue disappeared! After replacing my FLUSH command with a sys(1104), I ran a new test and the memory usage never exceeded 50Mb total! Plus while I expected some improvement in performance, I never dreamed I could possibly achieve a 93% reduction in the time it takes to import 250 million records over my initial tests. HOLY CRAP!
My process involves opening a table, opening two IDX files for that table, importing text from a 3GB file, parsing the text into area code 3byte character values and converting 7 digit text telephone number into an integer value, posting 145,000 rows at time to my table which automatically updates the IDX files, while summarizing a count of records in each area code AND providing a progress bar update for the user.
AND on my first full test it completed the process in only 36.18 minutes which is down from the 531.08 minutes it took when I started this experiment. Un-freaking-believable!
Never bet against the Fox! Never doubt the power of ProFoxTech! And, never, ever doubt Alan Bourke!!!
Thank you, Thank You, THANK YOU!
Paul H. Tarver Tarver Program Consultants, Inc.
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Alan Bourke Sent: Wednesday, March 06, 2024 2:50 AM To: profoxtech@leafe.com Subject: Re: VFPAdvanced - My Problem, My Tests & Some Results
WRT large memory allocation, try throwing a sys(1104) in maybe.
"Purges memory cached by programs and data, and clears and refreshes buffers for open tables. You can improve performance by calling SYS(1104) after executing commands that make extensive use of memory buffers. This function also clears internal buffers for open tables, forcing the tables to be refreshed, which is useful with large tables and in multiuser environments."
You're very welcome Paul, my invoice is in the mail ;)
-
Ajit Abraham -
Alan Bourke -
Kurt Wendt -
Paul H. Tarver