Re: 2 GB memo bloat in DBF used in WebConnection site (was RE: Friday Poll (while dl'ing MS Flash Patch))
At 08:56 2016-11-03, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
On 2016-10-28 10:24, Kevin J Cully wrote: X DBFs - I have a client that insisted I use DBFs for his desktop and Web-Connection site. It's run almost flawlessly since 2008 with the exception of the 'memo field bloat' issue. That's caught me a couple of times over the years hitting the 2G limit per file.
I'm handling one of Jeff Johnson's (RIP) clients and having this issue. I was about to create a simple utility for the end-user to grab the table and pack it, but curious if you've got a better idea?
I have a routine that packs and reindexes all of the tables. I have not needed anything more involved.
I do see some memo bloat. I have a table that stores configuration data. When packed, the .fpt is 1280 bytes. I see from the latest sent to me that it is up to 5,205,248 bytes. That is bigger than the source code.
Sincerely,
Gene Wirchenko
On 2016-11-03 14:24, Gene Wirchenko wrote:
I have a routine that packs and reindexes all of the tables. Ihave not needed anything more involved.
I do see some memo bloat. I have a table that storesconfiguration data. When packed, the .fpt is 1280 bytes. I see from the latest sent to me that it is up to 5,205,248 bytes. That is bigger than the source code.
Would the PACK do a reindex automatically? According to VFP Help (also here: https://msdn.microsoft.com/en-us/subscriptions/downloads/t2704cs4(v=vs.80).a... ): "If the current table has one or more indexes open, PACK rebuilds the index files."
So your reindex, Gene, must be the EXPLICIT kind of reindex akin to Stonefield Database Toolkit or your own explicit INDEX Field TAG MyOrder commands. Otherwise, if you're just doing REINDEX, sounds to me like you're doing it twice. In fact, if you're running your own reindex, you may as well do a DELETE TAG ALL beforehand to avoid the double-work. ????
On 11/3/2016 2:24 PM, Gene Wirchenko wrote: [snip]
I'm handling one of Jeff Johnson's (RIP) clients and having this issue. I was about to create a simple utility for the end-user to grab the table and pack it, but curious if you've got a better idea?
One thing I put into my design is working with temporary cursors or objects (aka from SCATTER MEMO NAME oDataRec....). I rarely have user interface objects directly accessing DBFs.
As I recall, the main reason for memo bloat was caused when saving data. Specifically, if I was editing a memo, then saved it, if the contents were larger than my "blocksize", VFP would create a whole new "block" in the .fpt file. The original block is essentially wasted space at that point. I think there are some nuances to this: e.g. VFP doesn't see it as "bigger" unless the "blocksize" threshold is crossed - or maybe the text has to be "bigger" than the text 1st edited. I can't remember.
So, if you have a GUI screen element that is directly bound to the DBF memo field, every time you type a character (well, every few characters maybe), a new .fpt block gets assigned, wasting the previous one. So imagine if you have users typing quite a bit of text in there, editing, yada yada. One editing session might cause dozens of "blocks" in the memo file to be wasted. But if you take my approach, there is only 1 "write" operation (when data is saved) and therefore, at worst, 1 wasted .ftp "block".
Take a look at "SET BLOCKSIZE". If you can't really change a bunch of source code, simply "recreating" a table with a different memo blocksize can sometimes solve the bloat problem. E.g. if the memo field will almost always have more than 100 characters, but rarely have over 512, do a SET BLOCKSIZE to 1. Once the table is created I think the blocksize for the memo fields are "locked in". And I don't think a simple MODIFY STRUCTURE will adjust to the new blocksize, so you may have to do some "table-create-swap" thing. The default is 64 bytes. And be advised, setting it to 1 thru 32 is not in bytes, it is in 512 byte chunks (SET BLOCKSIZE to 1 is 512, setting to 2 is 1024, ... up to 32). After 32, the number is interpreted as straight bytes.
Of course, as always, test these things out, run some variations.
HTH, -Charlie
[snip]
On 2016-11-03 17:31, Charlie wrote:
One thing I put into my design is working with temporary cursors or objects (aka from SCATTER MEMO NAME oDataRec....). I rarely have user interface objects directly accessing DBFs.
I did this years ago with my MBSS framework based upon your ideas when you presented as such as Central PA VFP Users Group back in what...1998? Been problem free ever since. Great approach.
As I recall, the main reason for memo bloat was caused when saving data. Specifically, if I was editing a memo, then saved it, if the contents were larger than my "blocksize", VFP would create a whole new "block" in the .fpt file. The original block is essentially wasted space at that point. I think there are some nuances to this: e.g. VFP doesn't see it as "bigger" unless the "blocksize" threshold is crossed
- or maybe the text has to be "bigger" than the text 1st edited. I
can't remember.
So, if you have a GUI screen element that is directly bound to the DBF memo field, every time you type a character (well, every few characters maybe), a new .fpt block gets assigned, wasting the previous one. So imagine if you have users typing quite a bit of text in there, editing, yada yada. One editing session might cause dozens of "blocks" in the memo file to be wasted. But if you take my approach, there is only 1 "write" operation (when data is saved) and therefore, at worst, 1 wasted .ftp "block".
Take a look at "SET BLOCKSIZE". If you can't really change a bunch of source code, simply "recreating" a table with a different memo blocksize can sometimes solve the bloat problem. E.g. if the memo field will almost always have more than 100 characters, but rarely have over 512, do a SET BLOCKSIZE to 1. Once the table is created I think the blocksize for the memo fields are "locked in". And I don't think a simple MODIFY STRUCTURE will adjust to the new blocksize, so you may have to do some "table-create-swap" thing. The default is 64 bytes. And be advised, setting it to 1 thru 32 is not in bytes, it is in 512 byte chunks (SET BLOCKSIZE to 1 is 512, setting to 2 is 1024, ... up to 32). After 32, the number is interpreted as straight bytes.
Of course, as always, test these things out, run some variations.
I created this simple test, without regard for blocksize, on a copy of the table on my own machine last weekend:
CLEAR SET ESCAPE ON LOCAL liCnt as Integer liCnt = 0 CLOSE DATABASES all USE c:\crap\vendor SHARED DO WHILE .T. liCnt = liCnt + 1 ? liCnt WAIT WINDOW NOWAIT "Iteration " + ALLTRIM(STR(liCnt)) UPDATE vendor SET ven_note = ven_note ENDDO
It hit the 2GB limit after so many iterations. So I'm not sure the blocksize plays a part. This code below says average note length is just under 130 characters:
SELECT LEN(ALLTRIM(ven_note)) as iLen FROM vendor INTO CURSOR MyLens SELECT AVG(iLen) FROM MyLens
On 2016-11-03 17:31, Charlie wrote:
On 11/3/2016 2:24 PM, Gene Wirchenko wrote: [snip]
I'm handling one of Jeff Johnson's (RIP) clients and having this issue. I was about to create a simple utility for the end-user to grab the table and pack it, but curious if you've got a better idea?
One thing I put into my design is working with temporary cursors or objects (aka from SCATTER MEMO NAME oDataRec....). I rarely have user interface objects directly accessing DBFs.
As I recall, the main reason for memo bloat was caused when saving data. Specifically, if I was editing a memo, then saved it, if the contents were larger than my "blocksize", VFP would create a whole new "block" in the .fpt file. The original block is essentially wasted space at that point. I think there are some nuances to this: e.g. VFP doesn't see it as "bigger" unless the "blocksize" threshold is crossed
- or maybe the text has to be "bigger" than the text 1st edited. I
can't remember.
Just an update: found 2 records that had bazillions lines in them (determined via MEMLINES), and the process was continually adding another line every 6 minutes since August 2016. No wonder the file size spiraled. Problem source fixed from vendor side. All good now.
On 11/30/2016 5:40 PM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
One thing I put into my design is working with temporary cursors or
As I recall, the main reason for memo bloat was caused when saving data. Specifically, if I was editing a memo, then saved it, if the
Just an update: found 2 records that had bazillions lines in them (determined via MEMLINES), and the process was continually adding another line every 6 minutes since August 2016. No wonder the file size spiraled. Problem source fixed from vendor side. All good now.
FYI, I did more research on this and just never sent in an update.
The key point is whether or not the table was opened "EXCLUSIVE" or not. If you open it exclusive, there is a "chance" the memo "block" will be reused (if the new text size is the same or smaller than what was used before).
But opening the table in shared mode means EVERY save to the memo will cause a NEW memo block to be appended to the .FPT file.
I was going to write up some code folks could run to show this behavior, but didn't get to it. So the proof is left as a homework assignment. :)
-Charlie
On 2016-12-01 19:01, Charlie wrote:
FYI, I did more research on this and just never sent in an update.
The key point is whether or not the table was opened "EXCLUSIVE" or not. If you open it exclusive, there is a "chance" the memo "block" will be reused (if the new text size is the same or smaller than what was used before).
But opening the table in shared mode means EVERY save to the memo will cause a NEW memo block to be appended to the .FPT file.
I was going to write up some code folks could run to show this behavior, but didn't get to it. So the proof is left as a homework assignment. :)
Well that would be intelligent design when in exclusive mode if true.
Divide the data into smaller DBF's then add code the select the correct small table that needs updating.
Rick Q quilhotr@gmail.com
On Thu, Nov 3, 2016 at 2:24 PM, Gene Wirchenko genew@telus.net wrote:
At 08:56 2016-11-03, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
On 2016-10-28 10:24, Kevin J Cully wrote: X DBFs - I have a client that insisted I use DBFs for his desktop and Web-Connection site. It's run almost flawlessly since 2008 with the exception of the 'memo field bloat' issue. That's caught me a couple of times over the years hitting the 2G limit per file.
I'm handling one of Jeff Johnson's (RIP) clients and having this issue.
I was about to create a simple utility for the end-user to grab the table and pack it, but curious if you've got a better idea?
I have a routine that packs and reindexes all of the tables. I havenot needed anything more involved.
I do see some memo bloat. I have a table that stores configurationdata. When packed, the .fpt is 1280 bytes. I see from the latest sent to me that it is up to 5,205,248 bytes. That is bigger than the source code.
Sincerely,
Gene Wirchenko
[excessive quoting removed by server]
On 2016-11-03 19:25, Richard Quilhot wrote:
Divide the data into smaller DBF's then add code the select the correct small table that needs updating.
Not an option at this point. Besides...this table has only ONE memo field.
We currently have an fpt file that has reached 250 mb, When I need to add another memo field, I create a dbf with only the key and the memo field and relate it to the other database. This way each memo can grow up to 2 GB in size I do a pack memo once a week and save a copy of the pre-packed dbf and fpt just in case. Archiving old data or splitting memos among multiple databases is the way to fix the problem . I don't feel safe when a memo file goes over 1 GB. It's probably best to have some sort of warning built into your software that tells you when it's close to 2.GB so you can make adjustments. Once it goes over 2 GB, things get scrambled and may not open at all.
I have had minor corruption over the years, but nothing major that I couldn't fix from a backup. Of course, make sure you backup every night.
From: "mbsoftwaresolutions@mbsoftwaresolutions.com" mbsoftwaresolutions@mbsoftwaresolutions.com To: ProFox Email List profox@leafe.com Sent: Thursday, November 3, 2016 11:14 PM Subject: Re: 2 GB memo bloat in DBF used in WebConnection site (was RE: Friday Poll (while dl'ing MS Flash Patch))
On 2016-11-03 19:25, Richard Quilhot wrote:
Divide the data into smaller DBF's then add code the select the correct small table that needs updating.
Not an option at this point. Besides...this table has only ONE memo field.
[excessive quoting removed by server]
-
Charlie -
Gene Wirchenko -
mbsoftwaresolutions＠mbsoftwaresolutions.com -
Michael Madigan -
Richard Quilhot