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