DbConnection create command performance implications

Question

I have some legacy code using workflow foundation that has some performance issues , I'm seeing this pattern repeated . does the db.CreateCommand() have any preformace hit , is there any other way to create a DbCommand object ?

DbCommand CreateCommand(DbConnection db)
{
  using (var cmd = db.CreateCommand())
  {
     ... 
     return cmd;
  }
}

Interesting method, by the, way, it returns a command but first disposes it. What the client is supposed to do with the returned value then? — Wiktor Zychla, Jul 14 '16 at 22:42
What @WiktorZychla is referring to is the fact that your "using" code disposes the object before it actually returns it. I'm guessing this is just psuedocode because if it's real code, it would be problematic — Erik Funkenbusch, Jul 14 '16 at 23:29

score 1 · Answer 1 · edited Nov 26 '19 at 17:40

Would guess that the performance hit you would take would be very small for something like this. Know that create new connections over and over can be very costly over re-using the same connection but don't think creating commands over and over would have the same effect as the cost with opening and closing connections has to do with the initial authorization and establishing the connection.

You can try using one connection and reusing commands as explained by the accepted answer here: Reusing SqlCommand?

Basically shows a more detailed example of something like the following

using(var connection = new SqlConnection(connectionString))
{
    connection.Open();
    using(var command = new connection.CreateCommand())
    {
        command.CommandText = "--Some Awesome Sql Here";
        command.CommandType = CommandType.Text;
        command.Parameters.AddWithValue("@awesomeVariable",1337);

        /* Do Stuff until you need to make a new query / request */

        command.CommandText = "StoredProcedureGuy";
        //Get rid of old parameters
        command.Parameters.Clear();

        /* Rinse repeat */
    }
}

Connections are pooled, and typically do not get opened and re-opened in most cases. So there is seldom a performance penalty for closing and opening connections to the same source. — Erik Funkenbusch, Jul 14 '16 at 23:27
@ErikFunkenbusch meant opening new connections, the same way new DbCommands were being created and disposed in this example, will clarify — konkked, Jul 18 '16 at 15:31

score 1 · Accepted Answer · answered Jul 18 '16 at 15:43

is there any other way to create a DbCommand object ?

Not in a provider-agnostic way. Since you are using the abstract DbConnection1 class instead of a specific provider, you need to use the factory method to ensure that the right connection type is used.

Note that in general connections are pooled by .NET, so creating then generally isn't an expensive process. If you want to know in you actual situation if you have a performance problem then try it both ways and measure the difference, otherwise you (and we) are just speculating.

I would also reiterate what is mentioned in the comments, that you are returning a disposed object. It's not clear what you do with the command other than just creating it, but returning a disposed object is probably going to result in the client trying to use an object in a bad state and getting errors. Best to let the client dispose of it rather than this method.

DbConnection create command performance implications

2 Answers2