[AccessD] Compression slows down MDB

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