FoxPro Index called from SQL Server

**Chuck Urwiler** · Jul 20 '05, 01:40 AM

Re: FoxPro Index called from SQL Server

Hi Steve,

I can give you a better answer if you tell me what's indexed, what's not,
what kind of query you're sending, and how much data you're querying vs.
what you're expecting as a result set.

Regardless of how many records you are expecting in teh result set, the
ODBC/OLEDB provider must load at least the portion of the index to realize
the result set of the query, then it must retreive that data. If the query
isn't optimized, then the provider is retreiving the entire set of records
from the tables in your query and processing them. Depending on how things
are set up, this could be very slow since you're downloading a potentially
huge amount of data to the memory of the SQL Server box.

This is the problem with file server databases and why nowadays, I store all
of my data in SQL Server, with only tiny amounts of data ever in VFP
(typically for small applications that stand alone on my one workstation).
While VFP can handle very large datasets, it's not something you should be
doing (in my opinion, of course).

So, no, you can't tell the query to use the index or not, that's decided by
the optimizer in FoxPro/VFP, not by the query. I would suggest you try the
queries two ways: one with the tables and their indexes, and another time
after dropping the indexes. You may actually see it get faster without the
indexes.

-Chuck Urwiler, MCSD, MCDBA

"Steve Perry" <jsperry@hotmai l.com> wrote in message
news:blcjhm$d4j @dispatch.conce ntric.net...[color=blue]
> Hi,
>
> I have a large FoxPro table with an index that I need to be Queried from[/color]
SQL[color=blue]
> Server by OLE.DB or ODBC. If I query the DBF directly a search takes 1
> Minute +. Is there a way I can call the data from the table and use the
> existing FoxPro Index?
>
> Thanks
> Steve[/color]

FoxPro Index called from SQL Server

FoxPro Index called from SQL Server

Comment