I'm going to say that you have something wrong in your test setup for the .NET code. There's no way that the .NET should be this slow. Are you running in debug mode? With the developer tools active in Visual Studio? That'll dramatically affect performance. If you do this sort of the thing make sure you run the application on its own without any tooling attached preferrably from the command line - or a Unit in Release mode. Also make sure you test only the data access code and not the environment startup time which probably has a large impact on over time.

For bonus points do your benchmarking in benchmark.net which is a tool that helps make sure you write benchmarks properly to test the right thing.

Benchmark.net


ADO.NET is as close as you can get to the metal in .NET and in general this is slightly faster than VFP using SQL Passthrough - but overall the perf should be bound by the connection throughput and actual SQL processing for simple commands like this. The difference should be relatively minor.

+++ Rick ---

>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