I'm just curious if the IDbCommand.CommandType's value makes a difference in terms of performance?
Basically, 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!
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.