"order by" slow on indexed field

From: Jochen L. Kaiser (jlkaiser_at_starcon.de)
Date: 04/20/04 

Date: 20 Apr 2004 00:54:27 -0700

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

Relevant Pages

  • Re: Help with Crosstab Report
    ... The table that this query is run on gets populated using ADO - I have ... noticed that the Customer field is being padded with a space at the ... > If I remove the WHERE clause then the query returns the expected results ...
    (microsoft.public.access.reports)
  • Re: Help with Crosstab Report
    ... > The table that this query is run on gets populated using ADO - I have ... > noticed that the Customer field is being padded with a space at the ... >> If I remove the WHERE clause then the query returns the expected results ...
    (microsoft.public.access.reports)
  • RE: Any good T-SQL quick reference recommended?
    ... The full syntax of the SELECT ... SELECT Clause ... Specifies the columns to be returned by the query. ... Specifies that duplicate rows can appear in the result set. ...
    (microsoft.public.sqlserver.programming)
  • RE: Xlocking with a select statement
    ... named query expression, order clause, update clause, lock option ... A result table or the underlying base tables are updateable if the query ... A lock can be requested for the ...
    (microsoft.public.sqlserver.programming)
  • RE: ODBC query in VB code Need HELP
    ... Everything for ADO is in the first 2 messages that I gave you. ... Since your pass-through query already exists (including the ... Dim STRSQL As String ...
    (microsoft.public.access.formscoding)