IDbCommand - CommandType.StoredProcedure

I'm just curious if the IDbCommand.CommandType's value makes a difference in terms of performance?

Ba开发者_如何转开发sically, I could do either set it as CommandType.Text and pass in a SQL query "EXEC sp_my_stored_procedure ..." as the CommandText. Or, I could set it as CommandType.StoredProcedure and pass in a stored procedure name "my_stored_procedure" as the CommandText.

I'm wondering if there is any performance difference here, or is it just a matter of passing in a query calling the SP vs passing in the name of the SP?

A side note, I realize that it could depend on the driver, but am not sure. So, if that's the case, I'd like to know that as well. Thanks!

Using the CommandType.StoredProcedure is more efficient. The impact would be felt dependent on your app's load.

In my opinion it is also cleaner. I would generally use CommandType.Text if there was a need to build the command at runtime (i.e. SELECT id, name FROM Table WHERE.......).

Hope this helps, Kevin

I reflected into the Informix object: IfxCommand which has an internal property named AdjustedCommandText. It seems that this method formats the string value to send to the driver based upon the CommandType.

What's interesting is that it either returns one thing for TableDirect or another for StoredProcedure. Or, just the stored text. Now, the StoredProcedure one goes deeper. If there are no command paramters it just returns "EXECUTE PROCEDURE ...", but if there are command parameters involved then it builds the string up starting with "{?=CALL ..." or {CALL ...".

So, I can say as far as Informix is concerned there is no difference except for clarity and cleanliness. As far as other database drivers, I don't haven't looked into them yet.