Prev: Querying for most recent value
Next: Print Order Sort - 3 sections per page - to be cut and stacked
From: Kevin Myers on 4 Jun 2010 18:42
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



From: Tom van Stiphout on 6 Jun 2010 11:41
On Fri, 4 Jun 2010 17:42:32 -0500, "Kevin Myers"
<kevinmyers_nospam(a)austin.rr.com> wrote:

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


>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
>
>
From: John Spencer on 6 Jun 2010 15:44
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

Tom van Stiphout wrote:
> On Fri, 4 Jun 2010 17:42:32 -0500, "Kevin Myers"
> <kevinmyers_nospam(a)austin.rr.com> wrote:
>
> 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
>
>
>> 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
>>
>>
From: Bob Barrows on 6 Jun 2010 15:45
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"


 | 
Pages: 1
Prev: Querying for most recent value
Next: Print Order Sort - 3 sections per page - to be cut and stacked