Is SQLDataReader slower than using the command line utility sqlcmd?
Posted
by Andrew
on Stack Overflow
See other posts from Stack Overflow
or by Andrew
Published on 2010-05-14T00:28:08Z
Indexed on
2010/05/14
0:34 UTC
Read the original article
Hit count: 447
sqldatareader
|sqlcmd
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
© Stack Overflow or respective owner