Solved

excessively large memo file in foxpro

Posted on 2011-03-24
6
1,804 Views
Last Modified: 2012-05-11
I have a .fpt memo file asssociated with one of my visual foxpro tables that regularly grows to the 2gb limit. The table only has 1 memo file and most of the memo fields are empty. The table only has 3800 records.
I have other tables with two and three memo fields and lots of records and data in them that dont even come close to the size limit.
Does anybody have any ideas as to why this file would grow so large so fast.
0
Comment
Question by:donrbrowning
  • 3
  • 2
6 Comments
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 35209839
It depends how you write and rewrite to this table. It sounds like it needs packing for the memo.

PACK MEMO
0
 

Author Comment

by:donrbrowning
ID: 35210692
Pack memo only takes a two gig file down to 1.6 gigs. Bummer!!
I have a product table with two memo fields full of data and 5600 records and it is only .6 gigs.
???
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 35210940
The effect Cyril hints at is called memo bloat. Each time you write to a memo field and the new value does not fit into the reseved FPT chunk of the old value, a new chunk is added to the end of the fpt instead, causing a bloat. PACK MEMO will rewrite the fpt without all the garbage leftover of old and not referenced blocks of the fpt file. That's for sure.

You can finetune and reduce this a bit via SET BLOCKSIZE before creating a table with memo fields. The blocksize is stored in the fpt header and stays like it was at table creation. Every chunk of FPT used for a memo field value occupies a multiple of this block size.

Besides that, you know about the deleted flag of each record? Like DBF does not shrink with a record deletion, so does FPT.

Here's a small program illustrating the bloat effect:
Cd (GetEnv("TEMP"))
Set BlockSize To 64
Create Table tblTest (mMemo M)
Insert into tblTest values ('')
For lnCount = 1 to 10000
   Update tblTest set mMemo = Space(lnCount)
EndFor 

Open in new window


This updates the memo of the single record 10000 times, each time with a one space longer string. Using a table unbuffered this is what could happen if interactivechange of a editbox is set to save the current editbox value to the dbf with each keystroke and 10000 spaces are entered.

With the default blocksize of 64 this ends up in a single record with Space(10000) in it in the end, the fpt file has grow much larger, though, to 776KB.

With Blocksize set to 0, meaning blocks are single bytes (see the foxpro help on BLOCKSIZE) the bloat effect is even worse, as each new, longer value causes bloat. The final length now is ~49 MB.

With Blocksize 1 or 512 (which actually is equivalent) the bloat effect is lower, the final fpt size then is 106 KB only.

Small block sizes mean less occupied space, less unused bytes for the packed version, but also an increasing chance an update with a longer value causes a bloat.

On the other side setting blocksize very high means, that even short strings will at least occupy one block, eg with blocksize 512 even a single char in a memo occupies 512 bytes, a string length of 513 occupies 1024 bytes etc.

You have to find a compromise blocksize not occupying too much void space and not causing too much bloat.

Bye, Olaf.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:donrbrowning
ID: 35214326
I have an edit box in a form that has the control sorce set to this field.
I have maxlength property set to 256. could this have anything to do with it?
The funny thing is that if I browse this table 95% of the memo fields are empty.
yet the file is 2 GB.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 35215893
Don,

sorry, this does not say anything about the tables fpt blocksize and how often the memo is updated. maxlength property has no influence about how often the values are updated, this is rather a matter of how you save and if the table is buffered or not.

It's improbable one such form will cause that havoc on the fpt file. Have you use code references to cehck all places in code, that act on this tables memo field? Any other application writing there?

Bye, Olaf.
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 35215918
Oh, and as you have a limit of 256 chars, why not make it 254, this enables you to use a char field instead. fixed length, no bloat. Just the fixed 254 bytes per record, with 3800 records that's still a low size.

Bye, Olaf.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Find out what the Office 365 disclaimer function is, why you would use it and its limited ability to create Office 365 signatures.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now