Hi Kevin

Many thanks for your reply and suggestions.

Paul

-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com Sent: 26 May 2020 14:33 To: profoxtech@leafe.com Subject: Re: Memo bloat

Sent by an external sender ------------------------------

Hello Paul,

I worked on a system with a table called ITEMS which has multiple memo fields, and ran into similar issues.  It took quite a while to PACK MEMO.  What I ended up doing was to split out all of the memo fields into their own table called ITEMSMEMO with a structure of CREATE TABLE ITEMSMEMO (ItemMemoPK INTEGER, ItemPK INTEGER, TypeMemo CHAR(20), TextField MEMO, dCreate DATETIME).  The time it takes to do a PACK MEMO on this table was reasonable compared to the combined table.

Another option is to avoid memo fields, but this increases programming difficulty.  You could create an external table that contains 255 character segments of the memo fields that combined with an ORDER field could allow for the recreation of the memo field.  CREATE TABLE ITEMSMEMO (ItemMemoPK INTEGER, ItemPK INTEGER, OrderNo INTEGER, TEXTFIELD CHAR(255), dCreate DATETIME). To display the field, you'll need to query for it and then assemble it and then display it.  When it changes, you will delete the previous values, and then split the field and insert it into the table.

Another option is store the text in external files on the server or the machine.  There are all sorts of issues that go along with this including making sure you have the right version that goes with the record as it was saved.

Another option is to upgrade to a true database server such as MySQL or MariaDB or PostgreSQL.  These database servers will effectively PACK MEMO on your behalf when needed.

Hope this helps.

-Kevin

On 05/26/2020 08:14 AM, Paul Newton wrote:

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: ProFox@leafe.com 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/c8aa28a7-72a5-2b1d-4154-a3d8ae9d89e0@cully.... ** 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. Report [OT] Abuse: http://leafe.com/reportAbuse/c8aa28a7-72a5-2b1d-4154-a3d8ae9d89e0@cully.biz

The way VFP uses Memo files will always append data to the end. Memo bloat will happen.

If the changed data in bytes > blocksize then it will add a new block. If you keep the changes < blocksize then it will re-use the same space. The trouble is with a blocksize of 64, it's very likely that it will almost always be adding blocks.

One way to mitigate is to set blocksize to 0, in which case it will always only allocate as much as is needed.

Another way to mitigate is only write to memo fields if they've changed.

Hi Alan,

If the changed data in bytes > blocksize then it will add a new block. If you keep the changes < blocksize then it will re-use the same space. The trouble is with a blocksize of 64, it's very likely that it will almost always be adding blocks.

That would be very thoughtful of VFP, indeed, but it's unfortunately not what is happening:

Set Blocksize To 64 Create Table memotest (memofield M) Use memotest shared Insert into memotest values ("1234567890") Use Dir like memotest.fpt ? Use memotest shared Replace memofield with "54321" Use Dir like memotest.fpt ? Use memotest shared Replace memofield with "1234567890" Use Dir like memotest.fpt

The code replaces the same field with a shorter string, then with a longer string. All strings are way below the blocksize. Yet, each time the FPT file grows by 64 bytes.

Guys, I wrote a piece of software to overcome this exact same Memo Bloat problem back in 2005 and it is on Ed's VFP Download section under the name of "Memo Field Substitute".

Give it a go and see if it works or is helpful

Dave Crozier Software Development Manager Flexipol Packaging Ltd.

﴾⚆ᨎ⚆﴿

Flexipol® Packaging Ltd T 01706 222 792 E DCrozier@flexipol.co.uk W https://www.flexipol.co.uk/ Follow us: Unit 14 Bentwood Road, Carrs Industrial Estate, Haslingden, Lancashire, BB4 5HH

​This communication and the information it contains is intended for the person or organisation to whom it is addressed. Its contents are confidential and may be protected in law. If you have received this e-mail in error you must not copy, distribute or take any action in reliance on it. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.

Flexipol Packaging Ltd. has taken every reasonable precaution to minimise the risk of virus transmission through email and therefore any files sent via e-mail will have been checked for known viruses. However, you are advised to run your own virus check before opening any attachments received as Flexipol Packaging Ltd will not in any event accept any liability whatsoever once an e-mail and/or any attachment is received.

It is the responsibility of the recipient to ensure that they have adequate virus protection.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ​​ ​Terms & Conditions: Notwithstanding delivery and the passing of risk in the goods, the property in the goods shall not pass to the buyer until the seller Flexipol Packaging Ltd. ("The Company") has received in cash or cleared funds payment in full of the price of the goods and all other goods agreed to be sold by the seller to the buyer for which payment is then due. Until such time as the property in the goods passes to the buyer, the buyer shall hold the goods as the seller's fiduciary agent and bailee and keep the goods separate from those of the buyer and third parties and properly stored protected and insured and identified as the seller's property but shall be entitled to resell or use the goods in the ordinary course of its business. Until such time as the property in the goods passes to the buyer the seller shall be entitled at any time -----Original Message----- From: ProFox profox-bounces@leafe.com On Behalf Of Charlie Coleman Sent: 27 May 2020 01:09 To: ProFox Email List profox@leafe.com Subject: Re: Memo bloat

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 tracy@powerchurch.com 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:profoxtech-bounces@leafe.com] On Behalf Of Paul Newton Sent: Tuesday, May 26, 2020 8:14 AM To: profoxtech@leafe.com 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]

Thanks Christof, I will try your suggestions

Paul

-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Christof Wollenhaupt Sent: 26 May 2020 14:47 To: profoxtech@leafe.com Subject: Re: [SPAM] Memo bloat

Sent by an external sender ------------------------------

Hi Paul,

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.

This doesn't sound like the memo file is actually written at once and only once. When you change a memo field in a table that is not exclusively open, then VFP always appends the new text to the file. With just one write operation, you should end up with padded versions of the content appended only once.

Could it be that you have code that replaces the memo field with itself or does SCATTER MEMO / GATHER MEMO. VFP isn't smart enough to detect if you put the same content into a memo field again and just appends it. That might happen on other records than the newly added one.

I would test this with just one record instead of 161. Make a copy of the FPT file, run the code to append the record, then make another copy of the FPT file and look at what is different. If the file is OK, try the same with three records which should reveal patterns, if you process the same record more than once.

-- Christof [excessive quoting removed by server]