IDbCommand - CommandType.StoredProcedure

Question

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!

Personally I would expect any difference here to be almost negligible. Feel free to test it, though. — Marc Gravell, Jul 27 '11 at 15:48
I looked at the Informix driver and the CommandType is just used to return a string at the end of the day... — myermian, Jul 27 '11 at 19:39

score 2 · Answer 1 · answered Jul 27 '11 at 15:55

2

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

answered Jul 27 '11 at 15:55

KevinManx

519
1
5
26

I'm curious as to what makes it more efficient and is it particular to one database driver or another? I only ask because after inspecting IBM Informix's .NET driver the `IfxCommand` object has an internal property called `AdjustedCommandText` which returns the command text or a formatted commandtext if the command is a Stored Procedure. I think I pretty much found out the answer to my own question (as far as Informix goes), but I thought maybe you could update your answer as to which DB driver you found to be more efficient to set the CommandType to StoredProcedure vs Text? – myermian Jul 27 '11 at 19:31
The efficiency is on the database side. Stored procedures are generally pre-compiled on the server, so the server doesn't need to compile the procedure after the first time. When the code is sent as text it is compiled every time. Admittedly not a huge difference on a 1-to-1 basis, only as it scales up. – KevinManx Sep 26 '13 at 13:20

score 1 · Accepted Answer · answered Jul 27 '11 at 19:38

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.

IDbCommand - CommandType.StoredProcedure

2 Answers2