[AccessD] increasing database size

Erwin Craps - IT Helps Erwin.Craps at ithelps.be
Sun Feb 15 08:08:55 CST 2004


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.

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list