3

I'm working on upgrading an application written by a former developer at my work and I've been converting all the SQL queries in the code into Stored Procedures. I'm doing this with the idea that it can be easily called whenever it is needed instead of having the SQL query string repeated several times in the code. Also, I can easily use parameters with the stored procedure and avoid SQL injection vulnerabilities in my code.

I was talking with another developer on my team who asked why I did it that way and told me that "calling stored procedures a lot can impact the SQL Server's performance and that it is better performance-wise to just use the SQL string in the application's code." (Please note that this is a small VB.NET WinForms development team that doesn't use MVVM or other similar design patterns)

Is that an accurate statement? I've not found anything in my googling prior to posting this question that would indicate running stored procedures has a bigger impact to performance than a regular SQL query.

  • 1
    Won't the SQL Server still have to execute the SQL statement if you send it from the application code? – Robert Harvey Nov 2 '15 at 18:13
  • That was my thought too @RobertHarvey. I was coming here after not finding much on google to see if I was crazy. – Lews Therin Nov 2 '15 at 18:44
  • 1
    I would ask the developer to show a test case where the inline sql was faster. The performance should be roughly the same as both an inline statement and stored procedure will be compiled and cached by the database. But maybe with a special case (as indicated by @TMN) there may be a difference. – Jon Raynor Nov 2 '15 at 20:46
  • Since you have both versions of the code (with and without sp), isn't this something that can be tested and compared? – JeffO Nov 2 '15 at 22:43
1

Way back when, the ODBC driver used to be a bottleneck, because it was very thorough about type-checking all arguments. If you invoked a stored procedure with more than a couple parameters, it would be noticeably slower than sending in a string with all the parameters embedded in it. That said, I would hope that the drivers available today would work better, so you should see an advantage to using stored procedures if your operations are more than a dozen or so lines of SQL. Regardless, you definitely shouldn't see a performance hit.

  • Interesting, this may have been what she was referring to. Thanks. – Lews Therin Nov 2 '15 at 18:34
1

I have not heard of that as a problem.

However stored procedures can be a bit more of a headache when it comes to making changes, or having changes obvious, and none of your reasons have to lead to using stored procedures.

You can make methods with the SQL strings in and call them wherever required in the code to remove duplication of queries. Also there is nothing stopping you using parameters on sql held in strings in the code.

For example.

var sql = "SELECT empSalary from employee where salary = @salary";
var connection = new SqlConnection(/* connection info */);
var command = new SqlCommand(sql, connection);

command.Parameters.AddWithValue("salary", txtSalary.Text);
  • One thing I didn't mention is that some of these queries are extremely long and complex. And in the code it is super messy with a bunch of sqlString += lines to make one mammoth query. Converting one of these into a stored procedure saved over a hundred lines of code. My thought is that having the queries as stored procedures allows for consistency and keeps the application logic separate from the database logic. Am I way off-base with this line of thought? – Lews Therin Nov 2 '15 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.