By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,728 Members | 2,462 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,728 IT Pros & Developers. It's quick & easy.

Custom Field in Query Slows it Down Dramatically

P: n/a
I have a database I'm designing in Access 97. I have a custom field in a
query 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 became 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.)

PS--the query as designed BEFORE instituting this change:

SELECT [Table of Documents].DocID, [Table of Documents].DocTitle, [Table of
Documents].DocStatus, [Table of Documents].DocClass, [Table of
Documents].DocReviewDays, (SELECT Max(ToRevision) FROM [Table of Revisions]
WHERE [Table of Revisions].DocID = [Table of Documents].DocID) AS [Latest
Revision Number], [DocReviewDays]+[DocLastReviewed] AS NextRevDate
FROM [Table of Documents]
WHERE ((([Table of Documents].DocStatus)="Active") AND (((SELECT
Max(ToRevision) FROM [Table of Revisions] WHERE [Table of Revisions].DocID =
[Table of Documents].DocID))<>"OCO"))
ORDER BY [Table of Documents].DocID;
LRH
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Larry R Harrison Jr wrote:
I have a database I'm designing in Access 97. I have a custom field
in a query 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 became necessary, the query now runs
DRASTICALLY slower. Before, it took less than a second to activate.
Now, it takes 15-30 seconds
to activate.


I don't understand how [Approved] is a "custom field". If it is an actual
field in the [Table of Revisions] table is it indexed? It should be.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #2

P: n/a
"Larry R Harrison Jr" <no***@noone.net> wrote in message
news:ROwKd.11367$rw.7386@fed1read04...
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)


I am going to assume that revsions does have a autonumber key id, you can
thsu try:

Latest_Revision_Number: (SELECT top 1 ToRevision FROM [Table of Revisions]
WHERE [Table of Revisions].DocID = [Table of Documents].DocID
AND
[Table of Revisions].Approved = Yes
order by desc ToRevision, ID )

Of course, docID has a index on it...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
Nov 13 '05 #3

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your query formatted for clarity's sake:

SELECT D.DocID,
D.DocTitle,
D.DocStatus,
D.DocClass,
D.DocReviewDays,
(SELECT Max(ToRevision)
FROM [Table of Revisions] R
WHERE R.DocID = D.DocID) AS [Latest Revision Number],
[DocReviewDays]+[DocLastReviewed] AS NextRevDate

FROM [Table of Documents] D

WHERE D.DocStatus = "Active"
AND "OCO" <> (SELECT Max(ToRevision)
FROM [Table of Revisions] R
WHERE R.DocID = D.DocID)

ORDER BY D.DocID

Is the column "ToRevision" a numeric or alpha data type? I ask, 'cuz in
the main query's SELECT clause you have a subquery that seems to return
a numeric value, yet in the main query's WHERE clause you are comparing
the same result set against an alpha string ("OCO"). If the result set
returns a numeric value the expression will always evaluate to True,
'cuz a number won't equal "OCO."

This subquery:

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

Place an index on [Table of Revisions].Approved. I know, many db
wizards say it's improper to place an index on a True/False column.
Well, I've done it in the past & have had my queries improve in speed.
Try it & decide for yourself.

You could also create a multi-column index on [Table of Revisions]
columns Approved & DocID. E.g.:

CREATE INDEX idx_DocID_Approved
ON [Table of Revisions] (Approved, DocID)

This will create an index of all the DocIDs grouped by Approved value:

Approved DocID
True 22
True 33
True 2500
False 88
False 103
.... etc. ...

HTH,

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQfq1NYechKqOuFEgEQLJpQCdFCa3dHvpeF5+RTfwDt7WUo sv0ZMAoM6l
hFlT6+X5SNQjPpWqPSR+Qep/
=DGz+
-----END PGP SIGNATURE-----
Larry R Harrison Jr wrote:
I have a database I'm designing in Access 97. I have a custom field in a
query 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 became 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.)

PS--the query as designed BEFORE instituting this change:

SELECT [Table of Documents].DocID, [Table of Documents].DocTitle, [Table of
Documents].DocStatus, [Table of Documents].DocClass, [Table of
Documents].DocReviewDays, (SELECT Max(ToRevision) FROM [Table of Revisions]
WHERE [Table of Revisions].DocID = [Table of Documents].DocID) AS [Latest
Revision Number], [DocReviewDays]+[DocLastReviewed] AS NextRevDate
FROM [Table of Documents]
WHERE ((([Table of Documents].DocStatus)="Active") AND (((SELECT
Max(ToRevision) FROM [Table of Revisions] WHERE [Table of Revisions].DocID =
[Table of Documents].DocID))<>"OCO"))
ORDER BY [Table of Documents].DocID;
LRH

Nov 13 '05 #4

P: n/a
I'm going to have to check out "MGFoster's" post sometime.

Quickly: yes, the fields WERE indexed originally, all the relevant
ones. For some weird reason, having that 2nd parameter (checking if
Approved=Yes) really dramatically slows things down even WITH the
fields indexed.

In my case, I ended up creating a temporary table in code that's just
like Revisions except it only has {Approved=Yes} records included, and
it periodically deletes & re-creates the table at crucial points so it
stays current. Then, the query uses the TMP table in place of the
Revisions table, that dramatically improved performance.

I don't know if that's the best way--it looks like MGFoster may be on
to something--but it was all I could think of at the time.

LRH

Nov 13 '05 #5

P: n/a
<la********@yahoo.com> wrote
Quickly: yes, the fields WERE indexed originally, all the relevant
ones. For some weird reason, having that 2nd parameter (checking if
Approved=Yes) really dramatically slows things down even WITH the
fields indexed.


Indexing a boolean field will slow down your queries. The idea of an index
is to create an efficient search. An indexed search on a boolean field is
not more efficient, and in fact creates considerable overhead.

Also, I recall that indexing fileds with many Null values may also be
not-beneficial, if not bad.
Darryl Kerkeslager
Nov 13 '05 #6

P: n/a

Darryl Kerkeslager wrote:
<la********@yahoo.com> wrote
Quickly: yes, the fields WERE indexed originally, all the relevant
ones. For some weird reason, having that 2nd parameter (checking if
Approved=Yes) really dramatically slows things down even WITH the
fields indexed.
Indexing a boolean field will slow down your queries. The idea of an

index is to create an efficient search. An indexed search on a boolean field is not more efficient, and in fact creates considerable overhead.

Also, I recall that indexing fileds with many Null values may also be
not-beneficial, if not bad.
Darryl Kerkeslager


Bingo, Darryl. That was it. The Approved field (a boolean field) was in
fact indexed, I un-indexed it, went back to my earlier revision (which
did NOT use temp tables) and voila--the query was DRAMATICALLY faster.
LRH

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.