Shout!

Home

A default architecture – without stored Procedures

Friday, June 27, 2008

I'm aware that this is a very heated debate, I'm also aware that however I form this post there will always be opinions that are opposite from mine. But never the less I would like to write a little about why I put stored procedures on the substitutions bench and only call them in where something can't be solved in any other way.

To be clear; My position is not that stored procedures are "evil" or have no use at all, my position is that stored procedures usually forces me to abstain other technologies and techniques I value more. Furthermore, there are will always be scenarios where stored procedures is the preferred solution, I just don't believe in making them my default choice.

So let's get to it, first my main pain points:

Pain point #1: Stored Procedures lack proper version management

Sprocs don't version easily. There are a lot of challenges with version management and sprocs the database can only have exactly one version of the sproc at any given time. If I want to support two versions I need two separate sprocs. This might seem trivial but when you build multi-tenant systems where versions aren't just about bug fixes and new features, but slight nuances in the way they should behave for different tenants (customer Acme's business differs from customer Mechanics business), it becomes a bit more messy.

Additionally, for a long long time there hasn't been a good source control story around sprocs. That have change now with the new database role in Visual Studio 2008 Team System and the database project type (which rocks btw). Even though I like the database role I still feel that the story around code versions and version control isn't strong enough and therefor pains me.

Pain point #2: Stored procedures forces me to maintain two code bases

Having code in the database forces me to maintain two different code bases and maintain two different skill sets. It also moves me out of my environment with the tools I'm used to have like ReSharper, a proper debugging environment and all the other nice things I use when writing C# code. Maintaining two code bases also makes me less flexible, less agile if you will, and I just don't like it.

Pain point #3: T-SQL Doesn't express business logic well

I can't say I'm an expert on T-SQL, but I'm fairly good at it. My view on T-SQL is that it's really good at querying but for expressing business logic it lacks a lot of constructs that I get from a good solid OO language. I can't use well known patterns like the strategy pattern to vary calculations or other parts of the business logic, it's much harder to take other factors then pure data from the database in account when working out the logic. And frankly it is ugly and have very poor readability. Which in terms means that it will be much harder to maintain.

Pain point #4: Stored procedures are hard to test

It's impossible to test sprocs in isolation. It's also impossible to test sprocs without firing up a database. Testing with a database takes a lot of time and a lot of effort. Microsoft has, again, improved this area a bit with their Database role in Visual Studio 2008, but it's not really enough for me. My testing requirements go past the functionality I get from Visual Studio. Sure, for testing database interaction it works like a charm but for proper unit testing it just doesn't cut it. Also since sprocs often look like transaction scripts, they are usually these big things and it's very hard to test parts of the sproc in isolation, you have to test everything or nothing.

Pain point #5: Stored procedures forces me to write T-SQL for trivial tasks

Why should I have to write trivial T-SQL queries myself when I can have a tool do it for me? No doubt that if I'm really, really good at writing queries for my scenario and stick them into a sproc that I might get a little better performance then something auto-generated and generic. But is it worth the time you have to put in? I don't think it is. If I got a serious performance problem I will address it when it arises, not before. Premature optimization being the root to all evil and all that. I really believe that the gain I get in productivity is worth the loss in performance (if any; putting a query in a sproc doesn't guarantee better performance). It might even be that the tool generates better queries then those that I can write myself (and that probably goes for some of your queries as well ;)

Edit @ 16:13, forgot one :)

Pain point #6: Stored procedures are static

Stored procedures are not flexible around parameters and for every variation of parameters, joins, orders or other differences I might want; I need a separate sproc to support it. This means that if I want a query that is slightly different I need to copy and paste the sproc, change what differs and now maintain two similar sprocs, remember to change them both. Again, maintenance suffers.

So what do I suggest instead?

My last pain point kind of reveals where I'm going with this. I prefer dynamic SQL generated by a tool. I prefer it because it lacks the above pain points and makes me much more productive.

That's why my default architecture always starts out with an ORM and dynamic SQL if I need to talk to a database, it will generate all the trivial SQL, it will enable me to test in isolation, there will only be one code base to maintain, versioning works a lot better and all my favorite tools will help me write good and solid code.

Does that mean I don't use sprocs at all? Certainly not, but dynamic SQL is my default and I call sprocs in when I think I need them, which these days are very very rare.

The coming weeks I'll try to explain some of the misunderstandings around dynamic SQL in comparison to sprocs and also talk a little bit on particular solutions and how I go about them.

More information on ...

... visual studio team system database role: http://msdn.microsoft.com/en-us/vsts2008/products/bb933747.aspx
... ReSharper: http://www.jetbrains.com
... Object Relational Mapping: http://en.wikipedia.org/wiki/Object-relational_mapping

... Transaction Script: http://www.martinfowler.com/eaaCatalog/transactionScript.html

Comments

	6/27/2008 3:31:00 PM http://www.johannormen.com/blog - Johan Normén

Can't agree more :)

	6/27/2008 3:44:00 PM http://blogs.msdn.com/johanl - Johan Lindfors

Nice points Patrik! Once again a very interesting article and something that's worth thinking about!

	6/27/2008 6:19:00 PM http://www.rogeralsing.com - Roger Alsing

May I add one? :-) Stored procedure based application will often have worse performance than apps based on dynamic SQL. Simply becaue sprocs as you say are hard to maintain. Developers tend to avoid making new usecase specific versions of them. Which results in a few generic stored procs that reads a bit of everything, even fields that you do not need. Eg, get all records from the order table by customerid and read all the fields. So if your app only needs a few of those orders, it is not uncommon to see sproc based apps perform the filtering on the app server instead of inside the database. just to avoid adding new sprocs.. With dynamic sql and nice query support like Linq you can easily express your intent in your code but execute the filtering inside the DB.

	6/28/2008 12:42:00 PM http://www.lowendahl.net - Patrik Löwendahl

Add as many as you like ;) It's a good point; since maintanence is harder then with .net code you avoid adding more sprocs. You don't want to add more pain to your suffering...

	6/28/2008 6:44:00 PM http://tawani.blogspot.com - bennyb

Dude, couldn't agree more. I use my custom light modified ORM and boy, I haven't been happier ... no more useless, lousy, untestable stored procedures to deal with.

	6/29/2008 8:21:00 AM http://www.wictorwilen.se - Wictor

Good points here. Another one that I think should be on the list is that it's far more easier to read and understand the code if you avoid having the code (read TSQL) elsewhere.

	6/30/2008 4:27:00 PM - Donald

why is stored procedures harder to maintain / read / understand? sounds like you guys lack som experience and knowledge in this area. sp's are as easy to source control as any other textbased files you have in your sourcecode projects and have been so for quite some time now (and yes even before vs.net was introduced)

	6/30/2008 11:15:00 PM http://www.lowendahl.net - Patrik Löwendahl

I wouldn't say that I lack experience, I have had to much experience, that is the problem. My background started out in building client / server solutions based in SQL Server 6.5 and VB5. Then I thought they where a great idea. But after a couple of projects in .NET with C# and VS.NET I never wanted to touch SQL for my business code. And no, you can never get a language expert to agree that T-SQL can be as easy as maintain and as expressive as C#, Java or Ruby. It lacks fundamental syntax elements and programming constructs. And to claim it's easy to maintain, I'm not sure what your definition of maintencen is. But for me one of the important parts is versioning and regression testing. Two things that sprocs don't do well. And sure, you can pull out a sproc inot a .sql file, and manually check it into any source control for a couple of files it can be good enough. But for any serious development with a large amount of files etc, you will never be as productive as with an integrated experience, nor will you get the same support from the source control tool.

	7/3/2008 9:36:00 PM http://www.facility9.com - Jeremiah

I'm going to take a stab and responding to each of your points because I think it's good to get a dialog going on these kinds of things. Pain Point the First: Version Management I'm not sure what you mean by this. It's fairly trivial to version control SQL files, however I'm guessing that you aren't having any difficulty with adding plain text files to version control. So, I'm going to make a guess and say that this has something to do with quickly determining which version of development code is in the database. This can be somewhat tricky. It becomes necessary to use some kind of build/migration system similar to the one used in Ruby on Rails where you have a schema_info table with a version column that is incremented or decremented by an automated tool. At this point, a high level of DBA discipline is required to prevent changes from being made in production that are not present in source control and vice versa. Pain Point the Second: Two Code Bases Again, I'm not sure what is meant by this. You're absolutely right, maintaining stored procedures does require two skill sets. In an ideal world there would be a distinct separation of skills between database and application developers. While basic to intermediate SQL can be handled by most developers, venturing into high performance/advanced SQL requires a distinct skill set that requires years of experience and conscious practice. Pain Point the Third: Business Logic I've said this a large number of times over my career as both an application developer and database developer: business logic does not belong in the database. By default T-SQL will compile and store the first execution path it encounters in a stored proc. Let's say you have a procedure with two branches, one performs a simple insert and the other performs a complex operation that can benefit from a compiled execution plan. If the simple insert statement branch is the first branch to be executed, it will be compiled and the other, complex, path with be performed as an ad hoc query unless the stored proc is ALTERed and the second execution path is executed. If you need complex logic and you have SQL 2005, you can use CLR stored procedures. Pain Point the Fourth: Testing Stored Procedures While I'm definitely not an expert on the subject of testing stored procedures, I have read numerous resources on the subject of testing stored procedures. Adam Machanic devotes some time to it in Expert SQL Server Programming. I'll leave this topic to the experts. Pain Point the Fifth: Writing Trivial SQL When you're operating, or attempting to operate, a high performance, high access database, there are no trivial data access calls. Granted, for the majority of data access scenarios (SELECT a, b, c FROM xyz WHERE param = @param), a stored procedure could be described as development overhead. This is a point that we agree on. However, I see the point that many DBAs have - if they want to change the underlying schema for performance/storage considerations, they should be able to do so as long as they provide developers with the same set of outputs as before, given the same inputs of course. Pain Point the Sixth: Stored Procedures aren't Dynamic Nor should they ever be. The point of a stored procedure is to access data as efficiently as possible. In the event that you have multiple potential join combinations, you will need multiple stored procedures. Not to sound insulting, but the demand for multiple potential join combinations sounds like, to me, a poorly planned data access scenario. There are ways to dynamically generate WHERE and ORDER BY clauses using T-SQL and parameterized procedures/queries. Ultimately, of course, the purpose of a stored procedure is to increase performance, granularity of data access, and provide an additional layer of security on top of the data. A data access solution based around stored procedures isn't for every one, but it's always good to know the re

	7/6/2008 10:25:00 PM - Joakim

Could you explain how you handle ad-hoc reports, often my client would like underlying data in Excel, so they can do there own reports or analyze the data. If you hock up your .NET assemblies in office, how would you handle changes within the database?

	7/7/2008 3:12:00 PM http://www.lowendahl.net - Patrik Löwendahl

Could you explain how you handle ad-hoc reports, often my client would like underlying data in Excel, so they can do there own reports or analyze the data. If you hock up your .NET assemblies in office, how would you handle changes within the database? -- I wouldn't. Reporting is something different and neither sprocs nor classes are ideal for reporting. For reporting I like to use a separate database or a warehouse (which takes a very long time to build though) and hook up a reporting service ontop of it. Like Crystal or SSRS. /if/ there is requirements for reporting on live data (when digging into it, it never is that important) my preference is to create views to report off. Views are perfect for querying and it's even possible to index them speratly from the tables (which you can't do with a sproc) for better performance. That way the view will become my "contract" and as long as that doesn't change the client won't have a problem.

	7/11/2008 6:01:00 PM http://sqlblog.com - Adam Machanic

A couple of years ago I fell exactly in the opposite camp: http://www.simple-talk.com/sql/t-sql-programming/to-sp-or-not-to-sp-in-sql-server-an-argument-for-stored-procedures/ But now I realize that the best solution is taking a middle path. The primary issues I still have with ORM-based solutions are security and, in certain advanced cases, performance. So my current feeling is that one should take advantage of ORM where appropriate (almost everywhere), and stored procedures where appropriate (secure areas, extremely complex queries that need to be specially tuned). To ban either methodology from your toolset would be to do yourself--and your users--quite a disservice.

	7/12/2008 2:50:00 PM http://www.lowendahl.net - Patrik Löwendahl

Adam, I agree with your conclusions. That's why I'm talking about a "default architecture", not the "absolute architecture". If and when sprocs do bring features to the table that makes them the best solution for the scenario. By all means use them. But as the default option? Never. Regarding security, I'll be posting seperate on that after my vaccation (well it's actually already done if you want to read it I can mail it to you but I'm waiting for some feedback from a couple of SQL mvps)

	7/28/2008 2:42:00 PM - Jonas

Your arguments shows some great lack of understanding of what a stored procedure is and what it gives to you. You probably never ever worked on anything big or your just complete ignorant. I'll start with meeting your replying to you comments. (I'm more of an Oracle guy so some of my argument may not apply to SQL-server) 1. A stored procedure can be representated by a text-file. So in other words you are saying it's hard to source control text-files. Man! what tools do you use. How do you keep control of your C#-code? Don't you see it's the same problem. How do you build that code? How would you know what versions of your classes you put in to your .exe? (If you just look at the .exe file) The answer is that you build in a contolled way. Same with SP:s, Same in JAVA. Same with all code The real problem my friend is in how you source control your DDL:s of your persistent objects. All code (SP, .NET, JAVA etc) can just be dropped and re-release everytime. Persistent objects needs keep the data. You can't just drop a table and recreate it because you whant a new column. As you understand we need to make sure all the data stays aswell. But this is a different problem and has nothing which will occur as sonn as you have any persistent objects anyware in your code. 2. This is not a problem with SP:s. It's a problem with your set of skills. How can using 2 different technologies be less flexible? Just use the one that does the job best and makes sure you layer your application. 3. You must be kiding. SP:s are desinged for logic on data. And that's what we are talking about here or? With this kind of reasoning I guess you even check all constraints in C#. SP:s automagically handle the isolation level that you need. And this with no impact on database scalibilty. Try doing that with dynamic SQL. (Yes can be done but it's a headache and scaliblity will go down, most people just ignore this issue). Throw a connection pool in to your application and no it can't be done anymore. 4. How can they be hard to test? It's code my friend. You call sometingh with some in-parameters and something comes out. You check this output againts something expected. This is just like anycode. The rest is just a matter of test-tool. In facts it's really really easy to test why: 1. Modern databases have a build in profiler => this makes it easy to see exacly how many milliseconds and how many times each line of any SP has been executed. 2. Today SP:s can be debugged just like anyother code. 3. You can easily (inside the database) record all SQL calls from your application and then later just replay them. 4. The nicely connects with the database wait interface. 5. Ever hear of CRUD SP:s. These are the trivial ones you talk about. And yes there has been tools to generate them for at least 10 years. 6. They are not static at all. They are just as static as you choose to make them But there are som great advantages to make them static and thats why so many people do, Those advantages are: 1. All queries are pre-parse even before the first time they are executes. In a busy DB-application the thoughets job is not to actually execute the SQL But rather to parse all the SQL. (Check Syntax, Grants, Synonyms, Optimize) So imagine what would happen with Dynamic SQL. It just kills scalibility. 2. All objects in the database have something called strong dependecy check. This doesn't exist in your .NET world. And this is a huge help if you need to change something. You know like maintain you application. An example. If you drop a column on a table then all SP:s that refer to that column will automagically become invalid This happens instantily and not at run-time as with normal code (When the end user runs some code that only

Comment

Title:
Your name:
Your url:
Text:
Remember me
Please enter the text from the image: