Query speed - can you do better?

[Andy]

Hi guys

Using Access 2003 on a P4 with 1mb ram so should have sufficent
processing power.

I run a parameterized query to find a number which is between column 1
('Beginning') and column 2 ('Ending').

I call the query, pass the parameter and it is taking 9-10 seconds to
return the result.

The table it is querying has 4.4 million records so maybe this is the
best it can do.

Both the Beginning and Ending columns have an index.

Below is the query code - any suggestions would be appreciated.

Thanks....Andrew
Bendigo Australia

===================================================================

PARAMETERS IPNumber IEEEDouble;
SELECT IPdata.Beginning, IPdata.Ending
FROM IPdata
WHERE (((IPdata.Beginning)<[IPNumber]) AND
((IPdata.Ending)>[IPNumber]));

===================================================================

 

[John Spencer]

I don't see anything that would speed up the query. The length of time the
query is taking could be dependent on the number of records it is returning
rather than the efficiency of the search.

 

[Jeff Boyce]

Andy

Just a curiosity ... what kinds of values are being stored in [Beginning]
and [Ending]?

Also, have you tried testing how long a query takes if you only look for
values, say, greater than [Beginning]? Might it be the combination of
begin/end in the same query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Both the Beginning and Ending columns have an index.

Is that two indexes or one index for both? One index could be better.

Just because there is an index or two doesn't mean that Access is using
them. Check out ShowPlan to see how the query is being executed.

http://builder.com.com/5100-6388_14-5064388.html

< 10 seconds isn't too bad actually. If I was desperate for more speed, I'd
try rebuilding the table by inserting the records in Beginning and Ending
order. Actually it isn't suppose to matter on the order of records in a table
but sometimes.... You could also drop the index and recreate it. If there's
been a lot of table and record changes, the index could be somewhat
disorganized.

 

[Andy]

Thanks for all the responses guys.

The query is only returning one record so the time is in the search not
in returning the result.

The data type of the Beginning and Ending columns are 'Double' as is
the parameter being passed so there shouldn't be an issue here.

I have an index on both the Beginning and Ending columns so I will try
it with the one index only.

I will also have a look at Showplan to see if offers anything (thanks
Jerry).

I haven't done any testing on times if say just '> Beginning' but this
won't help because the way the algorithm works is it needs to find the
value between 'Beginning' and 'Ending'.

The data in both columns is sort in numerical order (the indexes should
take care of that anyway).

Of course the answer would be SQL however our corporate environment
doesn't allow that at this stage.

The single index option may be the only possibility to improve things I
suspect.

Thanks again for your responses.....

Andrew
Bendigo, Australia

Is that two indexes or one index for both? One index could be better.

Just because there is an index or two doesn't mean that Access is using
them. Check out ShowPlan to see how the query is being executed.

http://builder.com.com/5100-6388_14-5064388.html

< 10 seconds isn't too bad actually. If I was desperate for more speed, I'd
try rebuilding the table by inserting the records in Beginning and Ending
order. Actually it isn't suppose to matter on the order of records in a table
but sometimes.... You could also drop the index and recreate it. If there's
been a lot of table and record changes, the index could be somewhat
disorganized.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi guys

Using Access 2003 on a P4 with 1mb ram so should have sufficent
processing power.

I run a parameterized query to find a number which is between column 1
('Beginning') and column 2 ('Ending').

I call the query, pass the parameter and it is taking 9-10 seconds to
return the result.

The table it is querying has 4.4 million records so maybe this is the
best it can do.

Both the Beginning and Ending columns have an index.

Below is the query code - any suggestions would be appreciated.

Thanks....Andrew
Bendigo Australia

===================================================================

PARAMETERS IPNumber IEEEDouble;
SELECT IPdata.Beginning, IPdata.Ending
FROM IPdata
WHERE (((IPdata.Beginning)<[IPNumber]) AND
((IPdata.Ending)>[IPNumber]));

===================================================================