Here is what I found with my Memo field tests. If you open the table in SHARED mode, saving data to a memo field will always append regardless of blocksize and amount of data being stored. Blocksize will make a difference in how much 'minimum' space is taken by each write however. So you could potentially cut down on the rate of bloat. But I think a blocksize of 64 is just 64 bytes. That's not much waste, so I don't think you'll save much by altering that value (but you could waste a ton of space if you set it to 1 to 32 - which causes multiplies of 512 bytes).
If you open the table in EXCLUSIVE mode, it will *try* to do the smart space-reuse calculation mentioned by others. You mentioned a heavily used multi-user table - so normal EXCLUSIVE opening will not work in your case. But if you have several memo fields, you might want to do some checking to see if that is really necessary - e.g. SELECT max(len(alltrim(<memo field name>))) from <table> .... if you find many of the fields are way less than 254, change the structure to use character instead of memo. Of course you need to check the true business case/requirement to see if there is a clear statement about the amount of content to store. I very rarely need more than one memo field in a single table. But if you think you do, normalizing those out to other tables as others mentioned might be best. For me, I just went ahead and let the memo fields bloat. If I saw it growing rapidly - like 10's of MB every day, I'd set up a weekend run to open exclusive, pack (automate it, log results, etc). But it sounds like you might have a very rapid bloat issue on your hands (how often does the 161 records have to be added - does it happen every day?). If I recall correctly, the max size of the FPT is 2GB. So you went to 234MB pretty darn fast. -Charlie On Tue, May 26, 2020 at 9:41 AM Tracy Pearson <[email protected]> wrote: > Hi Paul, > > I don't remember exactly where I learned this. It may have been a talk by > Andy Kramek, or something I read here. > > The way VFP uses Memo files will always append data to the end. Memo bloat > will happen. > Even if you only change the case of some text and the memo size doesn't > change. > A pointer is stored in the DBF to the beginning position of the note. > > You could restructure you code to > - store a path to a file that holds the information. > - store the data in multiple 254 character fields or rows. > - use a different data store moving away from DBFs. > - nightly pack the memo. > > So many options and so little time in a day. > > Have fun, > Tracy > > > -----Original Message----- > From: ProfoxTech [mailto:[email protected]] On Behalf Of Paul > Newton > Sent: Tuesday, May 26, 2020 8:14 AM > To: [email protected] > Subject: Memo bloat > > Hi all > > I have a table with a memo field. It contains ca. 970 records and the size > of the FPT is ca 230KB. I added 161 records to the table, populating the > memo field with several lines of text (the total size of all the text added > was ca 700KB). In each case the memo field was a single replace (no memo = > memo + ..., no replace memo with .... Additive). The FPT ended up as 234MB > but a PACK MEMO reduced this to ca 935KB. > > The trouble is that this is a widely used table in a multi-user environment > and it would be impractical to use PACK MEMO on a regular basis. Blocksize > is set to 64. > > Any suggestions or comments would be very welcome. Thanks in advance. > > Paul Newton > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: https://leafe.com/archives This message: https://leafe.com/archives/byMID/CAJGvLx0RPUbBRN5-KSkztOtvR1tViT=xvmipmpyf0zx28bf...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
