Query Performance

Question

I just received the following question from a colleague: ___________________________________________________________ Student needs to retrieve a single record from a database (actually only a few fields). Is it faster to: a. Use datareader b. Use stored procedure with output parameters processing about 1/2 million records so he feels it's important. Besides devising a test do any of you know or know how to find out easily? Thanks. ______________________________________________________________ My first instinct is that the SP would be a bit faster – since there is less processing of the actual SQL statement. I don’t use output parameters very often at all but if the procedure returns more than 1 value won’t you need to use a DataReader anyways to process the results? So does this question just become one of: Is it faster to use a Stored Procedure over a direct SQL query?

Andrzej Wegierski · Answer

Try to test it.

ytram5 · Answer

The only performance issue is the initial compilation of an inline SQL statement. Sprocs are pre-compiled so you don't have to worry about that. I don't know of a way to handle multiple rows from a SqlCommand without using a DataReader or a Dataset. However the performance variance is very negligible. I prefer sprocs myself but it is primarily due to having a central repository for all of my SQL statements rather than the performance issue. If you decide on inline SQL ALWAYS parameterize it though :)