Custom Field in Query Slows Query Drastically

[Larry R Harrison Jr]

I have a database I'm designing in Access 97. I have a custom which looks in
{Table of Documents} and shows them all. It then needs a "latest revision
number," stored in another table named {Table of Revisions}. It naturally
matches them up by linking the autoid in {Doc} with the related field in
{Rev}. It then looks for a field in {Rev} called "revision number" and looks
for the last one for the given Doc (linked by the autoid field). That was
running fine & quick.

However, now I'm told I ALSO need to look for an "approved" field in the
{Rev} table and to only show the maximum one which has approved check to
"yes." So the custom field which was like this:

Latest Revision Number: (SELECT Max(ToRevision) FROM [Table of Revisions]
WHERE [Table of Revisions].DocID = [Table of Documents].DocID)

now looks like this:

Latest_Revision_Number: (SELECT Max(ToRevision) FROM [Table of Revisions]
WHERE [Table of Revisions].DocID = [Table of Documents].DocID AND [Table of
Revisions].Approved = Yes)

Since instituting this necessary, the query now runs DRASTICALLY slower.
Before, it took less than a second to activate. Now, it takes 15-30 seconds
to activate.

Why is it so much slower? Is there a better way?

(Note: yes, I know, the table names and so forth don't conform to "Reddick"
conventions. Problem is, I didn't design this database from scratch, I
inherited this design & am working within its context as such.)

LRH

 

[Marshall Barton]

I have a database I'm designing in Access 97. I have a custom which looks in
{Table of Documents} and shows them all. It then needs a "latest revision
number," stored in another table named {Table of Revisions}. It naturally
matches them up by linking the autoid in {Doc} with the related field in
{Rev}. It then looks for a field in {Rev} called "revision number" and looks
for the last one for the given Doc (linked by the autoid field). That was
running fine & quick.

However, now I'm told I ALSO need to look for an "approved" field in the
{Rev} table and to only show the maximum one which has approved check to
"yes." So the custom field which was like this:

Latest Revision Number: (SELECT Max(ToRevision) FROM [Table of Revisions]
WHERE [Table of Revisions].DocID = [Table of Documents].DocID)

now looks like this:

Latest_Revision_Number: (SELECT Max(ToRevision) FROM [Table of Revisions]
WHERE [Table of Revisions].DocID = [Table of Documents].DocID AND [Table of
Revisions].Approved = Yes)

Since instituting this necessary, the query now runs DRASTICALLY slower.
Before, it took less than a second to activate. Now, it takes 15-30 seconds
to activate.

Why is it so much slower? Is there a better way?

(Note: yes, I know, the table names and so forth don't conform to "Reddick"
conventions. Problem is, I didn't design this database from scratch, I
inherited this design & am working within its context as such.)

Do you have an Index for the Approved field?

Indexes can dramatically improve query performance, but for
a Yes/No field with only two possible values, it might not
do much.