I have a running system using VFP 8 SP1 as client-code and SQL Server 2000 as back-end. The main table I use for queries has 9,000,000+ records. Queries are very fast.
For reasons of scalability and near future growth we are upgrading to new hardware.
I installed SQL Server in new box's. SQL Analyzer runs a query very quickly as it should be. The index optimizer indicates everything is OK. I tried simple queries.
Tried from VFP8.0 SP1 (Windows 2000) to SQL Server - (local) connection in same box. The connection with ODBC - SQLStringConnect() gets a handle quickly. Yet the same query through SPT (SQLExecute) takes forever and a day (actually almost a minute). Returns the correct result but extremely slowly.
The same exact setup in the currently overworked box runs very fast. The replacement box should be just as fast if not more. There is no difference in setup or software or OS versions. BothSQL installations are local to each box. Connection is Trusted in both cases. Yet the new one looks like it might be retrying or waiting for a timeout that never comes, then finally after a minute or so it gets the result.
I don't see anything wrong in the setup or optimization. All queries I tried in the new box are slow.
Note: both boxes are used as web servers using VFP+Web Connect with a (local) SQL Server (e.g. no LAN access to another box).
Any ideas what it might be looking for that it doesn't find and has to retry or else what the slowdown could be from?
TIA