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