A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Optimization of Between Clause Queries



 
 
Thread Tools Display Modes
  #1  
Old June 4th, 2010, 11:42 PM posted to microsoft.public.access.queries
Kevin Myers
external usenet poster
 
Posts: 1
Default Optimization of Between Clause Queries

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



  #2  
Old June 6th, 2010, 04:41 PM posted to microsoft.public.access.queries
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Optimization of Between Clause Queries

On Fri, 4 Jun 2010 17:42:32 -0500, "Kevin Myers"
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


  #3  
Old June 6th, 2010, 08:44 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Optimization of Between Clause Queries

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"
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


  #4  
Old June 6th, 2010, 08:45 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Optimization of Between Clause Queries

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"


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:57 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.