Jürgen Welz
jwelz at hotmail.com
Wed Mar 3 18:18:00 CST 2004
I'd just compare two mdb files with 8 text fields, no indexes, 250 characters per field plus an autonumber field (unicode compression off) vs the same data structure but with no data in the text fields. A quick loop of 100,000 iterations to fill the tables with a constant string of 250 characters in each field vs just adding empty strings to the test database. Compact and draw your own conclusions. I would then run a routine to add a single character to one of the fields in the empty text database to see if it immediately swells back to the same size as the other database and draw further conclusions. It would have taken less time to run this test than to write this email but I believe we should all learn to fish and I saw William's frown. Most of my stuff is still Access 97 and all my 2000 stuff has been in limited number of user environments so I haven't worked with record level locking. I have run my own lock table/field when running unbound and that may prove to be more efficient from a bloat standpoint. I may just run this test this weekend but I am so busy right now that I'm deleting the vast majority of messages from the list right now. Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com >From: "William Hindman" <wdhindman at bellsouth.net> > >...where is Jurgen when we need him? ...this is right up his alley :( > >William Hindman "My idea of an agreeable person is a person who agrees with >me." Disraeli > >----- Original Message ----- >From: "Gustav Brock" <gustav at cactus.dk> >To: "Access Developers discussion and problem solving" ><accessd at databaseadvisors.com> >Sent: Wednesday, March 03, 2004 1:06 PM >Subject: Re: [AccessD] notes on mdb bloat > > > > Hi Jim > > > > > My guess would be no. Note that Garry mentions that it stays >bloated > > > until compact. That doesn't make sense. If the record locking >algorithm > > > can work after the database is compacted, then why would it have to >bloat it > > > in the first place? It just doesn't make sense from that standpoint. > > > > It could if you assume that - after compacting, when a record is opened > > for editing - it will be copied as a new and saved while the old is > > marked as deleted. But as you say, just guessing. > > > > > It does make sense though as to how they could achieve it with >little > > > problem, as they are just duplicating a technique that many developers >used > > > to get pseudo record level locking. It would be a simple matter to >change > > > the add logic to always use an empty page (they in part had already >done > > > that for JET 3.5). > > > > > So there's another reason why it's probably not that; if it were >that > > > simple (one record per page), then why did we need a new version of >JET? > > > Seems like something they could have put in A95 or A97 quite easily. > > > > Well, the implementation is slightly more complicated than bloating a > > row to fill a page. If I understand the article(s), the revised Jet > > engine maintains a flag which is set when any user requests row-level > > locking which forces subsequent users also to use row-level locking > > even if their default setting was for page-level locking. > > > > > With no docs, it's all guess work though. > > > > Yes, true. However, experimenting with this while watching the mdb file > > with an hex viewer/editor could tell a lot. I have neither the time > > nor the need for this, though ... > > > > /gustav > > > > > > > > > -----Original Message----- > > > From: accessd-bounces at databaseadvisors.com > > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock > > > Sent: Wednesday, March 03, 2004 12:11 PM > > > To: Access Developers discussion and problem solving > > > Subject: Re: [AccessD] notes on mdb bloat > > > > > > > Hi all > > > > > Does this - simplified - boil down to, that all row-level locking does > > > compared to page-level locking is bloating a row to fill a page? > > > > > /gustav > > > > > > >> Hi Seth et all > > > > >> In Garry Robinson's newsletter I noted this extremely interesting >topic: > > > > > > >> SINGLE RECORD LOCKING, BLOAT AND CITRIX BUGS > > > > >> Did you know that the record-level locking option (menu > > >> Tools~Options~Advanced) that is used by Access 2000 onwards has an > > >> interesting twist. To achieve a single record lock, each record that > > >> is locked uses up all of an Access page in the mdb file. This page of > > >> data, most of which will probably be empty for records with only a >few > > >> fields, consumes 4000 bytes. This space is later recovered when the > > >> database is compacted but if you have a database that seems to expand > > >> fast, this could be your culprit. For more on this database bloat and > > >> a possible cure for Access corruptions for Citrix head to this page. > > > > >> http://ewbi.blogs.com/develops/2004/01/access_corrupti.html > > > > >> and an other blog on the same topic > > >> http://dbforums.com/arch/213/2002/10/551337 > > > > >> Gary is here: > > > > >> http://www.vb123.com/ > > > > >> /gustav > > > > > > >>> I hadn't even thought of that. I tried it just now, and the >resulting > > >>> mdb is 1.5MB. Still three times as big as the A97 version, but not >as > > >>> big as the base A2K version. > > > > >>> The unicode comments make sense, I had forgotten about that, too. > > > > >>> Oh well, more interesting info from the developer front... > > > > >>> Seth > > > > >>> On Wed, 2003-03-12 at 11:45, Gustav Brock wrote: > > >>>> Hi Seth > > >>>> > > >>>> What happens if you "forward port" the A97 version to A2000? > > >>>> > > >>>> /gustav _________________________________________________________________ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=dept/bcomm&pgmarket=en-ca&RU=http%3a%2f%2fjoin.msn.com%2f%3fpage%3dmisc%2fspecialoffers%26pgmarket%3den-ca