remote
     synthesis

When to Use SQL Stored Procedures?

Posted on Jun 17, 2005

This seems to keep coming up in my work and discussions and I wanted to lay out my opinion on the topic and let others chime in as well. While I am a firm believer in the benefits of stored procedures, there seems to be a cadre of people who feel that all queries can benefit from being encapsulated in a stored procedure...that somehow performance is always better when using a stored procedure...that stored procedures are rock and inline queries are scissors. This, IMHO, is simply not true.What I have come across in my recent experience (and from discussions seems more common than I initially suspected) is long and complex conditional processing building a dynamic query string that is then evaluated. The first problem is that (at least as I understand it), queries built in this manner are compiled upon every call of a stored procedure (unlike standard stored procedure queries), thereby negating the main performance gains that stored procedures offer.

I have found that when used for handling queries built with complex conditional processing, ColdFusion can handle this better and faster than a SQL stored procedure. Recently, I converted one such stored procedure to an ad hoc query in CF and cut the processing down from 300+ ms for just the query to 30ms for the entire page. Nothing else had changed other than converting a stored procedure call to an inline query (well written of course).

Which leads me to my second point, moving something into a stored procedure has a negative benefit if the stored procedure is not well written. This seems to happen often, and my theory is that it is because SQL is not an application server and the Structured Query Language is just what it's name says, a query language (meaning that it is not always the ideal place for building your business logic...I might even say rarely with well thought out exceptions).

Clearly, my perception is not universally shared..and I am open to intelligent responses telling me why I am full of it.

Comments

You make some good points... The reason I tend to shy away from stored procedures is that I prefer to write my applications as database independent as possible. So I can really reuse them anywhere.

Also I think a lot of the performance benefits from stored procedures can be gained by using prepared statements (ie cfqueryparam), so I almost never consider them. I only use them as a last resort for performance tuning.

Posted By Pete Freitag / Posted on 06/17/2005 at 10:45 AM

You are absolutely right about executing a runtime sql statement (like using sp_executesql) inside of a query. It negates any positive gain you might have gotten from the compile process. good stuff.

Posted By mark kruger / Posted on 06/17/2005 at 11:11 AM

Hmmmm... I too have seen massive and ugly dynamic SQL SPs. Definitely the wrong way to go.

However, I have also seen dynamic inline queries that could have been turned into a _set_ of SPs. When people make dynamic queries they are usually doing so because they want to be able to change the tables and/or fields being referenced on demand. Since the tables and their columns are fixed, there are a set number of permutations available, each of which could be turned into its own SP and called based on conditional processing. Best of both worlds.

Inline queries which dynamically substitute tables and such can be vulnerable to SQL injection attacks as well.

Posted By Chip temm / Posted on 06/17/2005 at 7:44 PM