Friday, February 29, 2008

Dynamic SQL/ADO Command Execution Comparison

In a project I'm working, I need to build up filtering conditions within C# code based upon user settings.  There are about 50 settings so I chose to do this in C# code and pass it to SQL server to get the results set.  This is something I would really like to have in a stored procedure, but was hesitant to building up the dynamic SQL, both from a complexity and performance stand point.  Recently while troubleshooting a problem I was working with the SQL Profiler and a SqlClientCommand object executed a statement against SQL Server.  Since this was a parameterized query the following statement was passed to the engine.

exec sp_executesql N'select * from usv_action_item_detail where action_item_type_id = @actionItemTypeId',
     N'@actionItemTypeId uniqueidentifier', @actionItemTypeId = N'25B3384F-04C8-4B6D-BC11-B7EC92A07E45'

This gave me the thought that this is really just a parameterized dynamic sql statement.  This could be built and executed through a stored procedure.  As a trusted colleague suggested, I went on to run some performance tests.
I built a simple C# test harness with the following code
     SqlCommand cmd = new SqlCommand("select * from usv_action_item_detail where action_item_type_id = @actionItemTypeId", new  SqlConnection("server=sldsql1;database=ChaosFilter2007;Integrated Security=true"));
     SqlParameter parm = new SqlParameter("@actionItemTypeId",SqlDbType.UniqueIdentifier);
     parm.Value = new Guid("25B3384F-04C8-4B6D-BC11-B7EC92A07E45");
     cmd.Parameters.Add(parm);
     cmd.Connection.Open();
     cmd.ExecuteReader();
     cmd.Connection.Close();
I also built the statement to run in SQL Server Management Studio.
exec sp_executesql N'select * from usv_action_item_detail where action_item_type_id = @actionItemTypeId',
     N'@actionItemTypeId uniqueidentifier', @actionItemTypeId = N'25B3384F-04C8-4B6D-BC11-B7EC92A07E45'
And started my testing.
As I expected when the C# code was executed the following results where presented in SQL Profiler


Next I executed the dynamic SQL command through SQL Server Management Studio and capture the following result

You will notice the only real difference is that "EventClass" column which for the ADO.NET method it returned "RPC:Completed" and with SSMS it returned SQL:BatchStarting and SQL:BatchCompleted.  I guess this makes sense if you think about it.
I've executed this test about a dozen times and with all things being equal the, CPU, Reads and Duration where roughly the same in all cases.
My conclusion is that the same stored procedure gets executed either through the Parameterized Query through C# or through the Parameterized Dynamic SQL through SSMS.  What this tells me that if done properly there is no performance penalty for using a parametrized dynamic SQL statement in a procedure.  I can push the logic for building up my filtering statement from C# into a stored procedure.
-ec

No comments:

Post a Comment