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?

tia,

--Mike

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/e044193ba7018293428c650c963e03eb@mbsoftwaresolutions.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.

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?

tia,

--Mike

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/fb2684c7cd4a909e6a38cf87e487e747@mbsoftwaresolutions.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.

I created a subclass of the wwMaint.prg and then put that behind an authentication. I then added various PACKing routines: Some pack all tables, others pack the "vulnerable to memo bloat" tables.

The problem is getting exclusive access to the tables. If you're running in COM mode, then WWWC has a way of stopping all instances except one. If you're running in File mode which is what I'm doing, I have to remote in and kill the extra instances and then run the pack. It's a pain.

I've considered moving the table to PostgreSQL and not telling the client. Tempted but I haven't gone through with it.

-Kevin

-----Original Message-----

From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of mbsoftwaresolutions@mbsoftwaresolutions.com

Sent: Thursday, November 03, 2016 11:57 AM

To: profox@leafe.com

Subject: 2 GB memo bloat in DBF used in WebConnection site (was RE: Friday Poll (while dl'ing MS Flash Patch))

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?

tia,

--Mike

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/F82DB4DE49440845A14B540F6C2DF6A91DCF18@BLUFITSPMBS01.cherokeega.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.

On 2016-11-03 12:14, Kevin J Cully wrote:

> I created a subclass of the wwMaint.prg and then put that behind an

> authentication. I then added various PACKing routines: Some pack all

> tables, others pack the "vulnerable to memo bloat" tables.

>

> The problem is getting exclusive access to the tables. If you're

> running in COM mode, then WWWC has a way of stopping all instances

> except one. If you're running in File mode which is what I'm doing, I

> have to remote in and kill the extra instances and then run the pack.

> It's a pain.

Not sure how it's being run, but I would suspect File mode. Terminal

Server is involved with this client, which I've not dealt with before,

but not sure if that's important for the matter at hand.

>

> I've considered moving the table to PostgreSQL and not telling the

> client. Tempted but I haven't gone through with it.

I hear ya. I was thinking "man, I don't have this problem with MySQL

(MariaDB)!!!!"

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/cc8411a987f812f31ad19416984a32ef@mbsoftwaresolutions.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.

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

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/1324d2dfb2fc7e838a23ddb6f4fb7803@mtlp000087

** 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.

On 2016-11-03 14:24, Gene Wirchenko wrote:

> 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.

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).aspx ): "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.

????

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/0239ac85aa726fbeeeb2cebb17c52ac8@mbsoftwaresolutions.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.

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]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/036fab06-2c19-0bf0-76b5-9ff28c3a8bf6@verizon.net

** 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.

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

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/ba7b572ef12048b906d7e35301d3296d@mbsoftwaresolutions.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.

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 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

>

>

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/CADaZP2mPcyGowDuSGs3LcYAiR0+yoo5td+iprj6TedurEh_sHA@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.

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.

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/076c4e2d68bc4f0d7df489e9e0e7d52c@mbsoftwaresolutions.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.