[AccessD] Compression slows down MDB

Jim Dettman jimdettman at verizon.net
Sat Jan 28 07:21:07 CST 2012


William,

<<How would a query parser know whether a table is "full or not"?>>

 As determined by looking at the table statistics.

 I have a DB here somewhere that is an example of this. It had a three table
join which would be cause the wrong execution plan to be chosen if one of
the tables, which was a large one, was full of records or empty.  Using JET
show plan, you could clearly see that the query parser used two different
plans of execution based on the table being full or empty.  One was
efficient, one was not.  As a result, the query went from <2 sec execution
to more then 30 seconds.

 The table in question was filled/emptied on a regular basis and sometimes
when a C&R done, happened to be empty.  Took forever to track down why
sometimes it would work well and other times not.

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

  Because a DB changes over time and it doesn't track statistics like that.
JET is pretty simplistic.  The assumption was made that after a C&R, the DB
is its most efficient form and that most tables are relatively static in
size.

   After a C&R,  pages have been reorganized, indexes re-balanced, table
statistics updated, etc.  So every query plan at that point is invalidated.
First execution after that generates a new plan, which is then saved with
the query and used until the next C&R or you modify the query and save it.

<<And what does "recosted" mean?>>

  Any and all SQL query parsers given a specific statement "cost" it, which
means that it tries as several different ways of executing it and then
chooses the plan that is the most efficient or "costs" the least.  It does
this based on the SQL statement, indexes available and type of indexes (ie.
having a unique index vs a non-unique one allows for different types of
index-merge operations), and the number of records in the tables involved.

  In the case I mentioned above the parser decided that with the table
empty, it cost less to sort the main table and then perform other
operations.  However with the table full, sorting the table was a very
expensive operation and should have been done on the result set as a finial
step, which was only a couple of records.

 JET doesn't expose this costing, but SQL server does and you can view the
costing plan for any SQL statement.  It will show you each and every step it
performs (index joins, sorts, table scan, etc) and the cost to executing the
overall statement.

  SQL even allows you to override the parser costing by using plan hints to
force the parser to weight certain plans more.  JET does not have this
feature.  If it did, the problem above could have been avoided even with the
table empty.

  We also lost one of the old performance tricks that you could use with
JET; using a SQL statement instead of a saved query.  In the past, SQL
statements were always costed at every execution.  So if you had a very
dynamic table, you would just use SQL statements with it rather then saved
queries.

  However many developers started using SQL everywhere and started having
performance problems as a result (because a query plan was being done every
time).  

  Rather then document the difference and force people to use saved queries,
Microsoft decided to create a temp query (a saved query) for any SQL
statement it found.

  These start with a tilde (~) and you can see them in the querydefs
collection.

Jim.

 



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson
Sent: Friday, January 27, 2012 10:28 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Compression slows down MDB

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
>
-- 
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