Recordset ORDER BY using specific Index
Posted on 2008-10-17
I have an Access mdb with a table called CITY.
The PrimaryKey is a CityID, but there is also a field CITY that
is text and is Indexed with an Index called CITY.
When opening a recordset with a query like this:
sql$="Select top 50 * from CITY WHERE (Foreign=0) ORDER BY CITY"
Set rs = dat.OpenRecordset( sql$, dbOpenSnapshot, dbReadOnly)
It takes a long time (3-4 seconds) to return. If the ORDER BY
is left out the recordset opens immediately, but of course is
not ordered alphabetically by City name. Table is really big
I would have thought that the ORDER BY CITY would make the
JET Engine realize that it already has an Index CITY that could
be used to walk through these records in this order, but the
only explanation I have for the time delay is that it must be
sorting this table instead of using the built-in index.
Is there a way to force the query to use the Index we already
have so that it is much faster?