We have a server with W2003/SQL2000/VFP6/Webconnect. The remote views work fine.
We are building a new, faster server with W2003/SQL2008/VFP9/Webconnect. The remote views are 10 times slower at least.
Previously, we had tried to build a server with W2003/SQL2005/VFP6/Webconnect. The remote views were also 10 times slower.
Looks like the problem might be with the ODBC driver accessing SQL2005/2008 since none of the other programs are suspect.
We are using the SQLServer ODBC driver. Should we used something else, like the SQL Server native or mySQL driver?
Or is it the SQL2000 database that was upgraded? We set the proper compatibility level, and queries seem fast in Management Studio and testing in the dbc container file where the views are.
Or do I need to restart the computer after setting up the ODBC driver?
Any suggestions on how to find out where the problem is? The queries are for simple SQL statements - parametrized views on indexed tables with a simple where clause on the key (single join condition).