Non-Binding Constraint Speeds Query

J

John Slattery

I added a condition to the WHERE clause of a query that does not limit the
number of records returned and saw the execution time of the query decrease
dramatically. What's going on?

The query returns a little over a half million records from a database that
is about 80 MB in size and is somewhat complex in that it joins 24 tables.
The condition is that an incrementing autonumber field in one of the tables
is not equal to zero. Without this condition, I find that after ten minutes
about 20,000 records have been accessed, but with it, the entire half
million records are accessed in about 90 seconds.

By accessed, I mean this: launch the query from the database window by
double clicking and immediately press ctrl-down arrow to go to the last
record of the datasheet. With the constraint, I see the last record in 90
seconds. Without it, I give up at some point by pressing ctrl-break and
then hold the pgdn key until new pages of the datasheet suddenly nearly stop
appearing. After 10 minutes, this occurs at about 20,000 records.

Thanks,

John
 
M

MGFoster

John said:
I added a condition to the WHERE clause of a query that does not limit the
number of records returned and saw the execution time of the query decrease
dramatically. What's going on?

The query returns a little over a half million records from a database that
is about 80 MB in size and is somewhat complex in that it joins 24 tables.
The condition is that an incrementing autonumber field in one of the tables
is not equal to zero. Without this condition, I find that after ten minutes
about 20,000 records have been accessed, but with it, the entire half
million records are accessed in about 90 seconds.

By accessed, I mean this: launch the query from the database window by
double clicking and immediately press ctrl-down arrow to go to the last
record of the datasheet. With the constraint, I see the last record in 90
seconds. Without it, I give up at some point by pressing ctrl-break and
then hold the pgdn key until new pages of the datasheet suddenly nearly stop
appearing. After 10 minutes, this occurs at about 20,000 records.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

My guess: Since autonumbers are usually assigned an index, if named
something like "account_id," and, autonumbers do not start at zero, then
the query plan "sees" that the query should return all records (IOW,
there aren't any records w/ Zero in the autonumber index). When you add
the more specific WHERE condition the query plan must be deciding to
scan the whole table to find records that meet that condition. To speed
up the query try indexing the column(s) in your WHERE clause. This will
prevent the query from scanning the whole table, thus increasing the
speed of your query.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQwzRpoechKqOuFEgEQKvWgCaA9566JTlUTPH3yEQzwlh1L9/JbkAnj3Q
/dOn6M6M56g2m4Wyp5Uj3A7m
=aatb
-----END PGP SIGNATURE-----
 
J

John Slattery

Thanks for the response MG.

Not sure if we're on the same page. I'm saying that execution is quicker
with the additinal element in the WHERE clause. Here's a synopsis of the
query that is slow (probably should have included this in the original
post):

PARAMETERS
Forms!frmCSMCPFinance!txtstrCurrentUserSym TEXT (20)
;
SELECT
...
(40 or so output items)
...
FROM
...
(24 joined tables)
...
WHERE
tblUser.strSym = Forms!frmCSMCPFinance!txtstrCurrentUserSym
;

And following is a synopsis of the query that is fast:

PARAMETERS
Forms!frmCSMCPFinance!txtstrCurrentUserSym TEXT (20)
;
SELECT
...
(40 or so output items)
...
FROM
...
(24 joined tables)
...
WHERE
NOT tblSigPeriod.lngID = 0
AND
tblUser.strSym = Forms!frmCSMCPFinance!txtstrCurrentUserSym
;

As you surmised, there is a unique index on tblSigPeriod.lngID and there are
no records in tblSigPeriod where lngID = 0.

To your suggestion to index the fields in the WHERE clause, I think I have
that covered. tblUser.strSym is also uniquely indexed. (Fields involved in
joins, too, are indexed.)

Thanks Again,

John
 
Top