Stored Procedures vs. Prepared Statements

At work the last few weeks, I have been doing a lot with SQL in a web application. I will not say I am an expert in the matter, but I do feel I have a pretty good understanding of the two options. Ultimately, a stored procedure seems to be rather annoying than useful. The problem I have is that the languages RDBMS provide for stored procedures tend to be pretty bad as far as programming languages go. So there is a reason you have chosen to write your application in a particular language. Chances are it is the best for the situation and provides a lot more power than the meager implementation offered by the RDBMS. That means you need to get some advantage out of using stored procedures to make it worth your while.

So what advanatages can stored procedures offer? Protection from sql injection is thrown out because you get the same benefits from prepared statements. I have heard some arguments about performance gains. The two primary factors coming from caching the SQL statements along with the round-trip-time to the database server. From what I have heard, most RDBMS these days cache prepared statements just as well as stored procedures. Besides, on today's machines, you have to write the world's most complex SQL statement before you will notice the hit on the server. Meanwhile, the round-trip-time (RTT) can be a valid argument. If you have a lengthy series of statements with some simple logic to control them, there is some potential benefit to using stored procedures. This would depend on how minute your timing issues are along with how many calls of this nature you would make along with the length of a RTT to the database server. In most environments, the time from the application server to the database server is only a few milliseconds.

Ultimately, your average web application does not have strict timing requirements. It tends to be more dependent on the speed of development along with the ease of maintenance. it never makes a large enough difference to worry about it. When I am developing applications, I intend to lean towards prepared statements and will only shift to stored procedures when I absolutely need to.