Stored Procedures and ORM's

Question

What's the purpose of stored procedures compared to the use of an ORM (nHibernate, EF, etc) to handle some CRUD operations? To call the stored procedure, we're just passing a few parameters and with an ORM we send the entire SQL query, but is it just a matter of performance and security or are there more advantages?

I'm asking this because I've never used stored procedures (I just write all SQL statements with an ORM and execute them), and a customer told me that I'll have to work with stored procedures in my next project, I'm trying to figure out when to use them.

Marenmarena

Answer 1

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.

Hairbrush

In most scenarios the SQL generated by a decent ORM is perfect, it's just when things get complex that they break down. – Kinesics

@Paul, that's what I was trying to explain. Do some not so fancy subqueries + some Formula and the thing could explode. Anyway, here is when CreateSQLQuery saves the day. – Hairbrush

Speed and power of SQL generated by an ORM is just the same as straight T-SQL. (They also get precompiled and reused in SQL Server). It is also easier and faster to write, way less risky, and way more maintainable. – Practice

Paul and Ernst - straight SQL and stored procedures have the potential to be magnitudes of orders faster than ORM generated SQL, if both are written in the best possible way. This is fact. Granted, it's a HUGE trade off between maintainability vs performance. But please never claim that the speed/power is the same :) – Hearts

95% of the time I find myself doing "select fields from Table where [filter based on properly indexed field]" in which case stored procs are preferred by devs who think that typing more code == more value added. But then procs can be very useful for the remaining cases. Notice how many posts on this topic tend to be all or nothing. – Mistrustful

ORM code is how you end up with data quality issues in the database if junior programmers are ever allowed to write database code. Stored procedures are atomic by default and are a lot harder to mess up, and provide better & better consistency guarentees the closer the DB layer is to the problem domain. – Provisional

Answer 2

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.

Megawatt

Answer 3

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.

Deoxyribose

Most RDBMSes cache execution plans for every query that fits to a particular mold, and has since over a decade ago. That execution plans are only cached for stored procedures and not for dynamic SQL has been proven false many times, and is even stated as such in the SQL Server Help. – Rhoades

It's not just about the caching or execution plan. It's about the fact that there are tricks and techniques that can be used in a DBMS's native SQL variant to optimise queries and stored procedures by a massive amount. This type of SQL is not able to be easily written using an ORM approach. I've seen it time and time again. The bottom line is stored procedures using a DBMS's native SQL language can be orders of magnitude faster than ANY ORM generated SQL. However ORM approach is more maintainable. Most ORM devs are not experts at advanced SQL, and have no understanding/experience of this. – Hearts

It's probably worthwhile to note that properly parameterized sql queries ("prepared queries" in the docs) DO get cached and reused, according to the docs msdn.microsoft.com/en-us/library/cc293623.aspx – Mistrustful

@Hearts your comment presumes that the people who are writing the stored procedures are experts at advanced SQL. That's often not true either. Given two cases: a popular ORM that's been worked on by dozens if not hundreds of people to support thousands or hundreds of thousands of use cases vs a stored procedure that is written by one or two people, which one do you think has the most expertise applied to it? – Rhoades

Answer 4

I stumbled over this pretty old question but I am shocked that the most important benefit of Stored Procedures is not even mentioned.

Security and resource protection

Using SPs you are able to grand execution rights for that SP to a user. The user can execute the SP and only that SP. You do not even have to give the user read or write access to the tables used. The user does not even have to know the tables used.

Using ORM you have to give read or/and write access to the tables used and users. The user can read all data from all the tables you granted the rights and even can combine them in queries, if you want it or not, and also can run queries that creates heavy load on the Database server.

This is especially useful in cases where application development and database development is done by different teams and the database is used by more than one application.

Witchery

Answer 5

The primary use I find for them is to implement an abstraction layer and encapsulate query logic. In the same way that I write functions in a procedural language.

Aleman

Answer 6

As le dorfier mentions one of the the primary reasons sprocs (and/or views) should be used is to provide an abstraction layer between a database and its clients (web apps, reports, ETLs etc)

This 'DB API' can make it easier to change/refactor your database without necessarily affecting clients.

See - Why use stored procs - for a more in depth discussion

Typescript

The exact reason you put "'DB API'" in quotes is the exact reason this is bad. You're coupling domain logic with the persistence layer. So you can't use a metaphorical 'DB API' and get away with that. You are also assuming that the alternative is to put straight ORM or hardcoded SQL directly into the web apps or something close to the presentation layer. The correct alternative (whether using ORM or manual ADO.Net, etc) is to properly abstract your business or data logic into its own layer or tier. – Ocreate

Answer 7

I mainly stick to linq to sql as an ORM and i think its great, but there is still a place for stored procedures. Mainly i use the when the query i want to run is very complex, with many joins (especially outer joins, which suck in Linq), lots of aggregation in subqueries perhaps, recursive CTE's, and other similar scenarios.

For general crud though, there is no need.

Kinesics

Good point, i didn't remember the use of joins. In LINQ, using joins can really be hard. Btw, do you know if all major ORMs (EF, nHibernate) can call a stored procedure and return it to the entity associated with it? – Marenmarena

@guilhermeGeek EF4 has this functionality. EF allows you to avoid most explicit joins also provided that you have primary and foreign keys set up properly, but if you do need a join in Linq, mostly, it's just syntactically awkward. You may find you don't need to specify a join as a Join, but possibly rather as a Select or sometimes SelectMany in Linq. – Deoxyribose

@Andrew, in EF4 and L2S, having to manually write joins is pretty rare assuming the schema is correctly defined with the right keys, but when you can't change or control the DDL, or when outer joins and other more complex queries are required in abundance, then sometimes righting it in linq is not worth the effort in readability or performance, and some things are just not possible at all. – Kinesics

@guilhermeGeek, Linq to SQL maps stored procs to entities, though things can get a bit hairy when using dynamic sql inside stored procs. – Kinesics

Agreed. If you don't have solid DDL, it can be a real pain to try to use Linq. Supposedly, you can mitigate this somewhat in EF by defining the classes yourself with database mappings that are less-than-obvious, but I've not had to attempt this myself. I'm lucky: if the data model is broken, I get to fix it rather than try to work around it in code. – Deoxyribose

The fact that you see comments here like "i didn't remember joins" goes to show a HUGE disparity in the level of applications out there. The last 3-4 systems I've worked on have 100+ tables, with most queries doing advanced joins across 5-6 tables inner joins, left joins, sub queries, and other advanced SQL. If a system only requires very basic CRUD operations, then it's clearly very simple/basic and this whole issue doesn't really matter. But when you get to serious data, there is significant performance benefits in stored procs. – Hearts