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.
