Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

excessively large memo file in foxpro

Posted on 2011-03-24
6
Medium Priority
?
2,006 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 30

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 30

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 30

Accepted Solution

by:
Olaf Doschke earned 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

618 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