Stored Procedures and ORM's

Stored Procedures are often written in a dialect of SQL (T-SQL for SQL Server, PL-SQL Oracle, and so on). That's because they add extra capabilities to SQL to make it more powerful. On the other hand, you have a ORM, let say NH that generates SQL.

the SQL statements generated by the ORM doesn't have the same speed or power of writing T-SQL Stored Procedures. Here is where the dilemma enters: Do I need super fast application tied to a SQL Database vendor, hard to maintain or Do I need to be flexible because I need to target to multiple databases and I prefer cutting development time by writing HQL queries than SQL ones?

Stored Procedure are faster than SQL statements because they are pre-compiled in the Database Engine, with execution plans cached. You can't do that in NH, but you have other alternatives, like using Cache Level 1 or 2.

Also, try to do bulk operations with NH. Stored Procedures works very well in those cases. You need to consider that SP talks to the database in a deeper level.

The choice may not be that obvious because all depends of the scenario you are working on.

There are significant performance advantages to stored procedures in some circumstances. Often the queries generated by Linq and other ORMs can be inefficient, but still good enough for your purposes. Some RBDMS (such as SQL Server) will cache the execution plans for stored procedures, saving on query time. For more complex queries that you use frequently, this savings in performance can be critical.

For most normal CRUD, though, I have found that it is usually better for maintainability just to use the ORM if it is available and if its operations serve your needs. Entity Framework works quite well for me in the .NET world most of the time (in combination with Linq), and I like Propel a lot for PHP.

The main (and I'm tempted to say "only") reason you should use stored procedures is if you really need the performance.

It might seem tempting to just create "functions" in the database that do complex stuff quickly. But it can quickly spiral out of control.

I've worked with applications that encapsulate so much business logic in SQL, that it becomes virtually impossible to refactor anything. Literally hundreds of stored procedures that are black boxes for devs working with the ORM.

Such applications become brittle, hard to debug and hard to understand. By allowing business logic to live in stored procedures you are allowing SQL developers to make design choices that they shouldn't be making, in a tool that is much harder to work in, log and debug than an ORM. I've seen stored procedures that handle payment processing. Truly core stuff. Stuff that becomes so central to an application that nobody dares to touch it, all because some guy with good SQL skills made a script 5 years to fix something quickly, it was never migrated to the ORM and eventually grew into an unmanageable monster, full of tangled logic nobody understands. Devs end up having to blindly trust whatever it's doing. And what's worse, it's almost always outside test coverage, so you may break everything when you deploy, even if your tests pass with mocked data, but some ancient stored procedure suddenly starts acting up.

Abusing stored procedures is one of the worst forms of technical debt you can accumulate. The database, which is the persistence layer, should not be used for business logic. You should keep that distinction as strict as you can.

Of course, there will be cases where an ORM will have horrible performance or simply won't support a feature you need from SQL. If doing things in raw SQL is truly inevitable, only then should you consider stored procedures.

I've seen Stored Procedure Hell. You don't want that.