Is SQLDataReader slower than using the command line utility sqlcmd?
- by Andrew
I was recently advocating to a colleague that we replace some C# code that uses the sqlcmd command line utility with a SqlDataReader. The old code uses:
System.Diagnostics.ProcessStartInfo procStartInfo =
new System.Diagnostics.ProcessStartInfo("cmd", "/c " + sqlCmd); wher sqlCmd is something like
"sqlcmd -S " + serverName + " -y 0 -h-1 -Q " + "\"" + "USE [" + database + "]" + ";+ txtQuery.Text +"\"";\
The results are then parsed using regular expressions. I argued that using a SQLDataReader woud be more in line with industry practices, easier to debug and maintain and probably faster. However, the SQLDataReader approach is at least the same speed and quite possibly slower. I believe I'm doing everything correctly with SQLDataReader. The code is:
using (SqlConnection connection =
new SqlConnection())
{
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);
connection.ConnectionString = builder.ToString(); ;
SqlCommand command =
new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// do stuff w/ reader
reader.Close();
}
catch (Exception ex)
{
outputMessage += (ex.Message);
}
}
I've used System.Diagnostics.Stopwatch to time both approaches and the command line utility (called from C# code) does seem faster (20-40%?). The SqlDataReader has the neat feature that when the same code is called again, it's lightening fast, but for this application we don't anticipate that.
I have already done some research on this problem. I note that the command line utility sqlcmd uses OLE DB technology to hit the database. Is that faster than ADO.NET? I'm really suprised, especially since the command line utility approach involves starting up a process. I really thought it would be slower.
Any thoughts?
Thanks,
Dave
