[AccessD] Where to place a filter

...or not ...remember that Access uses the Rushmore query optimization 
algorithms which build adhoc indices based on the actual data in addition to 
the indices you've spec'd ...it could be that when you changed the index the 
first time that you forced a new query optimization that gave a much better 
result ...and that when you redid the query, Access retained the optimized 
adhoc indices because the change was actually immaterial ...in other words 
you had added a lot of data since you first built the query and when you 
forced it to review its optimization it did so with the additional data 
considered ...and when you reverted, the optimization held because the speed 
increase was based on the data rather than the index.

...or something like that :)

...so the real answer is that when you've added a large amount of data into 
an Access database, you may find that your original queries work much faster 
if you force them to reoptimize.

William Hindman

----- Original Message ----- 
From: "JWColby" <jwcolby at colbyconsulting.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Thursday, November 30, 2006 7:50 AM
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
>