Optimization of Between Clause Queries

[Kevin Myers]

I have some sql queries involving the use of between clauses, similar to the
following:

select Colors1.*, sum(Colors2.freq) as TFreq
from OldColors as Colors1, OldColors as Colors2
where Colors2.r between Colors1.r - 7 and Colors1.r + 7
and Colors2.g between Colors1.g - 7 and Colors1.g + 7
and Colors2.b between Colors1.b - 7 and Colors1.b + 7
group by Colors1.r, Colors1.g, Colors1.b;

The OldColors table has several indexes, including one that is defined as
follows:
create index RGB on OldColors(r, g, b);

Can anyone tell me with a high degree of certainty whether the jet 4.0
database engine can optimize the above query based on this index?
Does jet 4.0 *ever* optimize queries with WHERE clauses that only consist of
BETWEEN clauses?

Thanks,
KM

 

[Tom van Stiphout]

On Fri, 4 Jun 2010 17:42:32 -0500, "Kevin Myers"

A crude way to find out is to time the query, drop the index, and time
it again. My guess is that Access indeed uses the index.
Unfortunately Access doesn't have built-in tools to analyze queries at
this level, unlike SQL Server.

-Tom.
Microsoft Access MVP

 

[John Spencer]

You really need to have a separate index on each of the three fields to get
performance.

Your index could get used for the r field, but it would be useless for the
other two fields.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Bob Barrows]

I have some sql queries involving the use of between clauses, similar
to the following:

select Colors1.*, sum(Colors2.freq) as TFreq
from OldColors as Colors1, OldColors as Colors2
where Colors2.r between Colors1.r - 7 and Colors1.r + 7
and Colors2.g between Colors1.g - 7 and Colors1.g + 7
and Colors2.b between Colors1.b - 7 and Colors1.b + 7
group by Colors1.r, Colors1.g, Colors1.b;

The OldColors table has several indexes, including one that is
defined as follows:
create index RGB on OldColors(r, g, b);

Can anyone tell me with a high degree of certainty whether the jet 4.0
database engine can optimize the above query based on this index?
Does jet 4.0 *ever* optimize queries with WHERE clauses that only
consist of BETWEEN clauses?

Thanks,
KM

Behind the scenes, Jet converts your between criteria into the <= and >=
form, so the "between" concern is a red herring. Of more immediate concern
is the fact that you are performing calculations on field values in the
criteria so, from what I can see, the index cannot be used on the Colors1
side. You are forcing a scan on Colors1. The Colors2 derived table may
benefit from the index but, given that this is not an equi-join, performance
is not going to be great, anyways. This article explains why:
http://www.sqlservercentral.com/articles/T-SQL/61539/ .

Incidently, you can look at the Jet execution plans using SHOWPLAN. See:
http://www.sqlservercentral.com/articles/T-SQL/61539/