Stored Procedures - End of days.

Question

I’m listening to the Hanselminutes Podcast; "StackOverflow uses ASP.NET MVC - Jeff Atwood and his technical team". During the course of the Podcast they are speaking about SQL server and say something along the lines of 'The days of the Stored Procedure are over'.

Now I'm not a DBA but this has taken me a bit by surprise. I always assumed that SPs were the way to go for speed (as they are complied) and security not to mention scalability and maintainability. If this is not the case and SPs are on their last legs, what will replace them or what should we be doing in the future?

Answer 1

On modern systems, parameterized queries are compiled and cached at the server, so they're just as fast as a stored procedure. They have most of the same security features as well.

For databases that serve a single application, it makes more sense to have the query logic there at the relevant location in the code. If nothing else it makes it easier to do source control. If you keep stored procedures on the server keeping track of them can quickly become a mess. Additionally, if you're using an ORM tool you may not have much in the way of real SQL anyway.

If your database serves several different applications, then you may still want to use stored procedures to enforce business rules between application.

Answer 2

SPs are probably the way to go if you are ONLY concerned about speed. But if you care about scalability or maintainability, SPs might not be the best. Our architecture is built on SPs and after 10 years of code, it is very hard to maintain and very buggy. A good ORM mapper might be a better choice.

Answer 3

I'd say that SPs aren't maintainable and they don't scale. Ask any developer who's had to add functionality to a SP heavy system. Why have half your logic in a different layer? There's nothing to replace, just don't use 'em.

Googled this great post.

Answer 4

ORM and LINQ to SQL seem to be the current trends for replacing StoredProcs.

I personally have used ORM and find it much easier to maintain and support.

Some of the reasons stated for using stored procs where never legitimate reasons to begin with.

You do make a good point about having a stored procedures when you service multiple applications; they essentially become the DAL, usually with some business logic in there.

Answer 5

maybe i'm too old-school, or too lazy, or both, but i have to disagree. Time and again stored procedures have 'saved the day' because when a minor back-end change or bug appears we only have to fix the stored procedure instead of updating the desktop application on several dozen desktops plus the web server. In addition, the users are not interrupted. This saves a great deal of effort and user hassle.

In addition, some DB operations are just going to be more efficient on the server rather than going back-and-forth across the network, esp. when one stored procedure calls another which calls another etc. (with or without cursors)

EDIT: in a SOA architecture the update-the-client-apps issue is mitigated (thanks maud-dib), but stored procedures calling each other is still more efficient than multiple network round-trips to the SOA layer. And updating the SOA layer is not always trivial either.

Answer 6

It also depends on what your stored procedures are doing.

For example if it's just

select a from b where x = y

then a stored procedure is probably overkill. But I personally tend to use stored procs to return multiple result sets and page results within the database.

In my cases I can see a benefit to using them. It is an extra layer to deal with but if you have your code well organised and logical I don't see too much hassle personally.

A good project with stored procedures is always going to be better than a shoddy project without and vice versa.

Answer 7

Just throwing my little advice here. Before SQL 2005 (maybe even farther than that), SP were faster. However, SQL Server 2005 and up are really optimized and they cache your queries as you go. In fact, we had a web application transfered to a new SQL server. The application started by running slowing for everyone. Everything was taking "3/4 of a second" or 1 second to run. Then SQL started compiling the most used query and everything went from slow to blazing fast.

Of course, we swapped the server while there was a lot of people running on it (which can explain why it was slow at first). But trust me. SP are not over. They just have other uses than being tied to an application.

Answer 8

This question also bleeds into one posted earlier today.

Answer 9

When you combine SPs with logic with the database itself, you effectively convert the DB in to something akin to an Application Server.

Back when this was the hammer that was most handy, it made a lot of sense. But now with ubiquitous availability of Application Servers, it makes more sense to leverage them for things like centralized logic and business rules and rely on the DB for persistence only.

Answer 10

Judging from the lackluster performance of this site, I'm going to wager the major bottleneck is the database.

I'm not convinced in any way that the LINQ 2 SQL ORM they are using is one bit faster than a sproc.

Answer 11

SP's are generally used way too soon in the dev process. They should be used when you have a bottle neck sql statement. For example, you probably don't need an SP for deleteing or creating users for an app. For most companys that should be pretty static.

Answer 12

I keep hearing the argument that SPs are good if you have multiple applications connecting to the database or that it makes bug fixing easier.

THIS IS WHAT A SERVICE LAYER IS FOR!

Business logic goes in the service layer, application logic goes in the application/web site.

It's alot harder to debug and maintain hundreds of SPs (especially if there generated) than it is to maintain well written code that talks to a DB via an ORM tool.

Answer 13

Maintenability Probably SPs are better. If maintaining hundres of SPs are hard, maintaining them in business tier components is even harder.

Performance Caching of queries might be producing near performance to SP. But they can't match performance of SPs across varieties of databases in varieties of platform. Network latency is another area of concern, though the gap is reducing nowadays.

Debug Is probably fairly easy with SPs than debugging business tier + db tier put together.

There can be even more +ve points using SPs.

But looking at the modern trend of programming, its rather "wise" to go with 'N' tier architecture for plenty of business reasons than sticking with "old" SP based approach.

Good system should have mix of both. Probably following 80-20 principle, 20 being SPs.

Answer 14

Could it rather be that Jeff Atwood knows stored procedures will be around forever and was merely trying to stimulate thought and debate? I like to think that what he really would like to do is to write a persuasive essay entitled "Stored Procedures Considered Harmful" :)

Answer 15

Some good point make on both sides (as it were) but no-one has made much of the security implication. Wrapping up all DB interaction in SPs means you can lock down the DB so that any interaction with the data can be tightly controlled.

If you think of Encapsulation, you can regard the DB as an object and the SPs as methods and properties that expose the objects functionality to the outside world.

In some larger development environments, UI and Business layer developers aren't allowed near the DB. They specify their requirements and the separate team provides and interface via SPs.

There are some good reasons for not using SPs and some good reasons for only using them - depends on your application and your environment. But rest assured that SPs won't be going anywhere anytime soon.

Answer 16

I might add that some work be better done at the DB level.
e.g. Cross tab results in SQL 2005, Recursive queries.

I agree that some of the simple stuff such as SELECT, INSERT, UPDATE, DELETE can be taken care of by ORM, Linq.

So, it is stupid to say that days of stored procedures are over.
How many people really have to worry about DB platform changes (SQL to Mysql, Oracle)?

Answer 17

Part of this is driven by the availability of non-relational datastores. SPs generally imply a relational database; ORM and Linq offer the the ability to create abstraction layers that are richer than SQL offers, and sometimes a better match for the abstractions we use in other parts of the design (the "impedance mismatch" problem.)

And it can also be considered a function of architecture. SPs imho match pretty well with a classical table-driven application, and can provide a convenient way to architect an abstraction layer at the business-objects level.

They're not so handy if your data store is xml, or an analytical database.

Answer 18

Bah!

ORM, SPs, View, Magic Wands, or whatever.

Each "tool" has its place in your belt, use the tools you have wisely.

The only thing that has "changed" ( really improved ) is that some ORMs have nice caching tools already baked in and MySql and Sql 2005+ can handle dynamic or ad hoc query/execution plan caching.

The potential performance loss from throwing the all sorts of dynamic sql at your db server has been somewhat mitigated. Its just easier to go without stored procedures now. Stored Procs aren't going anywhere.

Answer 19

Stored Procedures / Views / Functions are still a good "Interface" to the database if you are running multiple enterprise applications sharing the same database.

App#1 - You need to add a new relationship/field, or change a nullable column to non null.

App#2-10 - May or may not use this database object

The first thing I want to do is check my database object dependencies to determine how its used and if I'll break anything. Well, guess what, if you have a bunch of external queries VIA ORM / SQL you would have no idea of the dependencies.

This is the only drawback I found having direct access to tables.

For a single application using single database, this really isnt an issue. Although you still have to look at the application code for dependencies in addition to the database.

Answer 20

Stored procs are useful for stuff that is not CRUD -- such as specialized, cross-table logic that is best executed in the DB. CRUD operations should not use SPs unless they are the automatically generated output of an ORM tool like TableAdapters.