OleDB provider not using index

Anders Altberg

2006-12-25 12:52:16 UTC

Your DBF is either member of a DBC or it isn't. That makes a difference in
how you setup the connection string. The CDX index has no business appearing
in the connection string whatsoever. The DBF knows from it's own header
whether there's a production CDX for it or not. If there is one it has the
same name as the dbf and will be opend automatically. Simple.
To take advantage of the index for optimizing queries you have to know the
indexing expression , and you have to use a WHERE clause whose lefthand side
EXACTLY tallies with the a key in the index. If there's an index on
UPPER(lastname) you use expressions like
... WHERE UPPER(lastname) ='SMITH'
... WHERE UPPER(lastname)=UPPER(userinput)
VFP can then parse the search condition and see that it matches a particular
index in the CDX.

-Anders

Post by s***@gmail.com
I've got a large FoxPro table that I'm trying to query from an ASP.NET
application. When I open the .dbf in FoxPro, the query takes .02
seconds to return the 11 records. When I run the query via the OleDb
provider (version 1.1 from January 2006), it takes over 20 seconds. So
1) using the directory where the .dbf and .cdx are as the source in the
connection string
2) using the .dbf file as the source in the connection string
3) creating a .dbc file and using that as the source in the connection
string
Is there anything else that can be done to force the OleDB provider to
use the index? I've seen references to SET OPTIMIZE ON and SET INDEX
TO, but both of those bomb when submitted as commands via the OleDB
provider.
Thanks,
Bob