Does VFPOLEDB use IDX files for optimization?

[sblocher]

Queries executed from SQL Server against Foxpro free tables via the VFPOLEDB Provider do not seem to recognize IDX files.

I have a linked server setup in SQL Server 2005 to a set of Foxpro free tables using the VFPOLEDB provider (v.9). If I execute a query against a table that has a CDX index, and my WHERE includes the field(s) in one of the CDX keys, then I can see that the CDX is being used based on response time. Query is fast.

But if i execute a query against the same table, which also contains several IDX files, and use an expression in the WHERE clause that SHOULD match the expression in one of the IDX files, the query is slow, and is obviously not using the IDX file.

From the research i've done, it seems that VFPOLEDB will use Rushmore, and that Rushmore will use an IDX if it can. But my tests do not concur.

I do NOT have control over the Foxpro data; I CANNOT add an index or modify one or anything. I can only read the data.

Sample query run from SQL Server Management Studio:
select * from openquery(FOXDATA,'select ssn, lastname from employee where ssn="123-456-7890"')

 

[GriffMG]

I don't think the VFPOEDB can possibly make use - or even update - indexed in the idx format, because it can't possibly know they exist.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[GriffMG]

Sorry, that answer was a bit glib and superficial!

VFP (and the OLEDB driver) 'knows' when there is a structural index (CDX) associated with a table - because it sets a flag in the table header and the structural index then has the same name as the table but gets a file extension of .CDX

With this, regardless of whether the table is in a database container (.DBC) or free, VFP, and the OLEDB driver, will open the table AND maintain the index (keep it up to date with new and amended records).

Any table which uses the older .IDX scheme, of non-structural indexes, can access the indexes in VFP by opening them specifically by name - but the OLEDB driver cannot do this, as it doesn't know what they are called and doesn't bother to guess.

So, move you free tables to .CDX indexes and VFPOLEDB might use them for optimisation!

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[sblocher]

That makes sense. If the table header has no knowledge of any IDX files that are built against it, then it certainly can't use it.

Is there any other way to tell VFPOLEDB about an IDX file, such as thru a config file?

Thanks

 

[GriffMG]

I honestly don't know... I've never tried, when I found the compound index scheme (moving from Clipper's .ndx arrangement) I was so pleased that I embraced the structural approach and haven't looked back!


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

The way to make foxpro aware of the additional index is to set it SET INDEX TO ...idx makes it an active index and rushmore then does find and use it.

Plus: only if you SET INDEX TO ...idx the idx file is updated with changed or new data, otherwise updating, deleting from and insertin into the table does not update the idx file and it get's out of sync with the dbf, that's the downside of usind an IDX, plus it's not compressed.

Bye, Olaf.

 

[GriffMG]

Olaf,

There is no 'SET INDEX TO' in the OLEDB interface is there?


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.