16

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

Community
  • 1
  • 1
Josh
  • 6,944
  • 8
  • 41
  • 64
  • Strange, I had a newline after the first link in the edit box but it didn't make it into the rendered output... Fixed. – Josh Jan 07 '14 at 13:26
  • Put it on a method, pass a collection of SqlParameters to it and reuse the rest. :) – Marciano.Andrade Jan 07 '14 at 13:26
  • Read this http://msdn.microsoft.com/en-us/magazine/ee236412.aspx and be sure to define accurately the size of the parameters. This should help you to improve the performance. (Except storedprocedure of course) – Steve Jan 07 '14 at 13:30
  • Are you executing this in a tight loop? Otherwise, I'd suspect you might be in a scenario where `Prepare()` is ideal. From my understanding, for `Prepare()` to be useful, you have to keep your `SqlCommand` object around, which could lead to problems with disposing it when you are finally done with it. – Sven Grosen Jan 07 '14 at 13:33
  • @Marciano.Andrade: Good idea, another way to do my Nullify. The question is more around how to re-use a prepared statement though. – Josh Jan 07 '14 at 13:37
  • @ledbutter: Not exactly a tight loop, as in a short loop, but there is a procedure that uses the same queries repeatedly many many times. Each iteration takes a few seconds with a number of queries. It's single-threaded (or single-flow), and essentially everything is static so it should be the same object in use throughout. – Josh Jan 07 '14 at 13:40
  • @Josh and you have evidence to show that using `Prepare()` produces appreciably better performance? – Sven Grosen Jan 07 '14 at 13:50
  • @ledbutter: I don't have evidence either way. – Josh Jan 07 '14 at 13:53
  • 1
    @Josh then I'd suggest not worrying about using `Prepare()` and just create the `SqlCommand` each time, unless you can refactor your logic to have it all happen in a single stored procedure call. – Sven Grosen Jan 07 '14 at 13:55

2 Answers2

12

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?

TylerH
  • 20,799
  • 66
  • 75
  • 101
simon at rcl
  • 7,326
  • 1
  • 17
  • 24
  • It is basically a periodic external load from various sources (SQL, LDAP, SOAP, etc.) with processing to transform the incoming data into a new structure. It's more complex than a column map or select/insert, unfortunately. I've seen other answers elsewhere that swear by preparing, and after reading lots of stuff online I'm not convinced most people use prepared statements correctly anyhow (for this purpose). Clearing them out is just a precaution, the question is really if this (resetting params only) is the right way to re-use the command to preserve the compiled statement. – Josh Jan 07 '14 at 13:47
  • 2
    Thanks. You only need to reset the parameters to the new values (I've just tested that and it works). You don't need to set them to null or anything. So yes, keep the connection open, keep the SqlCommands around, and reset the parameter values as required. – simon at rcl Jan 07 '14 at 13:58
  • Thanks for noting to keep the connection open. I ended up making a static DB class and "registering" commands with it so that I only had one connection for the life of the import that reused the commands over and over. Fortunately it's a single-threaded app. I also made it so I had to submit params when executing a registered command, and made sure they were all present so no previous values remained (they do linger between executes I discovered). – Josh Jan 22 '14 at 17:05
6

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.
Community
  • 1
  • 1
StuartLC
  • 104,537
  • 17
  • 209
  • 285