Is it a good idea to re-use ADO.NET command objects?

Question

I'm working on a .NET program that executes arbitrary scripts against a database.

When a colleage started writing the database access code, he simply exposed one command object to the rest of the application which is re-used (setting CommandText/Type, calling ExecuteNonQuery() etc.) for each statement.

I imagine this is a big performance hit for repeated, identical statements, because they are parsed anew each time.

What I'm wondering about, though, is: will this also degrade execution speed if each statement is different from the previous one (not only different parameters, but an entirely different statement)? I couldn't easily find an answer on that in the documentation.

Btw, the RDBMS used is Oracle, but I guess this question is not really database specific.

P.S. I know exposing the same Command object is not thread safe, but that's not an issue here.

Answer 1

The performance hit usually comes from establishing a connection to the database, but ADO.NET creates a connection pool to help here.

If you wish to avoid parsing statements each time anew, you can put them into stored procedures.

I imagine your colleague just uses some old style approach that he's inherited from working on other platforms where reusing a command object did make a difference.

Answer 2

There is some overhead involved in creating new command objects, and so in certain circumstances it can make sense to re-use the same command. But as the general case enforced for an entire application it seems more than a little odd.