[AccessD] Where to place a filter

John,

<<Any thoughts on the subject?>>

A.  JET has more options for query optimizations on a unique index.

B.  When filtering is done, it's a toss up if you should filter on the one
or the many side.  Table sizes can have a big impact, so best practice is to
test both and use the one that is the fastest.

Jim.

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