[AccessD] Compression slows down MDB

William Benson vbacreations at gmail.com
Fri Jan 27 21:27:35 CST 2012


Jim I had to admit I could not make any sense out of what you explained.
How would a query parser know whether a table is "full or not"?

Trust me I am not debating you it just doesn't get thru my thick head what
you are saying.

Why would access throw away valuable info such as the most efficient way to
plan out and run a query.

And what does "recosted" mean?

Sorry I am such an ignoramus, if you prefer to reply with a link that I can
read up on this I would appreciate.

This seems to indicate it is a bad idea to compact on close of a database.
On Jan 27, 2012 3:18 PM, "Jim Dettman" <jimdettman at verizon.net> wrote:

> Ed,
>
>  I'm not sure this explains this, but part of the compact and repair
> process is to reset the table statistics.  Those are used by the query
> parser to "cost" different ways of doing a query.  Also part of the repair
> and compact process is that each query is flagged to be re-costed.  So
> first
> time it's run, you get a new costing plan calculated (rather then using the
> save one).
>
>  Sometimes on rare occasions, the query parser on queries involving large
> tables will choose an inefficient plan depending on whether the table is
> full or not.
>
>  But with all that said, you query plan would remain inefficient even if
> the database grew until it was re-costed again, at which point it would be
> back up to speed.
>
>  So besides the compact and repair, what else are you doing within the DB
> in terms of records?  Why does it go from 850MB down to 170?  Are you doing
> a large amount of inserting/deleting?
>
> Jim.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Edward Zuris
> Sent: Friday, January 27, 2012 12:11 PM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Compression slows down MDB
>
>
>  Looking at MSFT's web site I get the impression
>  that repair and compression of your MDB file is
>  a good thing.
>
>  However. . .
>
>  Has anyone had the experience of doing a compress
>  and your MsAccess application slowed way down ?
>
>  At 170 megabytes in size, it takes 31 minutes to do
>  a days worth of updates.  At 850 megabytes it takes
>  just 8 minutes.
>
>  BTW, this happens on W2K 32bit Pro with Office 2000,
>  and Win-7 64bit Pro, using Access 2003 32bit, using
>  access 2000 file structures.
>
>  The application didn't change that behavior when
>  converting every thing over to Access 2003 file
>  structures.
>
>  Any ideas ?
>
>  Thanks.
>
>  Sincerely,
>  Ed Zuris.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> 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