MartyConnelly
martyconnelly at shaw.ca
Wed Mar 3 18:15:49 CST 2004
There was an article on this in Access Advisor around 98 or 99, most of it revolved around the use of Temp queries with the solution being putting them in a separate mdb. Ahh got it. Feb 98 Alan Biggs "the Mystery of the bloated database". . If you constrain the recordset of a form by setting an SQL statement from code into the recordsource of a form, the mdb grows. Access implicitly creates a temporary querydef and inserts it into your SQL statement. If you have a client server system where you dynamically modify SQL pass through queries from an ODBC datasource. the mdb grows. Each time the SQL text is changed, its querydef is deleted and a new one with the samed name is created at the EOF, thus enlarging the mdb William Hindman wrote: >...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 >>>>>> >>>>>> >>-- >>_______________________________________________ >>AccessD mailing list >>AccessD at databaseadvisors.com >>http://databaseadvisors.com/mailman/listinfo/accessd >>Website: http://www.databaseadvisors.com >> >> >> > > > > -- Marty Connelly Victoria, B.C. Canada