How to correctly and efficiently reuse a prepared statement in C# .NET (SQL Server)?

Question

I looked at lots of questions but evidently my SO-fu isn't up to the task, so here I am. I am trying to efficiently use prepared statements, and I don't just mean parameterizing a single statement, but compiling one for reuse many times. My question lies around the parameters and reuse and how to implement that correctly.

Generally I follow this procedure (contrived example):

SqlConnection db = new SqlConnection(...);
SqlCommand s = new SqlCommand("select * from foo where a=@a", db);
s.Parameters.Add("@a", SqlDbType.VarChar, 8);
s.Prepare();
...
s.Parameters["@a"] = "bozo";
s.Execute();

Super, that works. However, I don't want to do all of these steps (or the latter four) every time I run this query. That seems like it's counteracting the whole idea of prepared statements. In my mind I should only have to change the parameters and re-execute, but the question is how to do that?

I tried s.Parameters.Clear(), but this actually removes the parameters themselves, not just the values, so I would essentially need to re-Add the parameters and re-Prepare again, which would seem to break the whole point as well. No thanks.

At this point I am left with iterating through s.Parameters and setting them all to null or some other value. Is this correct? Unfortunately in my current project I have queries with ~15 parameters which need to be executed ~10,000 times per run. I can shunt this iteration off into a method but was wondering if there is a better way to do this (without stored procs).

My current workaround is an extension method, SqlParameterCollection.Nullify, that sets all the parameters to null, which is fine for my case. I just run this after an execute.

---

I found some virtually identical but (IMHO) unanswered questions:

Prepared statements and the built-in connection pool in .NET

SQLite/C# Connection Pooling and Prepared Statement Confusion (Serge was so close to answering!)

The best answer I could find is (1) common sense above and (2) this page:

http://msdn.microsoft.com/en-us/magazine/cc163799.aspx

Answer 1

When re-using a prepared SqlCommand, surely all you need to do is set the parameter values to the new ones? You don't need to clear them out after use.

For myself, I haven't seen a DBMS produced in the last 10 years which got any noticeable benefit from preparing a statement (I suppose if the DB Server was at the limits of its CPU it might, but this is not typical). Are you sure that Preparing is necessary?

Running the same command "~10,000 times per run" smells a bit to me, unless you're uploading from an external source. In that case, Bulk Loading might help? What is each run doing?

Cheers -

Answer 2

To add to Simon's answer, prior to Sql 2005 Command.Prepare() would have improved query plan caching of ad-hoc queries (SPROCs would generally be compiled). However, in more recent Sql Versions, provided that your query is parameterized, ad-hoc queries which are also parameterized can also be cached, reducing the need for Prepare().

Here is an example of retaining a SqlParameters collection changing just the value of those parameters values which vary, to prevent repeated creation of the Parameters (i.e. saving parameter object creation and collection):

using (var sqlConnection = new SqlConnection("connstring"))
 {
    sqlConnection.Open();
    using (var sqlCommand = new SqlCommand
       {
          Connection = sqlConnection,
          CommandText = "dbo.MyProc",
          CommandType = CommandType.StoredProcedure,
       })
    {
       // Once-off setup per connection
       // This parameter doesn't vary so is set just once
       sqlCommand.Parameters.Add("ConstantParam0", SqlDbType.Int).Value = 1234;
       // These parameters are defined once but set multiple times
       sqlCommand.Parameters.Add(new SqlParameter("VarParam1", SqlDbType.VarChar));
       sqlCommand.Parameters.Add(new SqlParameter("VarParam2", SqlDbType.DateTime));

       // Tight loop - performance critical
       foreach(var item in itemsToExec)
       {
         // No need to set ConstantParam0
         // Reuses variable parameters, by just mutating values
         sqlParameters["VarParam1"].Value = item.Param1Value; // Or sqlParameters[1].Value
         sqlParameters["VarParam2"].Value = item.Param2Date; // Or sqlParameters[2].Value
         sqlCommand.ExecuteNonQuery();
       }
    }
}

Notes:

• If you are inserting a large number of rows, and concurrency with other inhabitants of the database is important, and if an ACID transaction boundary is not important, you might consider batching and committing updates such that fewer than 5000 row locks are held on a table at a time, to guard against table lock escalation.
• Depending on what work your proc is actually doing, there may be an opportunity to parallelize the loop, e.g. with TPL. Obviously connection and commands are not thread safe each Task will require its own connection and Reusable Command - the localInit overload of Parallel.ForEach is ideal for this.