Re: Cannot set CDX index on DBF file from C#

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Cindy,

Following your advice, I have had a good deal of success with speeding up
SQL queries from C# against my large foxpro DBF/CDX table.

I have a field called STOREDATE which is a Date field (surprise, surprise)
and I have an index in the CDX file whose expression is just STOREDATE, and
so SQL queries with clause WHERE STOREDATE = {^ some date} are now going
lightening fast - from 30 seconds plus, down to less than 0.5 of a second
to return 300 hundred records from over 1 million (still under 1 second to
do a short BETWEEN) which is a great improvement!

However, my other index is a combined expression, and is as slow as ever.
Expression in this second index is STORENAME + DTOS(STOREDATE) + STOREVALUE.
STORENAME and STOREVALUE are character fields. So if I run a query such as
SELECT * FROM STORE WHERE STORENAME = 'SPEED' AND STOREDATE ={^2006/01/01}
it continues to run without apparently using the index. Running this SQL
without the AND STOREDATE = {^2006/01/01} clause also runs slow.

>From your last post, it occurs to me that I might only be able to use WHERE
clauses based on the whole expression, not just the 'most significant'
elements (not so ueseful), or perhaps the DTOS is causing problems. Any
thoughts?

Don



.