I finally had some time to address some of the points in Frans post.
> Ad-hoc SQL Script is brittle
‘For Ad-hoc queries, you change the query’
Yes, and you’ve also got to recompile the application don't forget to mention that
‘A component which generates the SQL on the fly at runtime’
Well this doesn’t really solve the problem does it – it just pushed problem to another layer. Now instead of updating my stored procedures I need to count on returning or executing additional code to dynamically generate the SQL. I’ve also now got more code on the stack that I have to call through to get my data vs. updating a sproc.
‘With a stored procedure this wouldn't be possible.’
Why not? For sprocs I can control what fields are returned and I can also control new inputs by defining new parameters that can set default values.
> Security
‘Roles’
Frans is making very good points here and we are in agreement. Roles can be used to restrict users to certain tasks, inclusive of SELECT, INSERT, etc. permissions on the database. I tend to think in my limited view of web based applications. In many cases for web based applications you may only need a single user. But, yes, it would be better to define and apply a role for that user – incidentally something we do for managability on www.asp.net.
‘in most applications you have 2 roles’
Ever heard of role based security? Frans hasn’t (touché -- sorry couldn't resist :-) ). I think most applications tend to have more than 2 roles, I know mine do :)
‘SQL injection attacks due to the parameters’
Totally agree, you can use parameterized ad-hoc SQL to solve this problem. Never said you couldn’t in fact what I said was that stored procedures provide an effective counter-measure, not the only counter measure.
> Performance
‘Compiled’
So, I actually did some fact checking and talked to some people on the SQL Server team. Frans' you may be interpreting the documentation incorrectly (or the documentation is wrong, I haven't had a chance to dig through it yet). From what I've been told - again coming from the team that builds SQL Server - is that stored procedures are compiled if the plan is not in the cache. You negelet to mention is that subsequent uses of the stored procedure will use that plan and not compile. My data comes from someone who helped design several stored procedure for a large trading company (called millions of times/day) and the stored procedure was able to stay in the cache and was never re-compiled, quote: 'performance that could never be duplicated with dynamic SQL'.
I was also enlighted that the view of the 'data' world affects the design. In a data warehouse/data mart view of the world I was told that dynamic SQL is probably the way to go where a bunch of unrelated sql statements need to be used. In OLTP systems or large data warehouse systems dyanmic SQL is 'not even close' and SPs are the way to go. For example, ad-hoc SQL for complex behaviors will be more resource intensive than stored procedures - take adding a post in the forums (all within a transaction):
1. SELECT - Determine if the forum is moderated?
2. SELECT - If the forum is moderated does the user’s post require moderation?
3. INSERT - Insert post
4. UPDATE - Update related posts with the new child post id
5. UPDATE – Threads table with information about new post
6. DELETE – Clean the table that tracks what posts a user has read
7. UPDATE – If the user is tracking the post, update that a new post has been added
8. Call another procedure to update statistics on the forums
9. UPDATE – Threads statistics
I also asked a friend of mine who had a lot of input on a fairly well known and large enterprise resource and planning software package when it was ported to SQL server. His opinion:
‘If the ad-hoc sql is a parameterized statement, then the perf will generally be similar. Otherwise the folks are incorrect - especially if the statement does anything complex such as a join, a sub-query, or anything interesting in the where clause AND the statement happens more than once. This is to say nothing about other DBA issues such as securing objects, maintenance, abstraction, and reducing network traffic, troubleshooting, etc.’
My own conclusions:
- Both stored procedures and ad-hoc SQL have application maintainability issues. The maintainability is done either in the database or the data abstraction layer, i.e. update a stored procedure, recompile the application, or use an OR mapper. You need to determine where you want to solve this problem, with dynamic SQL it can mean recomilation of the application. With a stored procedure it means an update to the stored procedure.
- Both stored procedures and ad-hoc SQL can address security issues in a similar manner. If you are using ad-hoc SQL, use parameterized statements. Neither has specifc security advantages, although setting permissions only on stored procedures can be easier than individually setting permissions on a resource-by-resource basis.
- Both stored procedures and ad-hoc SQL can be used to solve the same problems and in some cases have equivalent performance. However, stored procedures are better for complex database behaviors such as paging or performing multiple actions either within a transaction or outside a transaction.
Thanks Frans, this has definitely proven to be a very valuable discussion! I think the ultimate conclusion is that there are several ways to solve data access problem and everyone should come to his or her own conclusions on what is right for the application. In some cases this is dynamic parameterized SQL (not string concatenation) in others it is stored procedures. However, my personal preference will be to use stored procedures for the majority of data access code, as I still believe the benefits still strongly outweight the costs.