[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