To SP or not to SP in SQL Server: an argument for stored procedures

Av rating:

Total votes: 45
Total comments: 26

send to a friend

printer friendly version

To SP or not to SP in SQL Server: an argument for stored procedures

06 June 2006

by Adam Machanic

The database-as-API approach

By Adam Machanic

This article is adapted from Adam's forthcoming book, Expert SQL Server 2005, to be published by Apress later in 2006.

A seemingly never-ending battle in online database forums involves the question of whether or not database application development should involve the use of stored procedures. A popular article on the Simple-Talk Web site (To SP or Not to SP in SQL Server) discussed various pros and cons of the stored procedure vs. ad hoc SQL debate, including security, performance, and maintainability. These are definitely interesting questions to consider when deciding which side of the fence you stand on, but I feel that a stronger argument can be made by revisiting some of the basic tenets of software architecture in order to determine how stored procedures might play a part in a properly designed system.

In my opinion, all external database access should occur through stored procedures. In this article, I review a few of the factors that inform this belief and discuss a way of thinking about the database that is aligned with sound software architectural practices. To begin with, a bit of background understanding is important, so I will introduce (or re-introduce) a few software architecture ideals that should be considered whenever designing any software –database, or otherwise.

Coupling, cohesion, and encapsulation

As a database developer, it can be tempting to place yourself in a "data bubble" and ignore the wide world of applications and users around you. The database may exist simply to store and retrieve data, but in order to do so it must interact with those who need the data therein. There are three concepts that I believe every database-oriented software developer must understand in order to build correctly designed databases that can interoperate well:

Coupling refers to the degree of dependency of one module in a system upon another module in the system. It can also refer to the degree of dependency that exists between systems. Modules, or systems, are said to be tightly coupled when they depend on each other to such an extent that a change in one necessitates a change to the other. Software developers should strive instead to produce the opposite: loosely coupled modules and systems.
Cohesion refers to the degree that a particular module or subsystem provides a single functionality to the application as a whole. Strongly cohesive modules, which have only one function, are said to be more desirable than weakly cohesive modules that do many operations and therefore may be less maintainable and reusable.
Encapsulation refers to how well the underlying implementation is hidden by a module in a system. This concept is essentially the juxtaposition of loose coupling and strong cohesion. Logic is said to be encapsulated within a module if the module's methods or properties do not expose design decisions about its internal behaviors. A properly designed module should control its own data and rules internally—and not have to rely on any consumer to properly do so. The idea here is to implement the logic exactly once and reuse it as many times as necessary, instead of implementing the logic wherever it needs to be used.

Unfortunately, these definitions are somewhat ambiguous, and even in real systems there is a definite degree of subjectivity that goes into determining whether a given module is or is not tightly coupled to some other module, whether a routine is cohesive, or whether logic is properly encapsulated. There is no objective method of measuring these concepts within an application. Generally, developers will discuss these ideas using comparative terms—for instance, a module may be said to be less tightly coupled to another module than it was before its interfaces were refactored. But it might be difficult to say whether or not a given module is tightly coupled to another, without some means of comparing the nature of its coupling.

For more information on these and related topics, please refer to the following Wikipedia articles:

Interfaces

The only purpose of a module in an application is to do something at the request of a consumer (i.e., another module or system). For instance, a database system would be worthless if there were no way to store or retrieve data. Therefore, a system must expose interfaces, well-known methods and properties that other modules can use to make requests. A module's interfaces are the gateway to its functionality, and these are the arbiters of what goes into, or comes out of, the module.

Interface design is where the concepts of coupling and encapsulation really take on meaning. If an interface fails to encapsulate enough of the module's internal design, consumers may rely upon some knowledge of the module, thereby tightly coupling the consumer to the module. Any change to the module's internal implementation may require a modification to the implementation of the consumer. An interface can be said to be a contract expressed between the module and its consumers. The contract states that if the consumer specifies a certain set of parameters to the interface, a certain set of values will be returned. Simplicity is usually the key here; avoid defining interfaces that modify return-value types based on inputs. For instance, a stored procedure that returns additional columns if a user passes in a certain argument may be an example of a poorly designed interface.

Many programming languages allow routines to define explicit contracts. This means that the input parameters are well-defined, and the outputs are known at compile-time. Unfortunately, T-SQL stored procedures only define inputs, and the procedure itself can dynamically change its defined outputs. It is up to the developer to ensure that the expected outputs are well-documented and that unit tests exist to validate them. I refer to a contract enforced via documentation and testing as an implied contract.

Stored procedures: the database-as-API mindset

An Application Programming Interface (API) is a set of interfaces that allows a system to interact with another system. An API is intended to be a complete access methodology for the system it exposes. In database terms, this means that an API would expose public interfaces for retrieving data from, inserting data into, and updating data in the database.

My contention is that this set of database interfaces should comply with the same basic design rules as other interfaces: well-known, standardized sets of inputs that result in well-known, standardized sets of outputs. This set of interfaces should completely encapsulate all implementation details, including table and column names, keys, indexes, and queries. An application that uses the data from a database should not require knowledge of internal information—the application should only need to know that data can be retrieved and persisted using certain methods. I call this the "database-as-API" mindset.

In order to define such an interface, the first step is to define stored procedures for all external database access. Table-direct access to data is clearly a violation of proper encapsulation and interface design, and views may or may not suffice. Stored procedures are the only construct available in SQL Server that can provide the type of interfaces necessary for a comprehensive data API.

So, in short, I believe that all data access should be via a fully-defined API, implemented using stored procedures. Following are the advantages I believe are gained by this approach.

Ease of maintenance

Perhaps the biggest advantage to the "database-as-API" approach is the reduction in coupling that can be achieved between the object system and the database it uses as a back-end.

If a change to the database requires an application change, it can often be expensive to recompile and redeploy the application. Likewise, if application logic changes necessitate database changes, it can be difficult to know how changing the data structures or constraints will affect other applications that may need the same data. However, by using stored procedures with correctly defined interfaces and full encapsulation of information, coupling between the application and the database can be greatly reduced. Changes to one layer do not necessitate changes to the other layer, resulting in a database system that is much easier to maintain and evolve over time.

By reducing dependencies and thinking of the database as a data API rather than a simple application persistence layer, you can also arrive at a much more flexible application development process. Often, this can permit the database and application layers to be developed in parallel rather than in sequence, thereby allowing for greater scale-out of human resources on a given project.

Ease of testing

Another issue is testability. If stored procedures are properly defined, with well-documented and consistent outputs, testing is not at all hindered – unit tests can be easily created in order to support test-driven development (TDD). Furthermore, support for more advanced testing methodologies also becomes easier, not more difficult, thanks to stored procedures. For instance, consider use of mock objects, which are façade methods that TDD practitioners create, which return specific known values. These are then substituted for real methods in testing scenarios such that testing any given method does not test any methods that it calls (any calls made from within the method being tested will actually be a call to a mock version of the method). This technique is actually much easier to implement for testing of data access when stored procedures are used, as mock stored procedures can easily be created and swapped in and out without disrupting or recompiling the application code being tested.

Greater security

Yet another important issue is security. Ad hoc SQL (as well as dynamic SQL) presents various security challenges, including opening possible attack vectors (i.e., points of entry for an attacker) and making data access security much more difficult to enforce declaratively, rather than programmatically. This means that by using ad hoc SQL your application may be more vulnerable to being hacked, and you may not be able to rely on SQL Server to secure access to data. The end result is that a greater degree of testing will be required in order to ensure that security holes are properly patched and that unauthorized users cannot access data they're not supposed to.

Note that SQL injection holes (where an attacker attempts to re-write server-side SQL by manipulating a client side text box) are equally problematic for both ad hoc SQL sent by applications and dynamic SQL used within stored procedures. In both cases, the key to solving this problem is use of proper parameterization. However, SQL injection is not the only kind of security problem that can occur. Stored procedures can help to combat such problems as escalation attacks, in which an attacker gains rights to resources other than those for which he is authorized. By using stored procedures, security is encapsulated within the data access interface. A pure ad hoc SQL solution, on the other hand, requires the application to programmatically ensure that authorization and authentication are properly enforced.

Equivalent performance

Finally, I will address the issue towards which online debates always seem to gravitate: performance. Proponents of ad hoc SQL make the valid claim that thanks to better support for query plan caching in SQL Server 2000 and 2005, stored procedures no longer offer much of a performance benefit (please note, this is only true if ad hoc or dynamic SQL is properly used – again, that means correct parameterization!). Although this sounds like a great argument for not having to use stored procedures, I personally believe that it is a non-issue. Given equivalent performance, I think the obvious choice is the more maintainable and secure option (i.e., stored procedures).

Summary

The stored procedure vs. ad hoc SQL question can become quite complex, with proponents of rapid software development methodologies such as TDD claiming that stored procedures slow down their process, and fans of object-relational mapping (ORM) technologies making claims about the benefits of those technologies over stored procedures. It does not help that many of the combatants in these battles happen to have a vested interest in ORM; some of the most heated debates in recent memory were started by inflammatory claims made by vendors of ORM tools. Many in the ORM community feel that the database should be used as nothing more than a very simple object persistence layer, and would probably be perfectly happy with a database that only had a single table with only two columns: a GUID to identify an object's ID, and an XML column for the serialized object graph.

In the end, the stored procedure vs. ad hoc SQL question is really one of purpose. In my eyes, a database is much more than just a collection of data. It is also an enforcer of data rules, a protector of data integrity, and a central data resource that can be shared amongst multiple applications. For these reasons, I firmly believe that a decoupled, stored procedure-based design is the only way to go.

###

Adam Machanic is an independent database software consultant, based in Boston, Massachusetts. He has implemented SQL Server solutions for a variety of high-availability OLTP and large-scale data warehouse applications, and also specializes in .NET data access layer performance optimization. Adam is co-author of Pro SQL Server 2005 and is currently in the process of solo-authoring "Expert SQL Server 2005". He regularly speaks at user groups and community events throughout New England.

This article has been viewed 15230 times.

Subject:	I agree
Posted by:	Anonymous (not signed in)
Posted on:	Wednesday, June 28, 2006 at 5:34 AM
Message:	I totally agree.

Subject:	Nice work Adam!
Posted by:	Anonymous (not signed in)
Posted on:	Thursday, June 29, 2006 at 9:03 AM
Message:	Adam, You have done an outstanding job of boiling down this debate into things developers can grasp and stand behind. So many times, as you state, the topic turns to performance. I see many developers arguing, that, given equivalent performance, they don't want to change languages in order to do data access. You have provided good reasons to do that. As always, you have done a good job of bridging the gap between developers and DBAs. Jon Baker

Subject:	More than nuts and bolts
Posted by:	Anonymous (not signed in)
Posted on:	Thursday, June 29, 2006 at 11:15 AM
Message:	It's always good to read an article that broadens the perspective beyond the basic nuts and bolts and discusses technology within an architectural context. Thanks Adam!

Subject:	Blogs are dumb
Posted by:	Anonymous (not signed in)
Posted on:	Friday, June 30, 2006 at 9:03 PM
Message:	I'm responding to a blog so it means I'm dumb too

Subject:	Wow! great article!
Posted by:	Anonymous (not signed in)
Posted on:	Wednesday, July 05, 2006 at 9:19 AM
Message:	I love Adam's articles as they are so thoughtful - he goes beyond the syntax of sql and approaches development from an architectural perspective which is very refreshing.

Subject:	Weak
Posted by:	Anonymous (not signed in)
Posted on:	Thursday, July 06, 2006 at 10:34 AM
Message:	I hope that you don't spout your opinion in you new book Expert SQL Server 2005.

Subject:	Truckers say, "uh-huh" to each other
Posted by:	Anonymous (not signed in)
Posted on:	Sunday, July 09, 2006 at 1:05 PM
Message:	Nothing new here; these are the same arguments/discussion points belabored ad nauseum in the forums. This topic really doesn't matter except to newbies since everyone else has their own opinion and it's certainly true that opinions are like ...

Subject:	When to use...
Posted by:	Anonymous (not signed in)
Posted on:	Monday, July 10, 2006 at 9:23 AM
Message:	As with anything else, stored procedures should be used whenever they are appropriate and not be used when they aren't. Ad hoc access may be a neccessity for some systems. In those cases, making use of stored procedures is not an option. It just depends.

Subject:	You're arguing for a data tier, not SPs
Posted by:	johnwood (view profile)
Posted on:	Thursday, July 13, 2006 at 12:33 AM
Message:	Everything you say here could equally be applied to a data access layer written in any language. What about having a process, written in C#, that encapsulates all access to the database? An application database server, if you will. If it's a physical tier then you gain all the same security benefits, cohesion, testing and maintenance. T-SQL is a terrible language, this way you gain all the benefits of a modern day language and its IDE, and all the benefits of a data access layer that you mention above.

Subject:	Application code DALs
Posted by:	Anonymous (not signed in)
Posted on:	Thursday, July 13, 2006 at 11:19 AM
Message:	Johnwood, Thanks for your comment. I do not agree that a DAL written in an application layer is equivalent to a stored procedure layer, for the following reasons: A) Security. Stored procedures support T-SQL's declarative security model. An application layer's security would have to be programmed. This is both a reinvention of the wheel and a vector for introducing bugs that could make it more likely that some errant code will violate the security policy. B) Performance and resultant maintenance. One way or another, you are going to have to send some T-SQL into the database to query the data. You could use an object/relational mapper to automate writing the T-SQL, but these solutions have well known performance issues. The fact is, many complex queries do require some expertise to write if you want them to perform well. Given this fact, you are going to have to tweak some queries at some point. Is it really easier to write T-SQL in, e.g., C# or VB.NET, than in T-SQL? Personally, I think that opens you up to a maintenance nightmare. C) Application-agnostic data. This one is probably the most important factor from my point of view. I used to work for a company that had a variety of applications, written in: C#, Java, VB6, ASP, PHP, and Jython (I think that's the entire list. I can't remember in full detail at this point; I think I've blocked the memories!) Anyway, all of these applications shared the same database, and used the same stored procedures for data access. Would you be able to set up such a flexible environment if you programmed your access layer in C#? You could, perhaps, expose everything as a web service, but what efficiency cost would you pay? I hear a lot of talk about DBMS lock-in, but there are also application layer lock-in issues. And as a DB guy, I'd rather be locked to a specific DBMS... but YMMV on that one :)

Subject:	Re: Application code DALs
Posted by:	Anonymous (not signed in)
Posted on:	Thursday, July 13, 2006 at 10:42 PM
Message:	A) Au contraire - a security model implemented in a DAL allows the security to be more business oriented, something which is sorely lacking in most enterprise architectures. Security decisions are, in actual fact, business logic - they don't belong in the database. This isn't reinventing the wheel, it's inventing a better solution to a critical problem. B) I've converted a lot of T-SQL to C# in my time. Almost every time I've seen a performance boost in the resultant code. It's easy to think that writing stuff 'closer to the metal' is going to give you a performance boost, but the issue is that the lower the level you write against you end up with code that is a) more difficult to maintain because of reduced readability, b) far removed from the language of the requirements, and c) more error prone because of the greater complexity of the code. When you rewrite it in something more high level you're naturally optimizing the code because it's easier to understand what the code is doing and areas needing optimization become clearer. Of course it helps that in languages like C# you have far more tools at hand - hashtables, tree structures etc. In this day and age, depending on the scalability issues, there's often plenty of RAM available to do some serious caching that a tempdb wouldn't compare against. C) Application agnostic. Sure, web services, binary XML-RPC, JSON.. whatever you want. Heck I could even code up something that made my C# routines look like stored procedures in a database by implementing the protocol and fooling the SQL Server driver. Performance isn't really an issue at that level, what's important is having the transaction itself run in a performant manner. The time between DAL calls isn't quite so critical in my experience. Sorry, still not convinced. John Wood http://dotnetjunkies.com/weblog/johnwood

Subject:	SP are the way to go because of people
Posted by:	Anonymous (not signed in)
Posted on:	Friday, July 14, 2006 at 11:28 PM
Message:	IMO, stored procs truly shine when there are multiple applications from multiple development teams targeting the same SQL Sever. The problem with the DAL approach (having a DAL through which everyone accesses the database) is that it is difficult to guarantee that all data access of any kind will go through that one DAL. The moment someone writes an app that bypasses the DAL, you lose your control over the data access. I haven't even mentioned the problem of multiple platforms (Java, C++ etc). Another problem with the DAL approach is that designing SQL statements has become a lost artform. What you avoid (or can far more easily correct) with stored procs is the application designer writing something like "Select * From BigHugeFatTable Cross Join OtherBigHugeFatTable" and it bringing down your site. Tracking is another problem. How do you track down a bad SQL statement if you are using a DAL? There might be multiple routines that potentially produce the same SQL statement. This is doubly hard when SQL statements are assembled using sophisticated logic. I don't doubt that it is possible, but far more difficult than tracking the duration of a long running stored procedures. On small applications or small teams where absolute control over code access to the database can be maintained, either a DAL or stored procs can work equally well. However, when you start adding dozens of teams working against the same database, the DAL solution, IMO, breaks down. I'd like to address these two arguments against stored procs: "You can change database vendors" This is a mostly empty argument for a two reasons. First, it is rare that an organization will change a database system without also overhauling many of the other systems based on that database system. Second, this argument is valid ONLY if ALL SQL statements are compliant with the features implemented by ALL database vendors and those features are implemented in same manner across all vendors. Welcome to the world of lowest common denominator. That means, for example, you cannot use the TOP command or identity columns or a host of other vendor specific features. I will grant proponents of this argument this much, it does make it easier, but far from easy to switch database vendors. "You'll have to write hundreds of procedures..." Wouldn't this be true in a DAL? Looking past that fact, I think it is worthwhile to go through that exercise. A project on which I'm working now, the developer that originally designed the database used a code gen tool to generate dozens of stored procs...most of which are not used. This will make maintenance a headache. Furthermore, it shows a lack of understanding about what is needed from the database. OO != Relational design. Most C# developers do not get relational design. Databases typically stick around a lot longer than the applications that write to them. Designing and accessing the database should be a deliberate process.

Subject:	SQL security isn't the only security available
Posted by:	Anonymous (not signed in)
Posted on:	Saturday, July 15, 2006 at 10:37 AM
Message:	>> it is difficult to guarantee that all data access of any kind will go through that one DAL. << This is a security issue. The database should be logically and physically protected so that it can only be accessed from one process and one process only - the DAL server. There are network configuration measures that can be taken to physically isolate the database. >> I haven't even mentioned the problem of multiple platforms << I think that's covered in my application agnostic response. >> Another problem with the DAL approach is that designing SQL statements has become a lost artform. << Much like writing assembly language has become a lost artform. Would it really be missed? >> Tracking is another problem. How do you track down a bad SQL statement if you are using a DAL? << I'm not sure what problem you're getting at here. It depends on the DAL implementation. If it involves lots of manually written SQL, then I'm sure it wouldn't be that difficult to track it down - after all at least it's all in one module. If it's automatically generated then there shouldn't be any bad SQL, or if there is then it's a matter of tracking down the DAL's usage and putting in appropriate restrictions. Is it more difficult than tracking down bad statements in SPs? I'm not really sure because SPs can become incredibly complex and hard to maintain. >> However, when you start adding dozens of teams working against the same database, the DAL solution, IMO, breaks down. << My point is you wouldn't have dozens of teams working against the same database, they would be working against the same DAL. This does require some coordination, but only a little more than if they were writing SPs. >> "You can change database vendors" << In my experience this never actually happens. Other than scaling up. eg, systems that were, perhaps, originally developed using jet and were scaled up to SQL. But usually the jet based systems were sufficiently simple to easily translate to SQL Server's SQL. >> "You'll have to write hundreds of procedures..." << The benefit in a DAL is that, with a modern-day object oriented language, you're able to re-use code effectively, both your own and the framework's, and create abstractions. This kind of advanced programming just isn't easy or possible with T-SQL. John

Subject:	Aren't you having to reinvent the DBMS wheel?
Posted by:	Anonymous (not signed in)
Posted on:	Saturday, July 15, 2006 at 1:24 PM
Message:	>> it is difficult to guarantee that all data access of any >> kind will go through that one DAL. << > This is a security issue. The database should be logically and physically > protected so that it can only be accessed from one process > and one process only - the DAL server. IMO, that is difficult to achieve and maintain especially in a large enterprise. Said another way, it more difficult to achieve that goal than it is if you are using stored procs to control access to the database. You are kind of suggesting a DAL service which I'll grant you would provide sufficient isolation. >> Another problem with the DAL approach is that designing SQL >> statements has become a lost artform. << > Much like writing assembly language has become a lost artform. Would it > really be missed? Huh? That is not necessary with assembly because we have high level languages with which to create the assembly or binary which are lower level languages. SQL IS a high level language. Even with a DAL someone still has to write SQL statements. Not every operation that acts against a database is a simple CRUD operation. In the end, the application developers are charged with writing the SQL statements and in my experience most application developers are not versed in SQL. >> Tracking is another problem. How do you track down a bad >> SQL statement if you are using a DAL? > I'm not sure what problem you're getting at here. It depends on the DAL > implementation. If it involves lots of manually written > SQL, then I'm sure it wouldn't be that difficult to track > it down - after all at least it's all in one module. If > it's automatically generated then there shouldn't be any > bad SQL, or if there is then it's a matter of tracking down > the DAL's usage and putting in appropriate restrictions. Is > it more difficult than tracking down bad statements in SPs? > I'm not really sure because SPs can become incredibly > complex and hard to maintain. Actually, it is substantially easier to track down problems with stored procedures because of the SQL Profiler. In profiler, I might see "Select FN, LN.. From People". That code might be generated or called from numerous different entry points through the DAL. In fact, if the DAL is dynamically generating SQL you might instead have to go through the application code to find the problem. With stored procs, it no longer matters what is causing that proc to execute. You can analyze the cost of that call and correct it. In other words, the DBA does not have to know anything about the application in order to analyze the cost of the queries against the database and correct them. >> However, when you start adding dozens of teams working >> against the same database, the DAL solution, IMO, breaks >> down. > My point is you wouldn't have dozens of teams > working against the same database, they would be working > against the same DAL. This does require some coordination, > but only a little more than if they were writing SPs. The DAL solution works if you have the equivalent of a DBA that institutes absolute control over access to the database through the DAL. It is workable, but IMO difficult to enforce. Let's suppose that all access to the database is through a specific user (SQL or Windows) given to the DAL. It is too easy for those credentials to be used by another team to access the database through their own DAL. IMO, it is substantially easier to achieve the equivalent level of security through stored procedures than with the DAL solution. That is not to say you cannot do it with a DAL but the time you save generating code is made up in maintenance and security headaches. >> "You'll have to write hundreds of procedures..." > The benefit in a DAL is that, with a modern-day object oriented > language, you're able to re-use code effectively, both your > own and the framework's, and create abstractions. Good point.

Subject:	Re: Aren't you having to reinvent the DBMS wheel?
Posted by:	Anonymous (not signed in)
Posted on:	Saturday, July 15, 2006 at 1:48 PM
Message:	>> SQL IS a high level language. << I don't really like the term 'high level' or 'low level' language. SQL is a domain specific language. The domain is the database. Assembly language is a domain specific language, where the domain is the processor, memory and device ports. System requirements are written in business language and the implementation process involves translating that language into implementation domain terminology. The key is the ability to construct abstractions because this allows us to transform a language into something that is domain, or more specifically business focused. Bridging the gap between the domain of the implementation and the domain of the requirements is key to constructing a system that is easier to maintain and extend. 3GL compilers and JIts do a great job of translating abstractions into assembly language, to me it doesn't seem to be a stretch to extend this concept to abstractions and databases. >> In the end, the application developers are charged with writing the SQL statements << Traditionally perhaps. But that's not a prerequisite of a DAL. There are emerging technologies that dynamically translate intentions specified in 3GLs to database manipulation languages such as SQL. DLinq (Linq for SQL) is a good example of that. There are others too, that are perhaps even more advanced. >> Actually, it is substantially easier to track down problems with stored procedures because of the SQL Profiler. << I'll grant you that. >> It is too easy for those credentials to be used by another team to access the database through their own DAL. << Like I said there are other preventative measures, such as router configuration. I agree it would be a bit of a headache, but then again it would only be necessary in a large enterprise where you'd have a pretty proficient network team anyway. (or at least that would be the theory). I agree it's potentially a lot more work up-front. But I do believe in this architecture as a way to provide a more maintainable and extensible solution in the long term. John

Subject:	Replies to recent threads
Posted by:	Adam Machanic (view profile)
Posted on:	Monday, July 17, 2006 at 11:11 AM
Message:	To JohnWood and Anonymous replyer... > a security model implemented in a DAL allows the > security to be more business oriented How so? Business objects need to retrieve data from the database one way or another, and the same data can be secured the same way at either tier. Essentially, data security is data security. One key difference is that in an application tier, the security is implemented programmatically instead of declaratively. The other difference is that by securing data in the data tier, you ensure that ALL business objects will have a uniform view of the data, security-wise. Can you make that same guarantee in the app tier? > I've converted a lot of T-SQL to C# in my time. > Almost every time I've seen a performance boost > in the resultant code. Can you substantiate this claim? Are you saying that you can, e.g., convert an SQL INNER JOIN into a loop in your procedural code and it will give you a performance boost? Or are you instead suggesting that you've converted -procedural- T-SQL into -procedural- C# and seen a performance boost? These are completely different issues. In the first case, you would be replacing the actual data access code. In the second case, you've probably simply moved business logic between tiers. The question here is not one of the business logic, but the data access itself. If you can truly beat SQL at its own game (pure data access), I hope you will share your code with us! > there's often plenty of RAM available to do some > serious caching that a tempdb wouldn't compare > against. First of all, tempdb is not a data caching mechanism. It is a temporary storage area, and has nothing to do with caching. If you are using tempdb for caching, I can see why you think that SQL Server has issues in this area. But beyond that, you cannot really compare SQL Server's data caching with what you have in .NET. () You have different tools in .NET because .NET has totally different goals than does SQL Server! SQL Server's cache is all but invisible to users -- there is almost nothing you can do to manipulate it, and you don't really need to think about it in most cases. It's a primary cache, used internally by the server. A caching system written in .NET, on the other hand, has to reinvent a lot of what SQL Server already does automatically -- making sure the data is consistent, updated appropriately, making sure that changes are cascaded back to the data store appropriately, etc. This is a secondary cache, and while it certainly has its place, it really has nothing to do with the stored procedure vs. ad hoc SQL debate. In both cases, stored procedures can (and, IMO, should) be used. And in both cases, SQL Server -will- do its own caching -- it's not like you have a choice there. > The moment someone writes an app that bypasses > the DAL, you lose your control over the data > access. EXACTLY! > The database should be logically and physically > protected so that it can only be accessed from > one process and one process only - the DAL > server. Reinvention of the wheel, anyone? Let's not use stored procedures, and instead roll our own layer that acts just like stored procedures, and even control access so that it's the only layer that can be used... Why not just use stored procedures to begin with? > Much like writing assembly language has become a > lost artform. Would it really be missed? Yes, it would! I feel like you're implying that just because you don't know (assembly language, SQL, or other languages) they should not be used by practitioners who do understand how to use them. Please correct me if I'm mistaken in that assumption, but it is certainly what I'm taking away from your arguments. > If it's automatically generated then there > shouldn't be any bad SQL Really?? How well do you understand the performance differences between: SELECT FROM Tbl1 WHERE Tbl1.ID NOT IN ( SELECT ID FROM Tbl2 ) vs. SELECT * FROM Tbl1 WHERE NOT EXISTS ( SELECT * FROM Tbl2 WHERE Tbl2.ID = Tbl1.ID ) ... and does your DAL understand those performance differences? Will it make the right choice EVERY TIME with no intervention (which means, "no bad SQL")? Who is writing this DAL? Do they understand the issues? You seem to be implying that developers shouldn't have to know SQL, but someone needs to write this DAL. Who are these developers, and how will they know enough SQL to write an effective DAL? > I'm not really sure because SPs can become > incredibly complex and hard to maintain. And .NET code or other client code can't? Again, it seems to me that your argument is based on your personal lack of understanding of SQL. And I am trying to stay out of the realm of ad hominem attacks, so please do not take this as a dig -- but do you think that if you understood SQL a bit better, you'd still feel that it was any more complex than the client code that you do happen to understand at the moment? > My point is you wouldn't have dozens of teams > working against the same database, they would be > working against the same DAL. The benefit being what, exactly? How would this be any more or less flexible than a stored procedure layer? > This kind of advanced programming just isn't > easy or possible with T-SQL. Those of us who do database work for a living would tend to disagree. Can you show examples of what kind of flexibility you're looking for that you don't feel can be reproduced using views or stored procedures (or some combination thereof)? > The DAL solution works if you have the > equivalent of a DBA that institutes absolute > control over access to the database through the > DAL. Exactly. Re-invention of the wheel. Re-invention of the DBA. Why not just use the right tools to begin with, instead of re-creating them? If you don't know SQL, hire someone who does. If you don't have a DBA, hire one. Can a developer with no understanding of data management issues, really create a better data management system and then manage the data better than a data specialist?

Subject:	Adam...
Posted by:	johnwood (view profile)
Posted on:	Monday, July 17, 2006 at 4:15 PM
Message:	>> How so? Business objects need to retrieve data from the database one way or another, and the same data can be secured the same way at either tier. << The decisions that are made in order to grant or deny access to specific bits of data are often more complex than "this person can access this table but not this one". Sometimes it involves understanding the specific rows that are requested. These type of business decisions are best left to a language that works on a domain model than T-SQL. >> (on T-SQL to C# and performance) Can you substantiate this claim? << I can certainly clarify it. In one instance I converted a P&L engine that was written in T-SQL - something which is quite data access intensive - to a process that cached the table content in memory and performed the same calculations in C++. It was about 50 times faster, although this was 5 years ago. I've converted some T-SQL batch processes that transferred data nightly between two databases. Again I just download the data I need and perform the processing mostly in memory, then dump the result into the new database. It was also considerably faster. Sure there will be simple inner joins that will be faster to run in one sql statatement, but I'm just pointing out that it's not always the case. >> Reinvention of the wheel, anyone? ... Why not just use stored procedures to begin with? << a) To provide a modern day language object oriented language, b) To provide all the richness of the .Net framework, c) to allow the data access to be wrapped in domain specific classes to protect the business logic, c) To provide better tools for debugging etc. The list goes on. I admit a lot of these features are covered by SQLCLR, but that's not what we're discussing here it's SPs vs. a DAL. >> I feel like you're implying that just because you don't know (assembly language, SQL, or other languages) they should not be used by practitioners who do understand how to use them. << I'd like to consider myself proficient in assembly language and comfortable in SQL, but I'm no DBA that's correct. >> How well do you understand the performance differences between... << I haven't looked at the execution plans for your two examples but I'd imagine that the second sample would perform better, because perhaps the first sample would run the select to create the set it's comparing against for each row it tests. Perhaps the second might be optimized as a join. But the question is, in what situation would the inferior sample EVER be better? There is usually only one best way of querying the data if you understand the data, it's keys etc. This is information that a DAL could understand if it were written well. Why not? The DAL should be based on either generated code or a good SQL adapter. Both the code templates and the SQL adapter should be written by someone who does understand SQL very well - a DBA or other SQL expert. >> ("complex and hard to maintain.") And .NET code or other client code can't? << Most .net languages allow for the construction of abstractions, and this helps keep the code simple to understand and easier to maintain. >> If you don't know SQL, hire someone who does << My answer would be - if you don't know SQL, buy a code generator that does. If it works in 99% of the cases and is sufficiently performant, then how do you justify telling someone to learn SQL when they don't need to? Too many people try to write T-SQL when they don't understand it and you end up with spaghetti code. There is much better support for popular general purpose languages like C#. >> Can you show examples of what kind of flexibility you're looking for that you don't feel can be reproduced << Abstractions! The only way to protect business logic is by coding it against abstractions that isolate that logic from the specifics of SQL or even a RDBMS. I want my business logic to see an invoice as a unit, not have to go to one table to get the customer details, another to get the invoice details, another to get the line items. As far as I know this kind of simple encapsulation isn't possible in T-SQL.

Subject:	More for John...
Posted by:	Adam Machanic (view profile)
Posted on:	Monday, July 17, 2006 at 10:06 PM
Message:	> Sometimes it involves understanding the specific > rows that are requested. Your use of the term "rows" makes me think once again that it's a data question. Why is T-SQL not up to the challenge? I can easily implement a view-based row-level security scheme in SQL that will be enforced equally for every business object that accesses the data. Why would a C# solution be better? > In one instance I converted a P&L engine that > was written in T-SQL <snip> > I've converted some T-SQL batch processes that > transferred data nightly between two databases. An analysis engine and an ETL process -- both, I'd agree, are better suited to application code. In both cases, the data requires significant application-specific logic/massaging. Firmly in the realm of business logic, and clearly not the domain of T-SQL. Note that in the latter case, a tool even ships with SQL Server to do the work (SSIS). But now think about the code to RETRIEVE the logic for the analysis engine. Why is -that- code not well-suited to a stored procedure? That is the kind of code I'm talking about in my examples. > To provide a modern day language object oriented > language Which brings what to the table, in terms of data access? > To provide all the richness of the .Net > framework, Again, what does this bring to the table, in terms of data access? > to allow the data access to be wrapped in domain > specific classes to protect the business logic Why does the business logic need to be protected? Shouldn't the business logic be in the business tier, away from the data access altogether? > To provide better tools for debugging etc. Can you step-debug a SQL statement? I don't think it can be done. If your code is so procedural that it needs that kind of debugging, I agree -- it probably doesn't belong in a stored procedure. But again, that's probably a sign that you've merged the business logic and data access logic way too much. > that's not what we're discussing here it's SPs > vs. a DAL. No, the question is not SPs vs. a DAL. Rather, SPs as a or part of a DAL vs. a DAL based on ad hoc SQL. You can certainly wrap a secondary access layer over stored procedures -- and I'd recommend doing so in many cases! Regardless, I'd still use stored procedures for the base data access. > I haven't looked at the execution plans for your > two examples but I'd imagine that the second > sample would perform better <snip> > There is usually only one best way of querying > the data if you understand the data, it's keys > etc The answer is, "it depends" -- and finding that "best way" can be a difficult process. "Best" can change over time based on your indexes, your data, and other fuzzy factors that I think you'd need an AI system to estimate (or, just a good DBA). And worse, these factors are time based, modulating as the data and usage patterns change. If you can create a self-tuning DAL that can handle even those simple examples, you will make a heck of a lot of money and put SQL Server, Oracle, DB2, and any other DBMS with a query optimizer pretty much out of business. The fact is, it's really hard to tell a computer how to figure this stuff out, yet it's pretty easy for certain humans to do it. Put in .NET terms: Can you write a program that will automatically profile and re-write/tune all of your .NET code, or do you use profilers to find the issues and do the refactoring yourself? And if so, how can you expect a computer to do that with T-SQL? > Most .net languages allow for the construction > of abstractions, and this helps keep the code > simple to understand and easier to maintain. And SQL does not? What is a view, if not an abstraction layer? > Too many people try to write T-SQL when they > don't understand it and you end up with > spaghetti code. There is much better support for > popular general purpose languages like C#. Perhaps they shouldn't be writing SQL, then? SQL is a lot older and better established than C#, and probably has a bigger core audience -- why do you feel the latter has better support? > I want my business logic to see an invoice as a > unit, not have to go to one table to get the > customer details, another to get the invoice > details, another to get the line items. As far > as I know this kind of simple encapsulation > isn't possible in T-SQL. Then write a query that joins the tables and returns a single result. Or better, create a view! Is it really that difficult?

Subject:	The Saga continues...
Posted by:	johnwood (view profile)
Posted on:	Tuesday, July 18, 2006 at 1:42 AM
Message:	Adam, >> Your use of the term "rows" makes me think once again that it's a data question. Why is T-SQL not up to the challenge? << It's not a matter of whether it is capable - it's whether it's the best tool for the job. Business logic and T-SQL don't mix. If you're encoding your business logic into T-SQL then you're losing that logic. It's no longer written in terms of domain specific abstractions and it's tightly coupled with an unnatural relational model. Once you translate business logic into a language specific to databases it's lost. If I had to get a business specialist to look over the business logic they wouldn't be able to and that's a problem. If you believe that security is business intelligence then your security shouldn't be implemented in T-SQL. >> But now think about the code to RETRIEVE the logic for the analysis engine. Why is -that- code not well-suited to a stored procedure? << I assume you meant data and not logic. That code isn't suited to be a stored procedure because it's not necessary. If the DAL is the only process that can access the database, why should a select statement to get data from a table go through an SP? With execution plan caching it's just overkill to maintain an SP for that. >> Which brings what to the table, in terms of data access? << Like I said, abstractions. And no, a view isn't an abstraction. The purpose of abstracting data is to make it appear natural to a specific domain. A view returns data in a two dimensional list. The only way this can get even close to representing structured data is by adding additional columns and duplicating the values in the table. Or it can leave you to get the data yourself from multiple tables. Absolutely no encapsulation of data. This is what object orientation brings. But no, I'm not actually much of an OOP advocate. Just supporting nested data, structured hierarchical data, would be enough to make me happy. You get something like this with XML data types in Yukon but it's barely schema driven and sticks out like a sore thumb against the rest of the SQL data platform. >> Why does the business logic need to be protected? << Protected in the logical rather than physical sense. It needs to be protected in terms of cohesion. Mixing domains breaks that cohesion, that's what I mean by protecting business logic. >> If your code is so procedural that it needs that kind of debugging, I agree -- it probably doesn't belong in a stored procedure. << This is the crux of the matter. When I'm talking about SPs I'm picturing the procedural ones, not just simple select statements. Lots of transactional jobs are implemented in T-SQL - sometimes thousands of lines long. So do you agree that T-SQL should not be used as an imperative language? If so then perhaps we're getting somewhere. Except... >> SPs as a or part of a DAL vs. a DAL based on ad hoc SQL. << I'm big on rich clients. Actually I'm big on efficient rich clients. In particular I need the ability to specify which columns I'm interested in seeing. As a user I want to customize grids and specify which columns to see. As a developer there may be times at runtime when I need to change the columns I request. Another example is adhoc reporting which is becoming very popular these days. These things just aren't possible with the rigid constraints that SPs place on developers. >> ...and do the refactoring yourself? And if so, how can you expect a computer to do that with T-SQL? << I think you're blowing this out of proportion a little. I can guarantee that in 99.99% of cases, these types of performance issues just aren't worth even discussing let alone optimizing dynamically at runtime based on adhoc queries. In the majority of cases, unless you have a really poorly designed database, you can get acceptable performance through simple inner/outer joins and unions.

Subject:	DAL Scalability
Posted by:	Anonymous (not signed in)
Posted on:	Friday, September 01, 2006 at 9:52 AM
Message:	SQL on the wire <> Scalability +++++++++ You developers trying to justify table level data access at the application layer, or even SQL within a DAL (pick your language) are whistling in the dark. So too are you DBA types that think the only way to serve up the bits is from a stored proc quasi-API (managed code or not) on the SQL box. First off, whether scalability becomes an issue or not, you need to encapsulate all DB access to a DAL. Why? Because direct access to the SQL box from anywhere but a DAL is just bad multi-tier architecture. Second, if you are going to have a DAL you better use T-SQL stored procs on the SQL box to service it. Why? Constructing CRUD statements in the DAL is just stupid, as is writing CRUD in managed code. Why? It doesn't properly leverage the SQL Server's capabilities. CRUD in the DAL breaks encapsulation, while CRUD in managed code just doesn't perform as well as CRUD in T-SQL. And CRUD in the DAL doesn't provide adequate security. Even ad hoc dynamic SQL built by the DAL at run-time needs to be executed at the DB by spExecuteSQL, for a host of reaons. And although I can see a certain elegance in an object constructor in managed code at the SQL Server, essentially you just moved the CPU cycles needed to produce an object in the DAL from one physical platform (the application server) to the SQL box - and now you have to use the SQL box's network resources to ship the object to the requestor. Regardless of your transport mechanism you have created a network bottleneck - and scalability is compromised. So, UI <--> BL <--> DAL/ORM <--> spORM <--> spCRUD is much better, it leverages the strengths of the various platforms involved, and minimizes risks. It is loosely coupled, encapsulated, abstracted. And you can pick your transport, too, based on the users preference, without using any resources at the SQL Server. If scalability is not a problem, the DAL component can run on the same physical machine as the SQL Server. Then, if and when you need to scale, you simply move the application side of the ORM channel to another machine and go home to play with the puppies! Anyway, at the end of the day we are all history. UML based automated software factories will be the death of us...

Subject:	DAL EQUALS SCALABILITY
Posted by:	Anonymous (not signed in)
Posted on:	Friday, September 01, 2006 at 9:52 AM
Message:	SQL on the wire <> Scalability +++++++++ You developers trying to justify table level data access at the application layer, or even SQL within a DAL (pick your language) are whistling in the dark. So too are you DBA types that think the only way to serve up the bits is from a stored proc quasi-API (managed code or not) on the SQL box. First off, whether scalability becomes an issue or not, you need to encapsulate all DB access to a DAL. Why? Because direct access to the SQL box from anywhere but a DAL is just bad multi-tier architecture. Second, if you are going to have a DAL you better use T-SQL stored procs on the SQL box to service it. Why? Constructing CRUD statements in the DAL is just stupid, as is writing CRUD in managed code. Why? It doesn't properly leverage the SQL Server's capabilities. CRUD in the DAL breaks encapsulation, while CRUD in managed code just doesn't perform as well as CRUD in T-SQL. And CRUD in the DAL doesn't provide adequate security. Even ad hoc dynamic SQL built by the DAL at run-time needs to be executed at the DB by spExecuteSQL, for a host of reaons. And although I can see a certain elegance in an object constructor in managed code at the SQL Server, essentially you just moved the CPU cycles needed to produce an object in the DAL from one physical platform (the application server) to the SQL box - and now you have to use the SQL box's network resources to ship the object to the requestor. Regardless of your transport mechanism you have created a network bottleneck - and scalability is compromised. So, UI <--> BL <--> DAL/ORM <--> spORM <--> spCRUD is much better, it leverages the strengths of the various platforms involved, and minimizes risks. It is loosely coupled, encapsulated, abstracted. And you can pick your transport, too, based on the users preference, without using any resources at the SQL Server. If scalability is not a problem, the DAL component can run on the same physical machine as the SQL Server. Then, if and when you need to scale, you simply move the application side of the ORM channel to another machine and go home to play with the puppies! Anyway, at the end of the day we are all history. UML based automated software factories will be the death of us...

Subject:	Re: SQL on the wire Scalability
Posted by:	Anonymous (not signed in)
Posted on:	Sunday, September 03, 2006 at 9:57 PM
Message:	Anonymous - the majority of your arguments seem to come down to "don't do it coz it's bad". Saying that it "doesn't leverage SQL Server's capabilities" makes little sense unless you specify what capabilities you're talking about. If it's performance I think we've already established that in the majority of cases performance of SPs vs. dynamic SQL is much ado about nothing. If it's security, I explained why SPs aren't needed for that either, so long as you properly and physically isolate the DAL server from the rest of the system. Your: UI <--> BL <--> DAL/ORM <--> spORM <--> spCRUD ...is utterly over complicated for the majority of systems. Even where the size of the system might suggest justification, there are still easier implementations that don't involve so many layers, and in particular don't involve writing stored procedures.

Subject:	Application Tiers
Posted by:	Anonymous (not signed in)
Posted on:	Monday, January 29, 2007 at 3:30 PM
Message:	I'm an applications developer, and I would agree that stored procedures should be used 99% of the time, and I'll go further by stating that they need to be good stored procedures. By good I mean that the proc should follow best practices, such as not using "SELECT *", turning off counts if not needed, and referenced columns having covering indexes. Being good also means that a procedure is maintainable. Most shops have coding guidelines for application code to help ensure a maintainable code base, but rarely do shops have style guidelines for T-SQL. Right now I'm looking at a 175 line stored procedures with 70 more lines of commented out lines (the only "documentation"), and inconsistent indention. Not maintainable by my standards. While using stored procedures is a Good Thing, I have seen it taken to the ridiculous extreme of putting all the business logic in the stored procedures. To say the least, T-SQL is not well suited to implementing complex logic. BTW I disagree that the multi-tier approach is overly complicated for the majority of systems. Any seasoned application developer has seen at least a dozen "quick-and-dirty" or "throw-away" applications that have been hodge-podged into mission-critical software.

Subject:	To go Stored Procedures
Posted by:	sonnysingh (view profile)
Posted on:	Friday, February 16, 2007 at 2:12 AM
Message:	It is definitely good piece of and outstanding information which vital for both developers and DBAs .

Subject:	Stored Procedures as API
Posted by:	GSquared (view profile)
Posted on:	Thursday, February 22, 2007 at 3:55 PM
Message:	One of the advantages that the comments debate misses out on is that the stored procedures are, as the article points out, an API for the database. Here's an example of where it matters: In the CRM application I built/maintain/etc., we have two completely independent front end applications, using completely different technologies. When a new feature is needed for one or the other, table changes often have to happen. I am the one who will be making the table changes. I know exactly what will be changed. I know what won't be changed. I am there when the changes are made. This means, in the database, I can confirm that every stored procedure will still perform exactly as it has before, in terms of input parameters required, output parameters and datasets provided, etc. The table changes can be easily incorporated in the stored procedures by the person who understands them the best. This results in much faster, much less buggy development. The front end developers don't need to change their data access layer. Don't need to change any code at all, unless it is new code to take advantage specifically of the changes in the database. This means, for them, old pages and forms don't suddenly break. It also means they have a consistent means of accessing the data. The advantage is not in terms of "this runs faster" or "that has more security" or "the other thing is more scalable". It's in terms of development speed and reliability. The person most familiar with the database is the one who controls access to the database by programming the stored procedures. At the company I work for, we've tried various different methods of data access. Our web developers used to include a guy who had almost fanatical views that the database should consist solely of tables, and everything beyond data persistence should be handled by the data access layer. Turned out his method involved a lot of people who knew next to nothing about the database scheme were having to write code that was very, very dependent on that exact scheme. These were not inexperienced or untrained or inexpert coders. They were very good at what they do. They just weren't as familiar with the database as the guy who built it. That makes total sense from any perspective. By leveraging the knowledge and expertise of the people who build and maintain the database, we get much more rapid, much less error-prone development. More code faster, and it works better. It's really, really hard to argue with that from my perspective. Beyond that, the arguments over whether data is more "business rule" friendly if it's written in C# or T-SQL seem really, really silly to me. I have yet to meet a manager who could read either language with any proficiency. My data dictionaries are very English-language friendly, and easy to maintain. In what way is a good data dictionary and database diagram any more difficult for business-rule experts to deal with than a document written up from C# (or any other OOP language)? Unless the manager reads one or the other programming language, both have to be translated into plain English and flow-charts before they are useful to anyone who isn't a developer.

Subject:	Use of Stored Procs
Posted by:	Anonymous (not signed in)
Posted on:	Thursday, February 28, 2008 at 10:50 PM
Message:	Most of the articles that talk about the advantages of using Stored Procs normally ignore the fact that a purely SP-based approach makes your application database vendor dependent.