Doug Reilly's Weblog
Embedded Reporting of the Information Age...
Tech Ed Lost and Found...
Are Software Development Books Obsolete?
If you are going to Tech Ed, Do Remember My Birds of a Feather...
.NET Compact Framework
Blogs I Read
Jason Nadal's Weblog
The Scobleizer Weblog
G. Andrew Duthie's Blog
ScottW's ASP.NET Weblog
Julia Lerman Blog
My Other Blog (Mobile Software Developer's Log)
Database Related Links
Dr. Dobb's Database Forum
June 2006 (3)
May 2006 (1)
April 2006 (3)
March 2006 (5)
February 2006 (3)
January 2006 (2)
December 2005 (2)
November 2005 (4)
October 2005 (5)
September 2005 (4)
August 2005 (3)
July 2005 (5)
June 2005 (6)
May 2005 (6)
April 2005 (3)
March 2005 (3)
February 2005 (2)
January 2005 (2)
December 2004 (3)
November 2004 (1)
October 2004 (2)
September 2004 (4)
August 2004 (4)
May 2004 (2)
April 2004 (1)
March 2004 (5)
February 2004 (1)
January 2004 (3)
December 2003 (1)
November 2003 (5)
October 2003 (11)
September 2003 (2)
August 2003 (4)
July 2003 (2)
June 2003 (2)
May 2003 (3)
Stored Procedures vs. Ad Hoc SQL Article online...
I have a
new article posted
online at Red Gate's site, discussing the SQL Server vs. Ad Hoc SQL debate. It covers, point by point, the arguments I have heard for and against using Stored Procedures on Microsoft SQL Server, and rates them as I see them. The most important point is that security against SQL Injection attacks can be had without requiring use of Stored Procedures, though there are some other reasons you might want to use them.
Mar 30 2005, 08:36 AM
Good point on the Sql Injection attacks. Sql Injection attacks are pretty easy to foil withou sprocs. don't get me wrong, I like sprocs/parameters as appropiate, but there are some situations where sprocs aren't really appropiate.
March 30, 2005 9:57 AM
I absolutely should have also linked to your rebuttal on TheServerSide.Net. I believe I saw that post, but did not notice it as I was writng my article. Thanks for adding that link. I think that most of your points in that rebuttal are on target, especially with respect to security, a much misused argument in favor of Stored Procedures that is really a red herring.
March 30, 2005 10:04 AM
There is an argument I didn't find in your article: by implementing (some or all of) the business logic in stored procedures, you allow to reuse that business logic from *all* the layers above. In some cases, that can be extremely useful. I work on medical applications that always have to integrate with some legacy code, sometimes running on very exotic platforms/environments. Tough (or impossible) to call .NET/COM/whatever code running on a middle tier from there, but usually OK to call SPs directly. Having most of the business rules at the database level ensures that whatever the call context (from middle tier or from a legacy application), the same rules will apply.
Just my 0.02€...
March 31, 2005 6:10 AM
Yep, that is certainly an additional reason why in some cases SP's can be useful. Even in cases where the platforms are not exotic, it can be useful to have the database enforce many of the rules in the application.
March 31, 2005 7:13 AM
Another useful reason for using sp's is that if you are changing the tables via dependencies you can easily see any sp's that access the tables and therefore might need to be changed to support the changes. If you are relying on ad-hoc SQL you have no way of knowing where a table is acccessed from...
This arguement falls down if you are using dynamic SQL within the sp's, but even these can be highlighted by searching the underlying system tables..
March 31, 2005 8:44 AM
It seems to me that there is room for both. I am not currently happy with the tools and facilities in SQL Server to add all my business rules there. The richer environment provided by VS.NET is better for that. Additionally, as I model the data, processes and workflow associated with applications, I really don't want to deal with the persistence of data. My preference is to set the integration point far above the database, even a layer above the where the business rules are implemented, allowing system and integration level rules to be implemented separately from the real business rules.
As for performance differences, where I have seen major benefits from using SPs is in very complex SQL. SPs would be a tool to help with that, but only as an implementation detail of my data access component, not a general rule. Very complex SQL in an OLTP transaction might just indicate a problem with the data model, and not the wrong choice between ad-hoc SQL and SPs.
March 31, 2005 9:38 AM
Thank you very much for this objective discussion of the argument - even your subjectivity was objectively described. It is helping me make an informed decision.
A year ago I questioned the SP-Only approach during a Best Practices talk (not knowing I was walking into a firefight). Most people deal with several applications attached to one database but I deal with one application looking at many similar databases. When I asked how to make only one set of stored procedures access several databases, the speaker recommended a stored procedure database. My investigations have shown that this only works if the SP database is the Master database. Hmmm... Can I really improve security by giving global access to the master database? Probably not. I would like to know how to do this safely (any ideas?) but for now, maintaining ad hoc SQL in one location is preferable to stored procedures in fifty.
March 31, 2005 10:14 AM
The argument is moot. What is important is the design of the client application with regard to data access. Whether smart client or web your design should have a logical data access layer. The logical data access layer maps the logical call to a specific physical implementation. The physical implementation can be loaded at runtime. This allows you to take advantage of RDBMS features without coupling your clients business logic.
March 31, 2005 10:34 AM
I certainly concur that there are limitations to what you can reasonably do in T-SQL. Of course, you can do litereally anything in T-SQL if you try hard enough, but generally doing set based operations will be best. I often find that I express my business rules in ways that lend themselves to expression as data.
March 31, 2005 10:50 AM
The only reason I can think of for putting stored procedures in their own database is to seperate the SP's from the data, so that, perhaps, you can more conveniently backup and restore your SP code. You can refenrence tables in other databases from an SP, so while it might require a little more typing, it can be done. Personally, I would not bother with that. I script SP's and views, and also use Red Gate's SQL Compare to move changes here and there.
March 31, 2005 10:53 AM
Certainly seperating database access from the presentation layer is critically important, regardless of how you access the database. I think the system I mention that uses pluggable DLL's that access either MS Access or SQL Server does that in one of the best ways possible. You could create an Oracle plugin DLL and add it into the system without requiring explicit code changes, presuming you provide a way for the plugins to be discoverable.
That said, going to that extent (allowing late binding of plug ins) may really go beyond what the customer needs. Or it might be a core requirement of the application. Each scenario pushes you in a different direction.
March 31, 2005 10:56 AM
Late binding or early binding has nothing to do with the 'good' design practice of separaing UI, logic and data access. If you have done this then implementing late binding of the data access implementation is trivial
So, my advice is to use good design prinicples and whatever implementation method is dictated by the zealot in charge :)
March 31, 2005 2:11 PM
Agreed. The main point is that you separate the UI, business logic and database access.
And then duck when the zealot in charge comes around<g>.
March 31, 2005 2:15 PM
One of the main reasons I prefer SP is ease of maintenance. If you have to correct the SQL and it's Ad Hoc then we have to distrubute a new DLL and have it installed - for windows apps this means it has to be updated at each workstation, for web apps the server has to be brought down for a short time. With an SP you can just update the database and everyone is up to date with no down time.
March 31, 2005 2:22 PM
" Frans also argues:
Now, let me add something about performance here. Say, you have a database with 100 tables, with an average of 7 fields per table. This requires 100 update stored procedures, at least (the crUd procs). These 100 procedures will have on average 7 parameters to update all fields at once, because you can’t create optional update statements in stored procedures.
This is just not true. By allowing default values for SP parameters, you can properly send only the parameters you need, and use default values and IsNull() to update a table with some values missing."
I saw this and I think I have to elaborate. Of course you can use nullable parameters and use COALESCE() or ISNULL tricks, but that's very slow and with more and more parameters this gets slower and the procs get hard to read. This is especially true for selects, the majority of the procedures in a proc API.
Btw, Oracle also doesn't compile procs to intermediate code, DB2 does.
"The part of this comment that I vehemently disagree with is that changes to the signature of a stored procedure will break your application. Other comments to Frans’ post continue to argue that changes to SPs will break client code. While it is possible that any change to the database will break client code, I would argue that SPs do, in fact, enable you to change the database without having to change the application. Let me give you an example."
Adding a column to a table will break the update and insert proc header. Adding a filter field to a select proc, requires that any code calling the proc has to pass in that filter field as well, thus if 3 apps use the proc, you have to update all 3.
What if you don't control 2 of them? You have to add a new proc. In Oracle you can overload procs, in SqlServer you can't really do that, there is no package facility.
It isn't that you can't add a parameter to a proc, it's that you have to change a lot of code if you want to do that. Any developer of an n-tier system with procs knows that. Every made a COM+ using n-tier app and there was a parameter change in a proc? Great fun :)
March 31, 2005 2:57 PM
As this thread demonstrates the use of SP or Ad Hoc SQL requires the context of the problem to be solved. This is often lacking in a zealots support of an 'only this way' position because the justification often does not have context. 'Only SP' or 'only Ad Hoc' will never satisfy all constraints.
The question that should be asked is 'Under what contraints would SPs be preferred over ad hoc SQL'. The question 'Should I use SPs or Ad Hoc SQL?' cannot be answered because the answer requires context.
As Doug states, you may need to only optimize 1%. The solution to the 1% shouldn't be constrained because the other 99% is ad hoc sql.
Any change to an interface has a probability of breaking the consumer of the interface. Here again, the solution is contextual. If you control the source for all consumers then change the SP interface and remediate the consumer code. If you do not control the source for all consumers then you should leave the SPn interface and create a new SPn+1. SPn would be modified to forward the call to SPn+1. However, you may not be able to do this if you cannot set default values for the new parameters in the interface...hmm more context.
March 31, 2005 5:03 PM
This thread seems to be missing an important point (one which I think Frans has made elsewhere): The description "ad hoc" is misleading. The debate should not be about SP's versus "ad hoc" SQL; instead it should be about SP's versus "runtime-generated SQL".
Everyone agrees that its bad to use "ad hoc" SQL - in the sense of SQL scattered through the code base with no standards followed. But, according to those of us on the "generation" side of this debate, the answer is not stored procs, it is to generate (or at least control) the SQL with a well-defined data access layer.
For instance, I wrote one which mixed carefully-controlled select statements with automatic support for adding extra selection criteria at runtime, and generating update statements. Another example would be mapping products like the one Frans makes.
By controlling the generation of SQL, we can mitigate the risks of SQL injection attacks, we can control the maximum number of rows returned, and so on... addressing many of the concerns raised in this thread.
March 31, 2005 7:49 PM