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

Question

I have a long-running service with several threads calling the following method hundreds of times per second:

void TheMethod()
{
    using (var c = new SqlConnection("..."))
    {
        c.Open();

        var ret1 = PrepareAndExecuteStatement1(c, args1);
        // some code
        var ret2 = PrepareAndExecuteStatement2(c, args2);
        // more code
    }
}

PrepareAndExecuteStatement is something like this:

void PrepareAndExecuteStatement*(SqlConnection c, args)
{
    var cmd = new SqlCommand("query", c);
    cmd.Parameters.Add("@param", type);
    cmd.Prepare();
    cmd.Parameters["@param"] = args;

    return cmd.execute().read().etc();
}

I want reuse the prepared statements, preparing once per connection and executing them until the connection breaks. I hope this will improve performance.

Can I use the built-in connection pool to achieve this? Ideally every time a new connection is made, all statements should be automatically prepared, and I need to have access to the SqlCommand objects of these statements.

I don't understand what you mean by "until the connection breaks" and what exactly is supposed to happen between calling the different prepared statements. — Oded, Jun 13 '11 at 20:10
If someone restarts the SQL Server the existing connection will break. — lzm, Jun 13 '11 at 20:16
What is the size of the result set? You may want to use an SqlDataReader if it is a smaller result set. Also, connection pooling is enabled by default. When you Open() or Close() a connection, you are simply getting from or returning to the pool. — dotnetster, Jun 13 '11 at 20:26
@dotnetster (responding to your comment on oleksii's deleted answer) That's exactly what I want to do, but it gets a little complicated because the service will run forever, and I need to re-open the connection (and re-create the prepared statements) if the SqlConnection object becomes invalid (by checking the Status property). I suppose I could implement a connection pool myself to manage that, but is there a simpler way? — lzm, Jun 13 '11 at 20:51
You can check for: myConnection.State == Connection.Closed and re-open the connection if the condition is true. I do this in my long running processes. Prepared statements don't need to be re-created as long as the process has not exited. — dotnetster, Jun 13 '11 at 21:22

p.campbell · Accepted Answer · 2011-06-13T20:26:27.380

Suggest taking a slightly modified approach. Close your connection immedately after use. You can certainly re-use your SqlConnection.

The work being done at //some code may take a long time. Are you interacting with other network resources, disk resources, or spending any amount of time with calculations? Could you ever, in the future, need to do so? Perhaps the intervals between executing statement are/could be so long that you'd want to reopen that connection. Regardless, the Connection should be opened late and closed early.

using (var c = new SqlConnection("..."))
{
    c.Open();
    PrepareAndExecuteStatement1(c, args);
    c.Close();
    // some code
    c.Open();
    PrepareAndExecuteStatement2(c, args);
    c.Close();
    // more code
}

Open Late, Close Early as MSDN Magazine by John Papa.

Obviously we've now got a bunch of code duplication here. Consider refactoring your Prepare...() method to perform the opening and closing operations.

Perhaps you'd consider something like this:

using (var c = new SqlConnection("..."))
{
    var cmd1 = PrepareAndCreateCommand(c, args);

    // some code
    var cmd2 = PrepareAndCreateCommand(c, args);

    c.Open();
    cmd1.ExecuteNonQuery();
    cmd2.ExecuteNonQuery();
    c.Close();
    // more code
}

I may not have understood you answer, but I think this doesn't address my problem. With your suggestion I will keep preparing the statement (creating the SqlCommand object, setting the query string, defining the type of each parameter, calling .Prepare(), setting the variable and executing the query. I want to simply set the variables and execute the previously-prepared statements inside TheMethod(). — lzm, Jun 13 '11 at 20:21
@lzm: You're actually doing it correctly or as recommended. Create a new SqlCommand for each. — p.campbell, Jun 13 '11 at 20:23
Aren't prepared statements supposed to be prepared once and executed many times? — lzm, Jun 13 '11 at 20:31

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

1 Answers1

Linked