Hi Mark,

It appears that you are using a remote view here. In a word, remote views suck. If you want the best performance, create a stored procedure and call the stored procedure via SPT if you need a VFP cursor. Or, you can use an ADO Command Object to fire the stored proc to create an ADO Recordset.

Regarding the 12,000 rows, do you really need to download that much info over the wire? It seems like a lot of data? Regardless of that, what are the configuration statistics on the SQL box? Granted, you may only have NT and SQL on the machine. However, NT itself needs at least 24 mb for itself to operate correctly. Unless you are running at least 64mb of RAM, I would say you have a lack of horsepower here. Also, how much free disk-space do you have?

As Mike has already indicated, there can be many factors. Regarding the move to SQL, speed and performance usually rank at or near the bottom of reasons why to make such a move. For me, the top reasons are:

1. Size of Data - being over the 2gb Fox limit
2. Need for security
3. Need for transaction logging
4. Need for replication abilities

Just my 2-cents...



>Hello!
>I have a table setup in a SQL Server 7.0 database with 1.5 million records. Doing a very simple query, on an indexed field, in the Query Analyzer on the server machine itself, seems very slow. -Considerably slower than running the same query on a VFP table on the same machine. I'm talking about 27 seconds for a return of 12,000 records from a query like:
>
>SELECT MyField FROM MyDBC.dbo.MyTable WHERE MyField = 'MyValue'
>
>There is an index on MyField.
>The statistics report usually shows about 23 logical reads.
>
>There has to be something I'm doing wrong. Any suggestions would be very appreciated!
>Mark