"order by" slow on indexed field

[Jochen L. Kaiser]

Hi,

I have a table "tab1" with 200.000 records and an indexed
field "field1" in a ms access2000 database. (secondary index)

There are a couple of curious things:

- Querying "select * from tab1 order by field1"
from within access is quite fast. (So the index seems to work here)
Adding a top clause like
"select top 10 * from tab1 order by field1"
seems to ignore the index, so the query is very slow

- Executing the query from ADO (serverside cursor)
"select * from tab1 order by field1"
takes a long time - with and without the top clause - so
you can assume that the index has not been used.

What is wrong here? Do I miss something?
What con I do speed up getting the top n records of this
query using ADO?


Thanks,

-joe

 

[rich]

Joe,

the "select * from tab1 order by field1" just dumps and already ordered
structure so little overhead, the top10 has to do some work.

Now with ADO it is basically a layer for OLE DB, which then communicates
with the DBMS. I would suggest dropping the ADO bit and store a query
server side, ideally a stored procedure. Shouls your query be as simple
as "select * from tab1 order by field1" for the return recordset, stick
with the stored query query and call it from VBA. But I do have to ask
why you would want to return 200,000 rows to a form...!

Rich

 

[Jochen L. Kaiser]

Hi rich,

Now with ADO it is basically a layer for OLE DB, which then communicates
with the DBMS. I would suggest dropping the ADO bit and store a query
server side, ideally a stored procedure. Shouls your query be as simple

you are right - but in my case i am forced to use jet only - without
a server dbms.

But I do have to ask why you would want to return 200,000 rows to a form...!

I just wanted to retrieve the top 100 of a sorted list of 200000
records. So it is very important that the existing index is used,
otherwise the query would be executed too slow.
It seems that the problem with jet starts as I add the top-clause to
the query. As far as i can see the top-keyword prevents the use of any
index. My solution so far is to open the cursor without any top-clause
and grap only the first 100 records.

-joe

 

[rich]

Ah!!

The plot thickens!!!

I know with ORACLE the optimizer can drop the use of an index when
certain functions are called (in fact most functions) so this is
probably the case here. An example is an index on a field called
surname, if you do a search and use UCASE (for forcing uppercase) the
optimizer forgets the index and goes direct to the main table, this is
probably the case for you!

So how do we overcome your problem? Well i guess you are grabbing your
records 100 at a time, but do they have to come in any order, ie, first
100 surnames starting with A going onto Z?

If you can clear this for me i might be able to help.

Thanks

Rich