Are stored procedures obsolete?

Question

Duplicate: http://stackoverflow.com/questions/216569/are-the-days-of-the-stored-procedure-numbered

---

Background

We have a consultant who develops web applications using PHP and Ruby on Rails (RoR) who is responsible for designing/maintaining/developing a small web application for our company. I needed to build a data-feed from his MySQL repository to SQL Server and noted that the queries he was using were imperfect and creating a production issue with unique ids being used more than once.

The current process involved creating a file from PHP using a query. The PHP grabbed the dataset and then parsed the data according to some business rules.

I'm converting the process to SSIS and have a single query that does what his original query and his PHP script did, but does it correctly in a single pass.

In the ensuing conversation, I realized something: He had no idea that stored procedures even existed, or (consequently) knowledge of their use!

---

Three questions (please link if any have been answered):

1. In your opinion, do stored procedures produce code that is easier to maintain or extend, or do ORMs?
2. Is not knowing Stored Procedures a deal breaker? Should programmers that are writing data access code (or more generally, web applications) know how to write stored procedures?
3. For the 'older' crowd that use Stored Procedures and have recently jumped on the ORM Bandwagon: How painful was the transition, and should older programmers learn the fundamentals of ORMs?

---

Answer 1

I'm with you. I consider new technology intended to supersede stored procedures as solutions looking for a problem. (They can be quite useful for other purposes, however.)

If your institutional knowledge adequately covers DBMS abstractions based on existing technology, stuff like ORM and LINQ increases your technodebt if you don't use them for anything else.

One factor that can influence this decision is where your organization places responsiblity for database access code - is it procedural code developers who may not be skilled at SQL optimization, or is it dbas and other more focused people who are tasked with making multiple applications play well together (and partitioning the database more thoroughly?)

Answer 2

I first learned about data access using ORMs, because at the time, I had a deadline to meet, and it was a non-extendable deadline. Using NHibernate, I was able to get away not writing any data access code whatsoever. I simply used the CRUD actions provided by NHibernate.

The project shipped very quickly (it was a co-op, for what it's worth). After that, I took a job writing web-applications with Perl where ORMs weren't possible, so I began to learn Stored Procedures. I learned them rather well, and now can easily transition between both. In my current job, we actually have three different ways of getting to data (one of which is an ORM, one that isn't, and a third... well, you don't want to know the third), and I find that I'm able to more easily debug what happens in a Stored Procedure (or Parametrized SQL), and I don't have to worry about the dependency on an ORM.

It is slower than using an ORM from a development stand point -- but you only develop something 'once', you use it and debug it forever. ORMs also have a mild performance penalty -- another reason to use Stored Procedures or Parametrized SQL.

In the end,it is also based on your business requirements. In your case, Speed might be an issue, so Stored Procedures are the way to go. In a simple CRUD web-application that isn't constrained by speed, an ORM is the way to go, and you can always use SPs for the stuff that's speed constrained.

In the end, a developer should be able to switch between the two easily. They should not advocate one over the other all the time. Different situations call for different approaches.

Answer 3

Like anything stored procedures are very useful and powerful if used in the proper context. But I am always open to new tools that make my life easier later on.

Answer 4

In my mind it's a trade-off, code seems to be more maintainable with debugging tools etc, but SPROCs can be faster.

Answer 5

Stored procedures make life difficult as the project grows:

An ORM can track "dirty" fields and create minimal updates on the fly to write-back the record, er, row, er object updates. With the s/p only methodology, you are stuck with the choice of either having a vast multitude of special purpose update procs, or having to make sure you reread all columns from a row and merge the intended updates before calling a general purpose proc which updates all columns in a row. Bluck!

I first encountered NHibernate a few years ago, and did not much care for it at the time, but have come to realize in a deeply personal way what a nightmare the alternative is :-)

Answer 6

Root cause might be that MySQL did not support stored procedures until version 5 (current version), therefore your programmer was simply never exposed to them. I always found this to be the single most annoying (lack of) feature in MySQL.

Frankly, I think you're shooting yourself in the foot if you have SQL statements embedded into your compiled code. It makes refactoring your database next to impossible. Stored procedures allow for good encapsulation of the database tables, protecting your object code from changes.

I only know SQL Server & MySQL, so I can't speak for any other RDBMS. In the SQL world, stored procedures are compiled and generally execute faster than inline scripts. Plus, stored procedures help you avoid SQL injection attacks (though they're not absolutely immune).

Long live stored procedures! (unless something better comes along :-).

Answer 7

Is using both not feasible? Be pragmatic about the tools you use. I use NHibernate for the majority of my data access (simple CRUD) but you can easily populate your NHibernate objects with data from a sproc. I do this when HQL and the Criteria API are either incapable of doing the query I want or to difficult to follow.

Answer 8

I actually feel the same way. I think stored procedures are more difficult to maintain. ORM is pretty quick and painless. There are some places they're great, like some large 2000 line database operation. Most CRUD operations are pretty much time wasted. All programmers should know how to write one, because it's really not much more than just saving a query into the database.

Answer 9

In my opinion stored procedures are not dead. Not dead at all. Now the thing is, that there is no perfect solution. I use ORM mapping, Stored procedures and direct SQL queries in my projects because they all have their good side and bad side.

ORM is great for getting and managing most of the data you need to access in a web application, when you need to query for a complex piece of data, say that you use Hibernate and you need to get data that joins 5 tables, requires line and group aggregations, e.g.

select ... from A join B join C where A.x is not null having B.id = min(B.id) group by B.id

This becomes a mess using Criteria or just mapping on Hibernate but quite simple using a Hibernate JDBCRowMapper.

Finally about Stored procedures, say that you need to do migrations or trigger some logging once some database table is changed then a stored procedure can keep all this DB processing in the DB server itself making your app code "cleaner" and you only need to check if the transaction is sucessful.

So my opinion is that you should try to learn all the options, SQL, SP and ORM because they will make you a better Database developer.

Finally in DBs like oracle and postgres you can even code your SPs in Java or other languages tha PL/SQL.

Answer 10

The answer varies according to your requirements.

There are two different approaches to this issue. Both are equally valid.

1. The database is a backing store for object persistence.

   In this case, an ORM can handle all your access needs.

2. The database exists independently of the program and is expected to either last longer than the program, or be used by multiple programs which cannot use the same access code (because they're coded in different languages, for example).

   In this case, it is good to have the API defined as stored procedures, so that API changes can be made in one place. Otherwise, each program that accesses the database has to be modified to reimplement the new API requirement.

Answer 11

I posted a similar question a month ago. There were some pretty good responses there also.

Answer 12

I do not think that stored procedures are by any means dead -- any more than one would think that databases are dead because google has a scalable key-value lookup solution. Use the right tool for the right job!

There are some good answers to essentially this same question here: http://stackoverflow.com/questions/216569/are-the-days-of-the-stored-procedure-numbered