Are the days of the stored procedure numbered?

Question

In Scott Hanselman's interview of the stack overflow guys, Scott and Jeff make mention that they may be seen as heretics for declaring that the days of the stored procedure are dead. Why might they say this? Is it because many of the new DAL techniques of dynamic data and Linq to SQL use T-SQL to communicate with the SQL server (SQL 2005 in their case)?

Isn't there still significant value in having the T-SQL stored and compiled on the SQL server, along with the benefits of the query plans being cached on the DB? Is it just that these benefits are outweighed by the ease of data access with the aforementioned technologies?

Answer 1

These two articles describe most of the common arguments against stored procedures.

Jeff Atwood

Frans Bouma

Answer 2

The anti-stored-proc movement has some merit. I've seen lot's of projects go wrong because they tried to put business logic inside the database. I've seen this cause big maintanance nightmares.

In a lot of places, for example when you have multiple programs that use one database, it's a good idea to have a thin abstraction layer between the database and the applications that use it. I think views and stored procedures still have a role to play here. Although we might see products like astoria (service layer for databases) take over this role in SOA architectures.

edit in response to Revolucent
I can name three reasons off the top of my head:
If your business logic get's more complicated than simple validation I found that modern OO languages are more suitable than a set-based procedural language like SQL. I've seen the addition of one employee-type cause changes in more than a hundred stored procs. A well designed business object model is more suited to keep changes like his localized.
For larger databases that are accessed by more than one application usually not every application has the same business logic. Using one set of stored procs with business logic can quicly lead to situations where changes for one application break the other applications.
You don't always want to do a database round-trip just to execute business logic. Stored proc's aren't really portable, you can only execute them in the database. I found that in situations where business logic is implemented in the database eventually you'll start duplicating it in code to minimize database round-trips this causes maintenance problems because of duplicate code. I think it's better to keep it out of the database from the beginning.

Answer 3

Well, there's always those of us who don't use .NET, and we'll probably keep using Stored Procedures. Also, I'm no expert in Linq (I've never even actually used it), but I suspect there's still a place for Stored Procedures in optimization. In many cases its much faster to run a batch on the SQL server than it is to pull back a larger tableset and process locally.

Finally, as Mendelt posted, sprocs provide a very good architecture-neutral abstraction layer over the underlying data store.

Answer 4

Stored procedures or not, your business logic needs to be stored somewhere. If you access everything from a single uniform application, then it obviously does not matter. However, if you develop something like ERP system, you might have 20 different applications written in 20 different programming languages and libraries and running on 2-3 different operating systems. If you want to maintain a consistent business logic in that kind of system, you need to keep it all in a single place. IMHO, it's easiest to do it with stored procedures, but other solutions like 3-tier application and SOAP are also viable.

All those contra-procedure arguments are given by people who have obviously never been involved in development of a large system. By large, I mean 500+ database tables and 20+ programmers involved. If you let anyone get away with dynamic scripts it becomes a maintenance nightmare in few years.

Answer 5

More than just query plans, having stored procedures compiled and stored on sql server means another layer of implementation abstraction. With technologies like LINQ, the access and munipulation of data is now strongly coupled with business logic. Stored procedures, just like methods, provide an interface to the data. How the stored procedure is implemented doesn't matter to the caller and the implementation can be changed without affecting the callers.

Answer 6

Ugh. I feel that the entire question is loaded.

Sticking business logic into a stored proc, is widely agreed as being a bad idea. I'm sure there are probably cases where it makes sense (perhaps the system is only a db, and doesn't have a gui or anything else?), but I'm not convinced I can think of any!

I think that anyone saying "stored procs are always bad" is not really understanding the (not so) subtle differences between stored procedures and inline SQL. There are benefits, but what you need to work out is whether those benefits have any real consequence to what your functional and non functional requirements are. If they don't, then do whatever is easiest.

One thing I do agree with is that it makes releasing software easier... since schemas tend not to change frequently, but code does. Often someone forgets to release a stored procedure change or trigger change.

One thing I think many people forget is that without triggers and referential integrity you open yourself up to the potential issue that someone in your support team accidentally forgets to update that other, new table or puts a typo in. Triggers, can in some cases help. (Don't forget that triggers are really stored procedures with a bit of a twist, so we ought to include these in the discussions).

I disagree with Jeff's article. His point that 'However, there's one small problem: none of these things are true in practice.' is something easy to feel is incorrect. In the average case he may be correct, but as you start pushing the limits of the technology then you start running into scenarios where the differences between the two have real impact. To mention a few: performance, expected explain plan and refactoring.

• You know what depends on your tables, so you know what to refactor. Ever tried searching through code to work out what tables your different applications depend upon? In the better dbs you can always do a 'depends' to help work out the consequences.
• The security argument is is useful, since you know that app A which comes directly to your DB (which happens in real life) is actually only accessing tables x, y and z via stored procedures x and y. Perhaps when we come back in ten years and the system is still running and you have to migrate it to the latest version or decommision it then you'll see how important this is.

One point I'd like to also rubbish is the argument that only x of our developers can debug a stored procedure. Woooah! Hang on, are you suggesting that you have developers writing SQL who aren't able to write a stored proc? That sounds really worrying to me, since they're pretty similar. What else about the database do these developers not know?

At the end of the day, keep data constraints in your database, and put your business logic somewhere else. The argument between sp's and written sql (I'm not talking about dynamic sql) has to be taken on a case by case basis.

I discussed the problems that adding .NET languages to the new SQL server would have with someone from MS and they agreed that the business logic shouldn't be in the server, and that wasn't something they were really encouraging with adding .NET in. :/

Answer 7

This is kind of a loaded question.

It boils down to the age old truth, whatever is getting built it better have some ROI.

Writing stored procedures generally just increases the overhead and workload of the developer. A good, skilled, and forward thinking developer is going to use an OR/M to get 90%+ of the database work done for the application. There really is no reason to use stored procedures for application development anymore, and if they are being used in large part, it's costing more than it should for the application.

I will add the clause, there are always times and places for stored procedures, generally, there aren't many times and places left these days.

...NOTE: I used to be in the stored procedures only camp. Mostly for security reasons though, and because at the time I had done most projects without any decent OR/M.

Answer 8

Folks keep thinking of databases as massive single-node supermachines. For good reason -- that's what they are in the majority of cases.

But there is such a beast as a distributed database, used for handling extremely large datasets. Such systems take advantage of massive parallelism offered by the dozens (or hundreds) of nodes that compose the system. If you tell them what you want to do with the data, they may well know how to distribute the computation, giving you significant time savings.

See, for example, Greenplum's and AsterData's offerings of in-database Map/Reduce.

If you have a single application, a single database, and a couple of developers -- do whatever makes you happy, it doesn't really matter. Push the computation to whichever machine is likely to have more CPU cycles.

If you have a large system that is used by multiple applications, perhaps not all of them on the same platform and using the same language, and some of them not even written by you but rather commercial BI tools -- save yourself the headache, and do implement your business logic in one place.

If you have a large data warehouse that juggles data as if it was auditioning for Cirque du Soleil -- don't go thinking you are going to do better with your "learn to juggle in 7 days" pamphlet.

Answer 9

I have absolutely no problem using stored procedures for batch operations that can enjoy the performance benefits of stored procedures. But for CRUD operations there are no benefits. There is no performance benefit, security benefits are debatable, and putting business logic in the DB makes the system considerably less maintainable than putting it in well designed domain layer. A good ORM will 'generate' good SQL for these CRUD operations, not dynamic SQL scattered throughout the system.

Answer 10

I use SubSonic for my DAL generation, however I still use stored procedures where needed, normally when I need to do multiple joins.

If stored procedures are going to die, they're not going to die for a long time.

Answer 11

Stored procedures are by no means dead. I've been using LINQ to SQL in a recent project, and it does a lot of really nice things. For one, it creates parameterized SQL, and the query paths are usually pretty nice. I'd prefer the data load options to create one query instead of one join and several additional queries, but I can't say there's really a performance difference as I haven't looked and I doubt it's significant.

You can still command LINQ to use stored procedures, and this is very beneficial in the one area in which LINQ is missing functionality: batch processing. LINQ is great for general CRUD procedures, but it doesn't perform batch updates (at least in code) the way SQL would. I extended the List.ForEach() method to IEnumerable to give the impression of a batch update, but that is really just syntactic sugar for foreach.

I would really prefer to see LINQ evolve into something like Tutorial-D (e.g. Dataphor, rel, and Dee). At that point, I would probably agree that stored procedures would be dead, but only so long as LINQ was the only remaining option. Many are familiar with stored procedures, and they'll be around for a long time to come.

Answer 12

I would argue that there needs to be a distinction between what is "business logic" and what is "data logic".

It is obvious that any operation that manipulates a significant amount of data over a series of operations, especially under a "two phase commit" scenario, should be performed as close to the data as possible. SP's are a no brainer in this case.

It is obvious, that the application layer should not try to replicate or mimic the built in data management systems of a RDBMS, i.e. constraints and joins etc.

Now, the steps performed to close a bank account (calculate interest, calculate balance, transfer balance to a different account, print the closing statement, mark the account as close for example), could be performed by a single call to a stored procedure, or via a series of "high level" sp calls to the database (and maybe other databases). The order of the calls, to which database, and the consequences of each call is defined by the business (the analyst) and is therefore defined by "business logic".

At this high level, then there is no difference between a .NET application calling a single SP or a few SP's.

If the requirement is to change the data, then it should be in a stored procedure. This is "data logic" (your implementation).

If the requirement is to modify the actions and behaviour of the application, then this is "business logic" (the purpose of the application).

Answer 13

I disagree strongly with the notion that stored procedures should be abandoned. (And the notion that T-SQL is somehow "arcane".) Whether or not you use stored procedures depends on the role your database plays.

At StackOverflow, the front-end application and the database are tightly coupled. Perhaps not literally, but the point is that the database was made specifically for the front end application.

This is not always the case. I've worked in various corporate scenarios where the database was exactly of the sort described by E. F. Codd: a "large shared data bank". Many, many, MANY applications talk to this database, not just one or two.

In such a situation stored procedures (and excellent check constraints with a dash of triggers) are critical to maintaining data integrity, by providing a consistent interface to the data.

I've heard some say that "business logic should not be in the database." (Although I may be misunderstanding what's meant by "business logic".) Well, that works great if the relationship between your database and your application is 1:1. But again, if your database services a great many applications it's very important that you provide a consistent interface to the data through stored procedures. Some kind of "middle-tier" layer is not enough. Databases tend to outlast the technologies that are used to access them. If you rely on your middle tier to provide the interface to your database, you'll end up rewriting long before your database is obsolete. (And the applications that use the old middle tier may still be around.) That said, there are other reasons to have a middle tier.

Most people use databases as if they consisted of a bunch of dumb hashtables and arrays. This is where the mentality of abandoning stored procedures (and eschewing check constraints and good candidate keys) comes from. They don't see the database as a full player in the application: It's just a place to dump data.

My applications tend to stand things on their head compared to what other developers do. My databases tend to be very "smart". Most of the logic lies in the stored procedures; the middle tier and application code is very light. Many developers think this is crazy, but it allows me to develop new applications atop the database very rapidly, in whatever language I choose. It provides superb data integrity and much fewer side effects when multiple applications are accessing the same shared data store. And I've never had any lasting performance problems, beyond those that naturally occur from time to time in the course of development and are fixed. My corporate customers have been extremely happy.

However, I readily admit that the arrangement I just described may not be appropriate for an app like StackOverflow. It depends on whether your database is a "shared data bank" or not.

Just my slightly rambling 2¢.

Answer 14

Top thing, this discussion of stored procedures vs dynamic SQL is only for basic CRUD statements if you have a huge statement that has to use multiple tables, parse values and then return a single value use a stored procedure.

First in MS-SQL Server stored procudures and dynamic SQL are not treated differently, they are both combined and cached. This has been the case for almost a decade, since MS-SQL 2000, IIRC since MS-SQL 7 but not sure on that. In use stored procedures are actually slower because you have all the conditional statements, isNull and COALESCE are very slow.

For SQL injection SP offer no additional protection over dynamic sql, it is only the use of parameters that prevent it.

Finally if you are doing a large program get yourself an ORM, LINQ is poor, and don't worry about it; and if you not use parameters in your SP or dynamic SQL you have something to learn over this weekend and do so quickly.

Answer 15

From what I have observed at work (which is a Microsoft shop), the side of the sproc vs dynamnic-sql fence our developers land seem to depend on when they started honing their craft. The ones who started back in the 90's always go with sprocs, while those who started during the .NET era favor dynamic sql and ORMs. My reasoning for this is that MS promoted sprocs in the 90's, and now with .NET, MS is promoting dynamic SQL through LINQ. Personally, I think either approach can do the job and that the preference of one technology over the other oftentimes has to do with the developers' age.

Answer 16

Realizing that Microsoft is steadily moving in this direction of thoroughly bastardizing the middle-tier layer in respect to the sql relational store of SQL Server, I none-the-less like the purity of putting database performance tuned code into stored procedures.

First of all, there are cases of being able to write code that resides in the database that is sometimes many factors or even an order of magnitude faster than middle-tier code attempting to achieve the same end result. Hence with databases, it often pays to have code that lives very close to the data structures (i.e., the tables and their relations) being manipulated. So from a performance perspective alone, a rationale for something like stored procedures is well justified.

So then the question might become:

1. Should some standardized database language be used for that? (Something like Oracle's PL/SQL language.)

2. Or should various ways to do database code in the middle-tier languages be devised such that the code can be downloaded for execution into the database server. (Something like C# .NET LINQ code.)

The thing about PL/SQL is that it remains strictly in the database and is easy to be invoked from practically any middle-tier implementation language that might be in use. This facilitates multiple applications accessing the database (such as legacy apps and then newer, lately-coded green field apps, which is a very typical scenario for any shop).

My personal preference is to not blend middle-tier implementation languages with database so tightly as Microsoft is doing. From a marketing perspective this makes sense for them as they are promoting a vertical stack that is extremely tightly coupled. Take their approach (which is unique to their programming languages in combination to their SQL Server), and you're definitely highly captive to the entire Microsoft stack. Sure, plenty of folks are comfortable with that. Plenty other folks are not.

Answer 17

I think the days of the stored procedure written in a variant of SQL different from the application programming languages may indeed be numbered.

I think the future is in stored procedures written in the application programming language, with some SQL commands, that can be migrated to SPs and back to app code with virtually no change. It would then be a security and tuning decision as to where logic executes.

SQL Server 2005 made a big step in this direction with its server-side CLR procedures and it will be interesting to see how this goes, for example will the DLR mean that IronRuby will be available in SPs?

Java is available for stored procedures in Oracle, DB2 and c-tree SQL Server Java Edition and has a lame Wikipedia article.

Answer 18

I don't know. When it comes to SQL server, I think it hardly matters. T-SQL is too limited, and SQL CLR doesn't really offer any major advantage over code inside the application, IMO. What I do like is Oracle's PL/SQL, where all queries are statically typed, so you can easily se what you break when changing a table.

Answer 19

In a not so humble declaration, stored procedures are not dead. I think what is dying is the ability for the average developer to write stored procedures. The decline of programmers who know how to write stored procedures and the decline of programmers who then know when to use stored procedures is a different topic than this one.

Answer 20

They say this just like those who said the mainframe was dead 20 years ago. Yet mainframe systems are still of real value to some businesses. Dead is relative to your mind set and needs. In 20 years from now when I'm getting paid 10x more to maintain those old stored procedures I will be very happy. LONG LIVE THE STORED PROCEDURE!

Answer 21

no, stored procedures are not dead.

Answer 22

and linq to sql isnt dead either

Answer 23

stored procedures are very userful

Answer 24

dynamic sql is useful to, but you should be careful about sql injection

Answer 25

My first boss told me how when he was young he was part of a team developing software for ATMs, and as a final step before deployment, his team had to prove to an external auditor that:

1. It was impossible to insert transactions with a negative dollar value
2. It was impossible to insert transactions with a timestamp other than 'right now'.

They had the database privileges configured so that the only way for the software to add transactions was through stored procedures which guaranteed both of those constraints would be met. The audit was passed without even looking at the software's source code.

Answer 26

Stored procedures will last as long as relational databases.

Answer 27

What about extended stored procedures, are they numbered too?

Answer 28

stored procedure days are numbered... as much as any other technology that is in use today that is.

As long as there are relational databases there will be stored procedures being heavily used.

As long as there is a huge development community based around the 'database environment' (as opposed to a 'NET programming' environment) there will be stored procedures being heavily used.

One thing you could say is that stored procedure use will reduce in the .NET programming community due to Linq 2 SQL and Entity framework.

Answer 29

business logic needs to be stored somewhere and if there are many applications using one database, it can make sense for that business logic to be in a stored procedure.

Answer 30

You can use stored procedures in Linq 2 SQL as well.

Answer 31

I hope that one day JIT compilers are smart enough to decide where a piece of code should run. Bring the data to the code or the code to the data? The JVM and the Common Language Runtime should decide and choose the best performing option for each piece of code. We devs don't have to know. A little bit like Microsoft Volta. I know that Microsoft Volta is offline but I still like the idea.

Answer 32

Let's take a look at the new kids on the block...

MongoDB for instance has support for stored procs (you code them in JavaScript), they are called stored server-side functions in the manual. See http://www.mongodb.org/display/DOCS/Server-side+Code+Execution .

If you use the in-memory OLTP database H-store you have to call a stored proc, you can't do it otherwise. Every call to a stored proc is one trancaction. You have to code those stored procs in C++ but in the future it will probably be Ruby. http://www.vldb.org/conf/2007/papers/industrial/p1150-stonebraker.pdf

MapReduce is all about bringing the code to the data instead of bringing the data to the code so it is basically uses 'stored procs' that you can write in a lot of languages like Java or Python. The amounts of data that are processed in a MapReduce-system is massive so it is much faster to process the data local.

Aster Data focuses more and more on pushing the application logic into the MPP environment because pulling data from the database to the application layer takes too much time.

I don't think that stored procs are dead. I just think that the programming languages will change, no longer T-SQL or PL/SQL but Python, Ruby, C# or Java.

Answer 33

Performance: That's the #1 reason I continue to rely on stored procedures, and it is not about execution plans or pre-compiling. The bottom line, is that for a well written application, the #1 performance issue is I/O in and out of the database. The less you pull out of a database and then stick back in, the better the performance.

For example, if I had a table with a "Cost" column, and needed to make Cost = Cost * 1.5 for 10,000 records; my sproc could do that in a couple of seconds, and data would never leave the database. If my app needs to pull each record, change the value, then save the record back 10,0000 TIMES, then how long will that take? What about dependent data (Joins), which would make the non-sproc approach even worse.

When an ORM can do that update without going into a 10,000 step loop of some sort, let me know, I'll be interested.