SQLOLEDB doesn't support indexes? - ADO unusable - TEAMB please read/reply

No solution, but I have a similar problem with D6 Pro ADO & Access/Jet and
provider :
MS Jet OLEDB 3.51. I have tried every different setting available, but get
no improvement
over a Locate time of 8-9 seconds for the last record in a 100,000 record
table. I have
also tried this with both MDB & MDE tables for same result.

The Seek is only available for Jet/Access 2000 and later.

As an example, Diamond DAO takes a fraction of a second for the same locate.
Diamond DAO takes the same time to read all records in table as the ADO
locate.
(8 seconds). This indicates that DAO is not using the index.

An explanation / reply would be good, because ADO is not an option to use
with
such bad response times.

TIA
Brian

Quote
DaveO <D...@ALMSystems.co.nz> wrote in message news:3c1552eb_2@dnews...

Good Morning/Evening

I'm using D5 pro connecting to SQL Server 2000 via ADO using SQLOLEDB as
provider in my connection string.

I have a table which has grown thus far to 200,000+ rows and is set to
continue to rise to the milions in the coming months. Each week I have to
load or update yet more rows which I do quite simply by

IF LOCATE('PK','Value'[]) THEN
  BEGIN
  SET FIELDS...
  POST
  END ELSE
  BEGIN
  INSERTRECORD...
  END

This job is taking longer and longer as the table grows. The table has a
valid primary key index defined but my testing shows that it seems not to be
used hence the enormous delay in locating the records - as much as a second
per locate statement!

When I finally got around to experiamenting with the SEEK statement I got a
runtime EOleException error message which says "Current provider does not
support the necessary interface for Index functionality" A similar design
time error message can be gotten if you try to select an index for the
TADOTable.IndexName property on an open (Active=True) table.

So it would seem to me that for some reason the SQLOLEDB provider doesn't
allow the use of indexes - or maybe I've not set something quite right.

Does anyone know of a way around this so that my program can search via an
index and still use ADO/SQLOLEDB?

Regards
Dave