Speed for FoxPro / VB

I've written a windows program in vb.net that employs ADO/ODBC via a DSN-less connection to a FoxPro database. The biggest table has over 60,000 records. It works well with one user, but was built so a second person could work concurrently over a network.

The problem is, it works at a crawl for the remote user. A query which returns only a couple of dozen records can take almost 8 seconds to run. I've read that RDO would be faster, but it seems rather strange to port it backwards (plus, I can't get RDO to work in .net, a whole separate question). Also Microsoft has released an OLEDB connector for the latest version of FoxPro, which works faster than ODBC, but it's still not ideal.

Is there any way to get this thing up to an acceptable speed? Seems like vb.net may not even be the right tool for the job. I'm new to this, and am open to any advice on which direction I should proceed.

Thanks in advance.

I am not familiar with FoxPro, but there is a distinct possiblity that the work is actually being done locally, meaning the entire table is hoovered across the network and then processed. I have heard that Access does the same thing. I believe that FoxPro (like Access) is not run as a server bound to a specific machine but is launched each time the database is accessed. You can look into using something free like Postgresql and cygwin (or that costs $$ like Oracle or SQLServer) and probably get the performance you are interested in.

The Visual Foxpro OLE DB provider/ODBC driver is not client-server. It is the Visual Foxpro database engine that run locally and exposes OLE DB/ODBC interface to clients. So, if you have queries, the OLE DB provider will run locally the foxpro engine, open the database/tables from shared folder, and then retrieve the resulting record set.
Anyway, the Foxpro database engine can handle queries against milions of records in sub-second execution time even with shared network acces if:
-the resulting record set is small (the same apply for other servers)
-you have the database designed for fast queries ( queries that can use Rushmore technology)

Rushmore technology speeds up data acces thousands of times, using existing indexes, so I recommend you to create indexes using fields/expressions involved in SQL query.
If you post the code for queries, maybe I can help you.

Thanks for the responses... that sounds very promising, badukist.

One of the main SQL queries follows. strFilter is the last name, which the user has typed in, and all the fields used here have already been indexed:

"SELECT * FROM hsin WHERE upper(lname) LIKE '" & UCase(strFilter) & "%' ORDER BY lname, rname, suffix"

Looks like you need an index on upper(lname)
Let me elaborate a bit:
If you have an index on upper(lname)...
The expression

upper(lname) LIKE 'JO%'
is optimizable, partially but very fast (portions of index will be downloaded locally and a list of records will be built based on filtered index keys, then only matching records will be downloaded from source table)

upper(lname) like '%JO%' is not optimizable and all data from table will be downloaded to find matching records.

upper(lname) = 'JO' is fully optimizable (the fastest) and can be used to retrieve the same result as LIKE 'JO%' if SET ANSI OFF.
If SET ANSI is ON, 'JO' will be padded with spaces to find matching records.

If you don't have index on upper(lname) then all data from table will be transferred to find matching records.

Hope this help