We are converting an existing VPF application to .Net
One section of the VFP application executes 10s of thousands of queries against a SQL Server database and performance is critical.

As part of conversion planning and risk identification process we ran a small test to compare just the time taken to do the round trip to the database and back under VFP versus under ADO.Net


The VFP code was as follows:

LOCAL lcQuery
lnC = SQLCONNECT("MyODBCConnection","MyUserid","MyPassword")
=SQLEXEC(lnC, "USE [MyDatabase]")
FOR lnCount = 1 TO 10000
	lcQuery = "SELECT @@ServerName"
	 SQLEXEC(lnC, lcQuery)
ENDFOR

The .Net code was as follows:

{
    System.Data.SqlClient.SqlConnection loConn = new System.Data.SqlClient.SqlConnection() { ConnectionString = @"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=MyDatabase" };
    loConn.Open();
    System.Data.SqlClient.SqlCommand loCmd = loConn.CreateCommand();
    loCmd.CommandType = CommandType.Text;
    for (Int32 lnCount = 1; lnCount <= 10000; lnCount++)
    {
        loCmd.CommandText = "SELECT @@ServerName";
        loCmd.ExecuteScalar();
    }
    loConn.Close();
}

The time taken for the respective code blocks to execute were, unexpectedly, as follows:
VFP Code 1 seconds
.Net Code 15 seconds


When the above results are scaled from 10000 queries to 50000+ queries and higher the performance hit becomes un acceptable.

1) Is there a way speeding up the ADO connection performance ? - changing the Packet Size on the connection made no difference.
2) Is there perhaps another of way achieving the same result other than using ADO.Net ?

TIA