Kevin Myers wrote:
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/

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"