Stored procedures or OR mappers?

Question

Which is better? Or use and OR mapper with SP's? If you have a system with SP's already, is an OR mapper worth it?

Answer 1

I personally have found that SP's tend to be faster performance wise, at least for the large data items that I execute on a regular basis. But I know many people that swear by OR tools and wouldn't do ANYTHING else.

Answer 2

I like ORM's because you don't have to reinvent the wheel. That being said, it completely depends on your application needs, development style and that of the team.

This question has already been covered http://stackoverflow.com/questions/50346/why-is-parameterized-sql-generated-by-nhibernate-just-as-fast-as-a-stored-proce

Answer 3

Stored procedures are better, in my view, because they can have an independent security configuration from the underlying tables.

This means you can allow specific operations without out allowing writes/reads to specific tables. It also limits the damage that people can do if they discover a SQL injection exploit.

Answer 4

I would argue that using an OR mapper will increase readability and maintainability of your applications source code, while using SP will increase the performance of the application.

Answer 5

Stored procedures hands down. OR Mappers are language specific, and often add graphic slowdowns.

Stored procedures means you're not limited by the language interface, and you can merely tack on new interfaces to the database in forwards compatible ways.

My personal opinion of OR Mappers is their existence highlights a design flaw in the popular structure of databases. Database developers should realize the tasks people are trying to achieve with complicated OR-Mappers and create server-side utilities that assist in performing this task.

OR Mappers also are epic targets of the "leaky abstraction" syndrome ( Joel On Software: Leaky Abstractions )

Where its quite easy to find things it just cant handle because of the abstraction layer not being psychic.

Answer 6

They are not actually mutually exclusive, though to your point they usually are so.

The advantage of using Object Relational mapping is that you can swap out data sources. Not only database structure, but you could use any data source. With advent web services / Service-oriented architecture / ESB's, in a larger corporation, it would be wise to consider having a higher level separation of concerns than what you could get in stored procedures. However, in smaller companies and in application that will never use a different data source, then SP's can fit the bill fine. And one last point, it is not necessary to use an OR mapper to get the abstraction. My former team had great success by simply using an adapter model using Spring.NET to plug-in the data source.

Answer 7

Definitely ORMs. More flexible, more portable (generally they tend to have portability built in). In case of slowness you may want to use caching or hand-tuned SQL in hot spots.

Generally stored procedures have several problems with maintainability.

• separate from application (so many changes have now to be made in two places)
• generally harder to change
• harder to put under version control
• harder to make sure they're updated (deployment issues)
• portability (already mentioned)

Answer 8

This has been discussed at length on previous questions.

http://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code#15153

Answer 9

@ Kent Fredrick

My personal opinion of OR Mappers is their existence highlights a design flaw in the popular structure of databases"

I think you're talking about the difference between the relational model and object-oriented model. This is actually why we need ORMs, but the implementations of these models were done on purpose - it is not a design flow - it is just how things turned out to be historically.

Answer 10

Use stored procedures where you have identified a performance bottleneck. if you haven't identified a bottleneck, what are you doing with premature optimisation?
Use stored procedures where you are concerned about security access to a particular table.
Use stored procs when you have a SQL wizard who is prepared to sit and write complex queries that join together loads of tables in a legacy database- to do the things that are hard in an OR mapper.

Use the OR mapper for the other (at least) 80% of your database: where the selects and updates are so routine as to make access through stored procedures alone a pointless exercise in manual coding, and where updates are so infrequent that there is no performance cost. Use an OR mapper to automate the easy stuff.

Most OR mappers can talk to stored procs for the rest.

You should not use stored procs assuming that they're faster than a sql statement in a string, this is not necessarily the case in the last few versions of MS SQL server.

You do not need to use stored procs to thwart SQL injection attacks, there are other ways to do make sure that your query parameters are strongly typed and not just string-concatenated.

You don't need to use an OR mapper to get a POCO domain model, but it does help.

Answer 11

If you already have a data API that's exposed as sprocs, you'd need to justify a major architectural overhaul to go to ORM.

For a green-fields build, I'd evaluate several things:

1. If there's a dedicated DBA on the team, I'd lean to sprocs
2. If there's more than one application touching the same DB I'd lean to sprocs
3. If there's no possibility of database migration ever, I'd lean to sprocs
4. If I'm trying to implement MVCC in the DB, I'd lean to sprocs
5. If I'm deploying this as a product with potentially multiple backend dbs (MySql, MSSql, Oracle), I'd lean to ORM
6. If I'm on a tight deadline, I'd lean to ORM, since it's a faster way to create my domain model and keep it in sync with the data model (with appropriate tooling).
7. If I'm exposing the same domain model in multiple ways (web app, web service, RIA client), I'll lean to ORM as then data model is then hidden behind my ORM facade, making a robust domain model is more valuable to me.

I think performance is a bit of a red herring; hibernate seems to perform nearly as well or better than hand-coded SQL (due to it's caching tiers), and it's easy to write a bad query in your sproc either way.

The most important criteria are probably the team's skillset and long-term database portability needs.

Answer 12

Well the SP's are already there. It doesn't make sense to can them really. I guess does it make sense to use a mapper with SP's?

Answer 13

"I'm trying to drive in a nail. Should I use the heel of my shoe or a glass bottle?"

Both Stored Procedures and ORMs are difficult and annoying to use for a developer (though not necessarily for a DBA or architect, respectively), because they incur a start-up cost and higher maintenance cost that doesn't guarantee a pay-off.

Both will pay off well if the requirements aren't expected to change much over the lifespan of the system, but they will get in your way if you're building the system to discover the requirements in the first place.

Straight-coded SQL or quasi-ORM like LINQ and ActiveRecord is better for build-to-discover projects (which happen in the enterprise a lot more than the PR wants you to think).

Stored Procedures are better in a language-agnostic environment, or where fine-grained control over permissions is required. They're also better if your DBA has a better grasp of the requirements than your programmers.

Full-blown ORMs are better if you do Big Design Up Front, use lots of UML, want to abstract the database back-end, and your architect has a better grasp of the requirements than either your DBA or programmers.

And then there's option #4: Use all of them. A whole system is not usually just one program, and while many programs may talk to the same database, they could each use whatever method is appropriate both for the program's specific task, and for its level of maturity. That is: you start with straight-coded SQL or LINQ, then mature the program by refactoring in ORM and Stored Procedures where you see they make sense.

Answer 14

At my work, we mostly do line of business apps - contract work.

For this type of business, I'm a huge fan of ORM. About four years ago (when the ORM tools were less mature) we studied up on CSLA and rolled our own simplified ORM tool that we use in most of our applications,including some enterprise-class systems that have 100+ tables.

We estimate that this approach (which of course includes a lot of code generation) creates a time savings of up to 30% in our projects. Seriously, it's rediculous.

There is a small performance trade-off, but it's insubstantial as long as you have a decent understanding of software development. There are always exceptions that require flexibility.

For instance, extremely data-intensive batch operations should still be handled in specialized sprocs if possible. You probably don't want to send 100,000 huge records over the wire if you could do it in a sproc right on the database.

This is the type of problem that newbie devs run into whether they're using ORM or not. They just have to see the results and if they're competent, they will get it.

What we've seen in our web apps is that usually the most difficult to solve performance bottlenecks are no longer database-related even with ORM. Rather, tey're on the front-end (browser) due to bandwidth, AJAX overhead, etc. Even mid-range database servers are incredibly powerful these days.

Of course, other shops who work on much larger high-demand systems may have different experiences there. :)

Answer 15

There is nothing good to be said about stored procedures. There were a necessity 10 years ago but every single benefit of using sprocs is no longer valid. The two most common arguments are regarding security and performance. The "sending stuff over the wire" crap doesn't hold either, I can certainly create a query dynamically to do everything on the server too. One thing the sproc proponents won't tell you is that it makes updates impossible if you are using column conflict resolution on a merge publication. Only DBAs who think they are the database overlord insist on sprocs because it makes their job look more impressive than it really is.