Friday, March 30, 2012

Runtime Analysis

I just ran a testbed of 4 types of SQL queries:

1. inline SQL with a StringBuilder
2. managed sql
3. SQL text processing (@.SQL as varchar(5000); SET @.SQL = 'SELECT ' + @.var...)
4. a regular sproc that has the columns and table name hard coded

1,2, and 4 always end up at about the same time given the averages.
3 is always at last 1.5 times slower, and usually closed to 2 times.

1 and 2 both use StringBuilders, the code is a direct copy, and 3 is a copy as well.

My managed SQL is:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_Items_Select_Managed(SqlString table, SqlString name,
SqlString value)
{
// sql
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat(
"SELECT {0}.* FROM {0} WHERE {0}.{1} = {2}",
table,
col,
value
);

SqlConnection sqlConnection = new SqlConnection("context connection=true");

SqlCommand sqlCommand = new SqlCommand(stringBuilder.ToString(), sqlConnection);

sqlConnection.Open();
SqlContext.Pipe.Send(sqlCommand.ExecuteReader());
sqlConnection.Close();
}

Is there anything wrong with my Managed SQL, or is this just the way that it is?

ThanksI realize the second parameter to the method is a typo, it should be col. I changed the naming convention for legibility.

No comments:

Post a Comment