Cannot set CDX index on DBF file from C#

[Don K]

Hi,

I am using .NET framework 2.0 to open Foxpro DBF files. I have
successfully opened DBF files from C# using vfpoleddb driver (VFP 9.0)
and SQL SELECT statements.

The problem is that while the CDX index file is being opened as part of
the connection / command process, none of the actual indexes (ie TAGs)
within the CDX file are being selected, so when I run a select SQL
against the DBF file which has 1 million plus records it is slow
because no index is in use. I have also verified this by temporarily
deleting the CDX file and the SQL select takes the same slow time.
(Note no .IDX or .NTX or .MTX indexes involved here - the CDX index is
working fine for foxpro apps)

So its not so much a question of "cannot" set an index, rather "don't
know how" to set an index from C#.

How do I know the CDX file is being opened?

Because "SELECT TAG(1) FROM testfile.dbf", correctly returns the
first tag name in the CDX and "SELECT TAG(2) FROM testfile.dbf",
correctly returns the secong tag name. I get same data if I request
the schema indexes table, as in "oledbConn.GetSchema("INDEXES");"

Also when I execute "SELECT ORDER() FROM testfile.dbf" I get an empty
string back.

I do not want to create any new indexes, just use the perfectly good
existing ones! Does anyone know how to do "SET INDEX TO ORDER 1" in C#
using OleDbConnection and OleDbCommand objects, or some other technique
which would achieve the same end?

Don

[Cindy Winegarden]

"Don K" <***@dlkennedy.fsnet.co.uk> wrote in message news:***@o13g2000cwo.googlegroups.com...

Hi Don,

Post by Don K
I am using .NET framework 2.0 to open Foxpro DBF files. I have
successfully opened DBF files from C# using vfpoleddb driver (VFP 9.0)
and SQL SELECT statements.
The problem is that while the CDX index file is being opened as part of
the connection / command process, none of the actual indexes (ie TAGs)
within the CDX file are being selected, so when I run a select SQL
against the DBF file which has 1 million plus records it is slow
because no index is in use. ....
So its not so much a question of "cannot" set an index, rather "don't
know how" to set an index from C#.

As you may know, the FoxPro Index command requires exclusive use of the
table. The Index command is not supported via ODBC or OLE DB. You can,
however, set a PrimaryKey index via the Alter Table command.

Post by Don K
How do I know the CDX file is being opened? ......

The FoxPro data engine takes care of automatically opening CDX index files
when a table is opened. What you're really asking is how do you know if your
SQL commands are taking advantage of FoxPro's Rushmore index technology. The
Rushmore technology kicks in to optimize the Where clause of an SQL
statement whether the table's order is set to that index or not. For example
...Where CustomerID = 5 And CustomerName = "Smith" would run best if there
were indexes on both CustomerID and CustomerName. (Obviously you can't set
the index to both orders at once.)

Post by Don K
....Does anyone know how to do "SET INDEX TO ORDER 1" in C#
using OleDbConnection and OleDbCommand objects, or some other technique
which would achieve the same end?

As above, you don't kneed Set Index To at all. What you need to do is make
sure the expressions in the Where clause of your SQL statements. What are
the expressions in your Where clause and do you know what the index
expressions are on your table?

One more thing - OLE DB adds another level of complexity to data retrieval,
so it's never going to be as fast as native FoxPro data retrieval. I've seen
newsgroup posts complaining about extreme slowness and never seen a good
solution to the problem.

[Don K]

Cindy,

Thanks for your reply on this - glad to be speaking to someone who knows
what they are talking about!

You have cleared up my main misconception, that is that having opened the
CDX file, OLEDB also opens all indexs (tags) and will use them as required,
based on the selections in the SQL "WHERE" clauses (if I understand you
correctly). In the DBF world (mainly clipper) I am used to opening a DBF and
then choosing an index to go with, and had assumed that VFPOLEDB would use
the same process.

I can see the expressions in the CDX files (via a DBF tool) so I should be
able to use these to build appropriate WHERE clauses in SQL. Do you know if
there is any property in VFPOLEDB that I can use to see if I "hit the
Rushmore jackpot" for a given query or is it just a case of seeing whether
or not the SELECT process speeds up?

An associated question - do SQL INSERT and UPDATE statements keep the CDXs
updated?

Don

[Cindy Winegarden]

"Don K" <***@1.com> wrote in message news:dr0vm3$bk$***@news8.svr.pol.co.uk...

Hi Don,

Post by Don K
I can see the expressions in the CDX files (via a DBF tool) so I should be
able to use these to build appropriate WHERE clauses in SQL. Do you know
if there is any property in VFPOLEDB that I can use to see if I "hit the
Rushmore jackpot" for a given query or is it just a case of seeing whether
or not the SELECT process speeds up?

In the Visual FoxPro IDE you can issue the SYS(3054) command to see the
Rushmore optimization level, but this is not available via OLE DB. Please
note that your expression must exactly match the index expression. For
example, if the index expression is Upper(LastName) you won't be optimized
to look for ...Where LastName = "SMITH"... but you will be if you look for
....Where Upper(LastName) = "SMITH"...

Post by Don K
An associated question - do SQL INSERT and UPDATE statements keep the CDXs
updated?

Yes, the FoxPro data engine automatically updates the indexes. This was not
the case with the older IDX index files.

[Don K]

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

[Jay B]

to optimize
SELECT * FROM STORE WHERE STORENAME = 'SPEED' AND STOREDATE {^2006/01/01}
you must have indexes on storename and a separate index on storedate.

Post by Don K
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

[Cindy Winegarden]

Hi Don,

First off, DTOS() is DateToString which makes {^2005/01/23} into "20050123".
To take advantage of this index you'd need an expression like ... Where
StoreName + < something here to convert your date to a string > + StoreValue
= SomeValue. Don't forget, FoxPro fields are fixed width, so assuming your
StoreName field is longer than C(5) you will need to pad with spaces, making
your expression something like ... = "SPEED 20050123STOREVALUE " .....

If you don't want to put the name, date, and value together then, as Jay
said, you'll need separate indexes on name, date and value.
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP

Post by Don K
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?

[Olaf Doschke]

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}

1. Read what Cindy told you about DTOS and the expression you need to generate.
2. Then the optimizable SQL is:
SELECT * FROM STORE WHERE
STORENAME + DTOS(STOREDATE) + STOREVALUE = 'SPEED 20060124...'

As the = operator in Foxpro is not exact match but only compares up the the length of
the right hand side string you are able to search for 'SPEED' only too:

SELECT * FROM STORE WHERE
STORENAME + DTOS(STOREDATE) + STOREVALUE = 'SPEED'
will be optimized, as it would also use the index on STORENAME + DTOS(STOREDATE) +
STOREVALUE and only compare the left part. This finds all record where STORENAME
starts with 'SPEED'.

But rushmore isn't intelligent enough to see that for the sql select
SELECT * FROM STORE WHERE
STORENAME = 'SPEED'
it could also use the index on STORENAME + DTOS(STOREDATE) + STOREVALUE.
this won't be optimized and runs slow.

You really need the exact index expression within the sql, rushmore reacts only to the full
expression, even if you don't use the full expression with your filter string. But then, when
you use the full expression, then rushmore looks up values in the index.

By the way: Still an index on the sinlge field STORENAME could speed it up more,
as the shorter the index is, the shorter the index tree can be, the less lookups are needed.

Bye, Olaf.

[Don K]

Olaf,

Thanks for your input. While Cindy had clarified the points about the
"right hand side" of the = that is that string must be padded up to the
size of the field and so on, you have made clear the left hand side...

So I could not get your exact suggestion to work (quickly enough) -

SELECT * FROM STORE WHERE
STORENAME + DTOS(STOREDATE) + STOREVALUE = 'SPEED'

but with some adjustments to the right hand side, I got the follwing to
work in C# :

SELECT * FROM STORE WHERE
STORENAME + DTOS(STOREDATE) + STOREVALUE LIKE 'SPEED 2005023%'

So change from = to LIKE, and insert a wild card after the date. Does
not work without the date part either for some reason. Anyway this does
work very fast! Less than 0.4 second to get 300 records from a table
of 2.9 million records (only 6 fields wide) on a remote server is
pretty fast in my book!

Next thing I would like to try and do is a table join using the indexes
- maybe I should be writing a Foxpro stored procedure (if they exist)
and calling it from C# rather trying to figure take this SQL / Foxpro
hybrid any further...

Don

[Olaf Doschke]

Post by Don K
So I could not get your exact suggestion to work (quickly enough) -
SELECT * FROM STORE WHERE
STORENAME + DTOS(STOREDATE) + STOREVALUE = 'SPEED'

Hmm, I wonder why. Nevertheless you've witnessed the power of Rushmore
:-)

Post by Don K
Next thing I would like to try and do is a table join using the indexes

Rushmore can handle that too. Rushmore also handles optimization
of sql where several indexes are involved for join or where clause
optimization. Normally you'd have a primary key (pk) field in the
parent table and a foreign key (fk) field in the child(s) and then join
on parent.pk = child.fk. As you must have a primary index on the pk
field and surely have an index on the fk field this will run fast.

Post by Don K
- maybe I should be writing a Foxpro stored procedure (if they exist)

Yes, you can add stored procedures to the dbc. I'm afraid you need
vfp for that. But if there are stored procs in the dbc, you can send
"Exec procedurename(parameters)" via oledb driver. Here is some
php sample code to call a stored proc:

$conn = new COM("ADODB.Connection") or die("Cannot start ADO");
$conn->Open("Provider=vfpoledb.1;Data Source=E:\some\path\mydata.dbc;Collating Sequence=machine");
$query = "Exec NewCustomer (...)");
$result = $conn->Execute($query);
if ($result->EOF) { ... } else { $customerid = $result->Fields("customerid")->Value;}

It's not important what NewCustomer() does, you get an idea how to call
a stored proc.

Bye, Olaf.

[Cindy Winegarden]

Hi Don,

Creating cursors via stored procedure or selecting against a view would have
the same optimization from the indexes as your SQL Pass-through code does.

There are some topics in the VFP Help you'll be interested in:

How to: Access Stored Procedures on Remote Servers with SQL Pass-Through
Functions
How to: Return Multiple Result Sets
Creating Views
SETRESULTSET( ) Function
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP

Post by Don K
Next thing I would like to try and do is a table join using the indexes
- maybe I should be writing a Foxpro stored procedure (if they exist)
and calling it from C# rather trying to figure take this SQL / Foxpro
hybrid any further...

[Olaf Doschke]

Hmmm,

Post by Cindy Winegarden
How to: Access Stored Procedures on Remote Servers with SQL Pass-Through
Functions
How to: Return Multiple Result Sets
Creating Views

All these topics are related to access of remote data being remote from

Post by Cindy Winegarden
SETRESULTSET( ) Function

That is indeed helpful in vfp stored procs.
Also GETRESULTSET(), CLEARRESULTSET().

Bye, Olaf.

[Don K]

Cindy,

I do not currently have access to VFP application software, however I
might be able to buy a copy if it can be justified. In any case the
learning curve load of getting to grips with another IDE and framework
concerns me more... (My main experience is C#, ASP.NET and SQL Server)
.

So unless I can create stored VFP procedures in a text file, and call
them from C# this is currently not a goer.
Therefore my main path forwards is probably to stick with passing SQL
queries to vfpoledb provider in C#, even though it is getting more
complicated as I attempt table joins with indexes.

I am surprised that I cannot find better documentation on the web (or
on amazon) about this hybrid process of SQL and Foxpro terminology.
There is plenty of stuff about VFP on MS sites but very little on
vfpoledb - your and Olaf's assistance has therfore been of great help.

So next step would be do something like :

SELECT * FROM COMM JOIN SYM
ON COMM.SYMBOL = SYM.SYMBOL
WHERE COMM.STORENAME = 'SPEED'

Comm.dbf has an index on STORENAME and sym.dbf has an index on SYMBOL
(both in .CDX files). Sym.dbf to Comm.dbf is 1 to many relationship.

Can this work?

Don

[Olaf Doschke]

Post by Don K
SELECT * FROM COMM JOIN SYM
ON COMM.SYMBOL = SYM.SYMBOL
WHERE COMM.STORENAME = 'SPEED'
Comm.dbf has an index on STORENAME and sym.dbf has an index on SYMBOL
(both in .CDX files). Sym.dbf to Comm.dbf is 1 to many relationship.
Can this work?

Yes. And as said before: You don't need to set indexes,
rushmore optimization will use indexes for join and where
conditions.

You may better specify if you want an inner or outer, left or right join.
The default I think is LEFT OUTER.

You may have the same effect, that LIKE 'SPEED%' will work
better.

Why don't you simply give it a try?

Bye, Olaf.

[Don K]

Olaf,

Yes this worked fine. The index situation is simpler on this table, as I
have individual indexes on the interesting fiields.

Looks like I am making progress...

Don

[Cindy Winegarden]

Hi Don,

FoxPro is pretty much like SQL Server in that the data engine automatically
takes advantage of indexes when they are available and match expressions in
the Join or Where clauses of SQL Select, Update, etc. statements.

It's also just like any other OLE DB compliant database in that it takes
most of the ANSI SQL commands. Are you not very familiar with SQL commands?

I guess we Fox developers are spoiled. SQL is an integral part of the Fox
data engine and we write SQL code all the time to compile data for reports,
etc.
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP

Post by Don K
Therefore my main path forwards is probably to stick with passing SQL
queries to vfpoledb provider in C#, even though it is getting more
complicated as I attempt table joins with indexes.

[Don K]

Cindy,

Yes, I see that this quite like SQL Server but there are enough minor
differences to trip me up.

Experience-wise on SQL I am probably about 4 out of 10 (where10 is an
expert) - most of the effort usually goes on sorting out the joins, the
GROUP BY, HAVING and subquery stuff. I guess I am used to all the important
fields being set up with indexes on SQL Server, and not having to think
about indexes at all (especially composites) in the SQL SELECT / UPDATE
statements...

Don

[Cy Welch]

Post by Don K
Cindy,
Yes, I see that this quite like SQL Server but there are enough minor
differences to trip me up.
Experience-wise on SQL I am probably about 4 out of 10 (where10 is an
expert) - most of the effort usually goes on sorting out the joins, the
GROUP BY, HAVING and subquery stuff. I guess I am used to all the important
fields being set up with indexes on SQL Server, and not having to think
about indexes at all (especially composites) in the SQL SELECT / UPDATE
statements...
Don

Actually IMHO a well designed VFP database should have the same things.
Plain indexes on items that are likely to be searched on, and avoiding
compound indexes whenever possible.