Which is faster?

[Infocore]

Hey all... I was wondering if anyone could weigh in on this
question....

I have a form with a subform. Opening the form and navigating through
the records is very slow. The subform is a continuous form.

The datasource for the main form is query based on T_Encounter and the
datasource for the subform is a query based on T_Assessment. The
T_Assessment table has over 200,000 records in it.

Right now, I don't have the query based on T_Assessment filtered. The
subform is linked to the parent form on Encounter_ID filed but the
underlying query is not filtered.

In trying to speed up the form, I went into the query, added
T_Encounter table, linked it to T_Assessment, and then put a criteria
in based on Encounter_ID.

I can't tell if the performance has changed. Does anyone have an
opinion on this? Is it faster to filter the underlying query, even
though I had to add a second table to the query and join them? Or Is
it faster to leave the query as one table and just allow the subform/
parentform linking to do the filtering? OR It doesn't matter?

Thanks for any help?

 

[John W. Vinson]

Hey all... I was wondering if anyone could weigh in on this
question....

I have a form with a subform. Opening the form and navigating through
the records is very slow. The subform is a continuous form.

The datasource for the main form is query based on T_Encounter and the
datasource for the subform is a query based on T_Assessment. The
T_Assessment table has over 200,000 records in it.

Right now, I don't have the query based on T_Assessment filtered. The
subform is linked to the parent form on Encounter_ID filed but the
underlying query is not filtered.

In trying to speed up the form, I went into the query, added
T_Encounter table, linked it to T_Assessment, and then put a criteria
in based on Encounter_ID.

I can't tell if the performance has changed. Does anyone have an
opinion on this? Is it faster to filter the underlying query, even
though I had to add a second table to the query and join them? Or Is
it faster to leave the query as one table and just allow the subform/
parentform linking to do the filtering? OR It doesn't matter?

Thanks for any help?

As a rule you should base a form on a Query that restricts the form's
recordsource to the fewest records that "work" for you. This can be done using
criteria on the query, or a filter; either way will work. A subform's
Master/Child Link Field is a special type of filter - basing the subform on a
parameter query referencing Encounter_ID would not (I would expect) be any
worse or better than using the Master/Child feature.

Optimizing queries is sometimes complex. The query should be as simple as
possible - but no simpler!! You should have Indexes on all fields used for
joins, for sorting, or for criteria; check the table design and see if the
fields are properly indexed. In particular, you should be sure that there is a
relationship between T_Encounter and T_Assessment, joining on Encounter_ID,
with referential integrity enforced. You should also be sure that the database
is split into a frontend (with the forms etc.) and backend (with the tables),
and that the backend is compacted regularly.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Access Developer]

Performance when dealing with hundreds of thousands of records (perhaps
being read over-and-over) can be puzzling. I remember having a (much)
large(r) set of records, and a colleague helped me set up a Sub-Query that
changed performance from 'abominable' to 'tolerable'.

Sorry I missed some of your information on my first reading.

Larry Linson
Microsoft Office Access MVP

 

[David-W-Fenton]

m:

In trying to speed up the form, I went into the query, added
T_Encounter table, linked it to T_Assessment, and then put a
criteria in based on Encounter_ID.

There will be no performance improvement with that. Subform
filtering is very efficient and so you can't really optimize it this
way.

Are you having slow performance? If so, you might want to look at
your indexing and sorting to see if that might be the source of the
problem.

 

[Infocore]

Thanks everyone for your posts and adding to this. Filtering the
query behind the form and using the parent/child linking did seem to
speed up the performance. Especially going from parent record to
parent record.

I do have the database split between front end and back end. I
compact and repair regularly.

I also have relationships set up between T_Encounter (the parent
record) and T_Assessment (the child record).

Each table has its own Primary Key field. T_Assessment has a field
named "Encounter_FK" (foreign key).

In Access there isn't really a way, like Primary Key, to identify a
field as a Foreign Key. Is there? We use the naming convention _FK
to identify fields storing foreign keys. Should these keys be indexed
(duplicates ok)?

Thanks.

 

[John W. Vinson]

In Access there isn't really a way, like Primary Key, to identify a
field as a Foreign Key. Is there? We use the naming convention _FK
to identify fields storing foreign keys. Should these keys be indexed
(duplicates ok)?

There isn't specifically a foreign-key field type or identifier; a FK is a FK
by virtue of how it's being used, not by anything different about the field
itself.

If you create a Relationship in the relationships window (or, yuck!, using it
in a Lookup Field) Access will automatically index the field for you. It is
neither necessary nor useful to create another index.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David-W-Fenton]

There isn't specifically a foreign-key field type or identifier; a
FK is a FK by virtue of how it's being used, not by anything
different about the field itself.

I have always used naming conventions like this:

tblMyTable -- PK is MyTableID

Any field ending in ID is a PK of some table, and you can tell if
it's a PK or a FK by comparing it to the table name. This works in
all cases except for recursive relationships, since the name of the
recursive key field can't be the same as the PK of the table it's
recursively pointing to. But I think it would count as a FK,
nonetheless.

There's also the issue of CONSTRAINTs, which were added in Jet 4.0,
but creatable only via DDL (not exposed in DAO, GRRRRRR). If you set
up your foreign keys as constraints instead of as relationships, you
could then check if a foreign-key constraint exists on any fields,
but I don't know how you do that (likely through ADO, as DAO seems
to know nothing of constraints).

If you create a Relationship in the relationships window (or,
yuck!, using it in a Lookup Field) Access will automatically index
the field for you. It is neither necessary nor useful to create
another index.

Really? I always create indexes on my foreign keys, even when fully
enforced with RI. I thought that if the index already existed,
Jet/ACE would silently use it, instead of creating a second one? I
just checked one of my tables, and there's an index on the main
foreign key, but also an index that represents the relationship. I
don't know if this means there are really two indexes in the actual
binary structures where the table's indexes or stored, or if there
are just two index definitions pointing to the same binary
structure.