Sunday, March 16, 2008

Stored procedures, faster or not? (SQL Server)

I'm not really that knowledgeable about databases. I can do a select or two and I understand joins, but anything more complex that then that and I won't like it. I don't really like SQL. The language seems very illogical to me for some reason.

We do have a lot of discussion at work about databases. A colleague of mine really loves delving into database problems and he even made a list of SQL standard practices. One of these standard practices says that we should always use stored procedures instead of dynamical SQL statements.

I always want to know the 'why'. Why are we supposed to use only stored procedures? And the reasons invariably are:

  1. Stored procedures are not susceptible to SQL injection.
  2. Stored procedures are safer because you can secure them on the database level.
  3. Stored procedures enable us to update database logic or fix a bug without having to release a new version of the software.
  4. Stored procedures are faster then dynamic SQL.

Almost two years ago and ran into a weblog post that debunked all of these points: Stored procedures are bad, m'kay?.

Given this article, there is still a lot of discussion at the office and people generally still say stored procedures are faster and safer. So yeah, stored procedures are faster then SQL created by adding SQL statements and their parameters together with string concatenations, but according to the article they're not faster then properly parameterized SQL queries. And as for security at the stored procedure level, we don't do that in our office, so that shouldn't be a concern. Also, when changing a stored procedure, we still need to create a new release of the software, even though only a SQL script changed. That's just the way we work. So the 'software release' argument is also nonsense.

I will agree that stored procedures are faster in that you only send the name of the query and the parameters, while with dynamic SQL you'd need to send the entire query with the parameters. So it's faster in that you save a few bytes sent over the wire. And for some really rare cases those few bytes can make a difference, but not in the applications we make.

In the end it's a discussion about what 'feels' better I think. One of the many religious wars fought in the field of software development.

No comments: