image I listened to the panel discussion on the pros and cons of stored procedures from the currently ongoing TechEd09 today. It was not what I hoped for, the panel consisted almost exclusively of pro stored procedure people with the exception of Jeffrey Palermo who for an NHibernate guy appeared very pro stored procedure.

I was hoping for a more balanced debate. The arguments were to much focused on the real vs. perceived benefits of stored procedures in terms of performance, database coupling, vendor coupling, security etc.

The really big issues I have personally with stored procedures (sprocs from now on) were never fully brought up. Especially when you compare sprocs and a manually coded DAL (which I find is the most common) with NHibernate.

Code duplication
In my experience systems which rely heavily on sprocs also show a large amount of code duplication, for example duplication of SQL queries in the same sprocs in the case of dynamic queries that filter on different columns based on input. I have seen sprocs that feature the same basic query duplicated 12 times with small variation in the where/order clause. Also duplication between different sprocs can usually be very high. And the final point is the sad fact that sprocs usually contain some business logic, logic that sometimes also exist in the application itself.

Productivity & Lines of code
This topic was also not really touched upon. Data access layers which use sprocs often feature massively more amount of code to deal with calling the sprocs and mapping them to entities. The amount of TSQL you need to write for the basic CRUD sprocs is also a huge time waster and possible maintenance nightmare.

Some of these issues could be argued that it is just incompetent programmers/DBAs and sprocs are not to blame. Maybe it is not fair to compare sprocs with an ORM like NHibernate. But I think you can compare having to write and maintain sprocs compared to letting NHibernate generate adhoc SQL. Sure I think sprocs still have their usage in specific and relatively rare scenarios but the panel discussion too often concluded on the wishy washy "it depends". Of course it depends, context is everything (as Scott Bellware always says), but that does not mean that one method shouldn't be the preferred "best practice" choice.

Sorry for the rant. Kind of frustrated with a current legacy system (which uses sprocs) :)


Ben Scheirman said...

I completely agree. It wasn't a real debate, it was a sproc love-fest.

They also left out the fact that there's no point in doing stored procs for the simple CRUD operations on tables.

James Wilson said...

Not to mention the biggest problem I have with stored procedure heavy applications.

Invariably, domain constraints / domain rules find their way into the stored procedures, instead of being up at the service layer where they belong.

Or like in most real world applications, an unholy combination of both.

Daniel Fernandes said...

In the past 6 months I was involved in redeveloping a claims handling system for an insurance firm and we were not allowed to change the legacy db.
Decision was made early to use NHibernate to provide data access for POCO that form the domain.
Now that's all well and good but in this particular situation where a database has not clearly been developed as an application database and that seems to break just about every single principle of good database design then let me tell you maybe stored procedures have a place there to actually simplify your data access.
Without use of optimization techniques we ended up in situations where loading up a single screen would issue 500 separate sql statements to the server (typical n+1 problem). The legacy frontend which was arguably very badly developed felt at times much snappier than the system built with NHibernate.
More worringly, the "domain model" ended up importing all the crap data model.
So yes for NHibernate for green field systems but for brown field one has to take decisions carefully and do a comprehensive analysis of the db schema so not to get caught up with having to patch Nhibernate down the line!

Daniel Fernandes

chomama said...

Hi, grammar nazi here. I'm pretty sure it's "stored procedure", not "store procedure". As in the procedure is stored on the server.

Torkel Ödegaard said...

@Daniel Fernandes
Yes, NHibernate is no silver bullet and comes with its own share of problems.

Sounds like you need to use NHibernate Profiler (, N+1 can really kill preformance and there are easy ways to fix that.

thanks, fixed :)

Patrik Löwendahl said...

This debate is almost over, less and less cling tightly to the stored procedures mantra since their are again and again proven wrong, and those who still are will very soon cash out their pension.

The heavy usage of SQL to create business logic is a legacy from data centric applications where the only business logic that existed was that a field could not be empty. Todays demand on richer business rules won't allow for stored procedures to be the central part.

Did they mention anything about T-SQL being a sub-optimal language for expressing business rules?

Patrik Löwendahl said...

@Daniel Fernandes

Just use views and be done with it:

Torkel Ödegaard said...

@Patrik Löwendahl
Yes, I also thought that this debate was more or less over. That is why I was so surprised by this panel discussion which was more like a "sproc love-fest" which Ben Scheirman put so eloquently :)

T-SQL is a horrible language for business rules. This problem was not properly brought up, they mentioned that sprocs should be small and not contain business logic, but the problem is that they inevitably do.

Daniel Fernandes said...

@Patrik Löwendahl

Agreed that Views can help to provide optimized access to read only data but you have to still map to the underlying tables and their (nasty) structure if you want to persist things, unless you're happy to handle updatable Views.
We had to use Views to enable relationships between entities that would otherwise be impossible with NHibernate.
At times I was reading some of the advanced features supported in Hibernate 3 that aren't yet planned for NHibernate that could come very handy for legacy databases, for instance dynamic or formula based joins.

Regarding handling N+1 fetching problems. How would somebody implement this if say I have this scenario:
Level1 entity 1-* (real world example = 8) Level2 entities.
Level2 entities 1-* (real world example = 5) Level3 entities.
And I have to load up it all for a particular use case.
Will NHibernate support joining all those tables and ending up with about 500+ columns for the resulting set ?


Daniel Fernandes said...

Daniel Fernandes

On a slight different note.
I wished that NHibernate supported mapping a single table several times without needing inheritance as this isn't needed but still providing a mechanism to recognized entities in the identity map.

For instance:
Country entity would contain an exhaustive list of attributes.
But CountryInfo wouldn't but it's ID would be recognized as the same as the equivalent Country.
This way it would be really easy to optimize data access in loading only the things needed.
This solution does come with its own limitations mind you but more power is always welcome when one deal with data access.


Anonymous said...

Look this isn't a real debate. It's a sproc hate-fest.

Ben E said...

As the old adage goes: walk a mile in another man's shoes (and you'll be a mile away when he discovers he has no shoes)...or something like that.

I've worked as a DBA, BI consultant, developer, and application architect - and I can assure you that DBAs hate developers just as much for not using stored procedures as developers hate DBAs for insisting that they do.

In my experience the problems stems from not understanding the difference between a true object-based system and a predominantly data-driven system. In the former the database is just a state persistence layer for objects, in the latter it contains the raison d'etre of the system.

For example: most e-commerce stores are data-driven systems. An online bookstore does not create a 'book' object for every instance of a physical book it stores (unless it wants to be very unscaleable). The book does not change state or do anything. One could go so far as to stay that they system does not have state at all.

On the other hand, game software is (usually) truly object-oriented. An object is created, changes state, and may be destroyed. If it continues to exist at the point that the player pauses or saves the game, then the object is serialised into a persistence layer (text file, database, etc) and then deserialised when the state of the game needs to be restored.

The point of this is: if you are developing a predominantly data-driven system then it will/should be distributed. This implies that it should be service oriented, which mandates that services should be isolated and operate through interfaces. It follows that you should be using stored procedures as they provide encapsulation at the data access layer. If you don't think this is important then try running 'DELETE FROM dbo.myTable' or similar on your database.

My suggestion would be that you design the application according to the domain model and then talk to your DBA about the best way to manage persistence. The idea (which seems to have gained undue currency of late) that one can design a system along completely object-oriented/domain-driven lines is just plain nonsense perpetuated by insular hubris.

Robz said...

The one thing we have been able to get everyone to admit is that Sprocs can be less secure and obscure than table and view security access. If you grant select to a table, you know exactly what the permissions are. If you grant execute to a sproc - someone now has free reign to do anything (select, update, insert, delete) within the confines of what that sproc does. Of course naming is a way to help understand what a sproc does (i.e. usp_GetTrades_SELECT), but it can get out of sync with what it actually does when you execute the sproc. And if somehow over time that select sproc gets changed to make updates and someone executes it in production thinking it is a select and causes detrimental issues, Ouch! And that was a _SELECT sproc that you granted execute to. It's only supposed to Select, not delete!

True story.

Anonymous said...

"If you grant select to a table, you know exactly what the permissions are."

Do you want to try again? What is you have been maintaining the system for 3 years and have a mix of selects (including a mix of table-level and column-level) granted to a range of roles and users including column-level, along with a mix of DENY and REVOKEs.