William Hindman
wdhindman at bellsouth.net
Wed Mar 3 19:00:52 CST 2004
...I love to fish ...but when I want to eat I go to people who can catch them :))))) William Hindman "My idea of an agreeable person is a person who agrees with me." Disraeli ----- Original Message ----- From: "Jürgen Welz" <jwelz at hotmail.com> To: <accessd at databaseadvisors.com> Sent: Wednesday, March 03, 2004 7:18 PM Subject: Re: [AccessD] notes on mdb bloat > 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 > > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >