FoxPro Programming
FPT files hard to manage
Gravatar is a globally recognized avatar based on your email address. FPT files hard to manage
  Joel Aiken
  All
  Aug 1, 2018 @ 01:45pm

I have several DBF files with memo fields, so I must live with FPT files. My dilemma is the FPT files ballooning up in size very quickly. I have a couple which approach the 2GB limit. Somewhere I read that maybe the 2GB limit does not apply to FPT files (fact or fiction?)

My technique of dealing with this is to "PACK MEMO" the files. Of course, that requires exclusive use of the files, so I wrote a script to do that at 2:00 AM to avoid conflicts with logged-in clients. The PACK Memo command seems to reduce the file size dramatically, but the problem is the FPT files grow back rapidly. I have begun packing several times a day to keep them in check.

Now I wish I had avoided memo fields like the plague. I am in the process of re-designing some DBFs to remove or minimize the use of memo fields. I'm also trying to avoid storing data after its usefulness has expired.

I am looking for comments and advice about this. Thanks, Joel

Gravatar is a globally recognized avatar based on your email address. re: FPT files hard to manage
  FoxInCloud Support - Thierry N.
  Joel Aiken
  Aug 1, 2018 @ 02:10pm

replace memo fields only if new value is different:

&& pseudo-code

gather memvar all except memoFields

for each memofield in memoFields
  if ! trim(memoField) == trim(m.memoField)
    replace memoField with m.memoField
  endif
endfor

there's nothing you can do for inserts

Gravatar is a globally recognized avatar based on your email address. re: FPT files hard to manage
  Joel Aiken
  Joel Aiken
  Aug 1, 2018 @ 04:22pm

I changed several memo fields to Char fields where they were used for email addresses, image URLs and other data of length within 255 characters. My problem was these memo files (now Char fields) were in DBFs being updated frequently. Now that I understand the FPT file bloat caused by the FPT file getting a new record every time a memo field is updated (and all the older records for that particular memo field are deleted but not packed), I see why I was having such a problem. Looks to me like a memo field is perfect for a situation where that field is not being updated.

I have huge improvement already.

Joel

Gravatar is a globally recognized avatar based on your email address. re: FPT files hard to manage
  Rick Strahl
  Joel Aiken
  Aug 2, 2018 @ 09:17am

Unfortunately there are no automatic answers to this. Memo bloat is one of the big reasons that using FoxPro tables is a pain in the ass and a good reason to use a different backend for data. This is especially true for high transaction applications that constantly rewrite data and especially those using business objects that update the entire record of fields with GATHER.

Unless you're dealing with data that approaches the 2gig limit however, I generally don't worry about it, but make sure that the application has a good mechanisms for maintenance. Regular pack and reindex routines typically can address these scenarios nicely. In Web applications you can automate this by firing of a sequence of events to put a Web Connection application into single-instance mode, then doing the maintenance and then resetting to normal multi-instance mode and then set that up at a URL or as a scheduled task to run once a day during off hours (or however often required).

+++ Rick --

Gravatar is a globally recognized avatar based on your email address. re: FPT files hard to manage
  Joel Aiken
  Rick Strahl
  Aug 2, 2018 @ 09:29am

Rick, Thanks so much for taking the time to reply. In the past, I hardly ever used memo fields. On my most recent app, I decided memo fields were cool, used a bunch of them and it bit me. Fortunately, it was fairly easy to alter my technique and fix the problem. Wish I had the nerve to make my next app with a SQL server back-end. I'm in the old dog category (can't learn new tricks). Maybe I'll set up a test app to exercise a different backend database. I'm sure you would suggest SQL Server, right? Joel

Gravatar is a globally recognized avatar based on your email address. re: FPT files hard to manage
  Rick Strahl
  Joel Aiken
  Aug 3, 2018 @ 11:09am

Any SQL backend will work, but the West Wind tools are built for SQL Server and usually require some minor adjustments for other backends. I would possibly look into PostGres, but given that SQL Express is free and supports up to 10gig databases there's not a lot of reason to look at other solutions.

+++ Rick ---

© 1996-2024