[AccessD] increasing database size

Hi Erwin

I have never run any systematic tests on this. In general, I see speed
issues only for complex queries, and only for those I find it worth
spending time on optimization efforts.

Here are some more (diversified) observations on this:

http://www.experts-exchange.com/Databases/MS_Access/Q_20838759.html#10027591

/gustav


> What concerns query compilation.
> Regular database windows saved queries need to be compiled to.
> If you do a compress and repair or create an MDE, all you queries are
> decompiled.
> They get compiled when you execute them.

> I have a tool that compiles all queries before delivering a databse to
> the customer.
> I got this from a website somewhere, but unfortunatly I don't remember
> the author or link.

> This is one of the resons why a query runs at first execution slower
> then the second time.

> Please not that this is only true for saved queries.
> And if you use parameters in you query I supose the speed advantage will
> be lost.

> But for all +/- fixed list combo/list boxes this could matter.


> Erwin


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Sunday, February 15, 2004 10:49 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] increasing database size

> Hi Erwin

>> Is'nt that a bad idea?

> Not that I know of. On the other hand, you per definition eliminate
> frontend corruption - not that it is a big problem with WinNT+ but with
> Win9x we saw that from time to time. Also, Access needs to create
> temporary data for running some queries dealing with complex or large
> datasets; when the frontend is write protected, these data are forced to
> temporary files. With Pedro's form opening 1 + 12 queries, this could be
> his bloat problem ...

> As for the compilation, I've never heard this should happen more than
> once. For code it happens when you compile it which I guess we all do
> before leaving an app. As for the queries, do you mean the "~$.."
> saved queries created when you run a form or report bound to SQL-code
> and not to a saved query? Those, of course, Access will need to create
> every time if the file is write protected, but with today's fast
> machines I wonder if a difference can be noted and - if so - you could
> just save the SQL-code as queries in the normal way.

> By the way, that could be a part of Pedro's speed problem: Are you using
> saved queries for all those combos?

> /gustav


>> The size of a database get bigger (but not only reason) due to 
>> compilations.
>> Code get compiled when it gets used, but also queries.
>> So if you would make the file readonly you coder and queries never get

>> compiled and your app is slower.


>> Erwin

>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav 
>> Brock
>> Sent: Saturday, February 14, 2004 2:15 PM
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] increasing database size

>> Hi Pedro

>> If your frontend is not supposed to write data to itself, you may 
>> write protect the file when you have finished design changes and
>> compacting.