Jim Dettman
jimdettman at verizon.net
Thu Nov 30 14:59:13 CST 2006
John, Through Experts-Exchange, I bumped into something similar, but the opposite of what you've encountered. A query opened in design view and saved while the tables were empty would yield a fast execution, but if the tables were full, a very slow execution would result. Using JET showplan, I found that JET was not choosing an optimal plan when the tables were full. It was performing the joins on the tables in reverse (vs. the fast plan), which caused a sort to be required. If the joins were done the other way, no sort was required and thus a lot of time was saved. Basically it was a bug in the query optimizer. Although "bug" may be too strong of a word; the query optimizer has to draw the line somewhere in the number of different plans it checks or it would never finish. Since there was no way around it, the guy I was helping finally decided on writing a routine to empty the table, perform the query, then fill the table back up. As long as the query was not resaved, the fast execution plan was always used. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Thursday, November 30, 2006 7:51 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Where to place a filter There is more to the story, which leaves me completely confused. In the process of fixing the problem, I had dropped the AgencyID FK from the tblChild table and pulled in the AgencyID PK from tblAgency, and then applied the filter to the AgencyID PK. To do further testing I then pulled in the FK from the tblChild table again and moved the filter back to that FK field and the speed was still 3 seconds! To summarize, I had an extremely slow query. I discovered that if I simply pulled the PK from the tblAgency and applied the filter to that (dropping the FK AgencyID from the tblChild out of the query in the process) the query went from 3 minutes down to 3 seconds. However when I pulled the AgencyID FK back out of the tblChild and reapplied the filter to the FK, the query time remained at 3 seconds. Hmmmm..... It certainly sounds like something about the query was saved in the background, and whatever that something was it was corrupted or something. Changing the query by pulling the PK and applying the filter to that, dropping the FK in the process, perhaps caused something in the background to be corrected. I dunno. I have never seen anything like this before. Sadly it means I cannot necessarily look for speedups in my existing database by moving a filter from an FK to the corresponding PK. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Thursday, November 30, 2006 6:45 AM To: 'Access Developers discussion and problem solving' Subject: [AccessD] Where to place a filter I found an interesting anomaly today. I was troubleshooting why a query was two orders of magnitude slower in a query in a new version of a database vs. the same query in an older version of the database. To make a long story short: Two tables, Agency / child Agency has a PKID Child has a FK for the agency PKID Child joined to agency on this PK/FK Filter on the FK in child yields a query 3 minutes long Move the filter to the Agency PKID the time drops to 3 seconds!!! I have to assume that the difference lies in the fact that the PK is a unique index vs. a non-unique index for a FK. I have to say that I had never thought about this specific subject and just treated an index as an index, but in the future I will examine carefully applying a filter on an FK as opposed to joining to the required table and filtering on the PK. Any thoughts on the subject? John W. Colby Colby Consulting www.ColbyConsulting.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