views:

1606

answers:

20

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?

+15  A: 

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.

Joel Coehoorn
Agreed SP are a blessing when you have several applications in several languages (thus can't share code among them).
Robert Gould
but who writes a system where the stored procedures only perform queries? what do you do for updates - or update side-effects (without triggers, which of course still suck ;-))
Steven A. Lowe
"queries" refers to insert, update, and delete et al as well.
Joel Coehoorn
If your database serves several applications then you probably need a service (WS/REST) layer.
flukus
@[maud-dib.blogspot.com]: it wouldn't matter if the database only served one application, updates to one table cascade into updates to other tables; since triggers in general suck, the stored procedures call each other. For a "simple" DB parm.queries would be fine, as would a trivial WS layer...
Steven A. Lowe
On a modern system ad hoc and dynamic queries are cached and compiled as well.
jfar
+9  A: 

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.

NotDan
Good ORM is a contradiction.
Ian Boyd
Just because SPs didn't work for you does not mean it can not work. SPs can be done right, ORMs can be done wrong
Sam Saffron
+13  A: 

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.

Terry Lorber
how do you justify that first sentence? i would say any code can scale and be maintainable as much as any other code to a large degree
marshall
My experience. Maybe I should say: "For me, SPs are hard to maintain, especially at scale, with many developers across different continents."If the entire system is SPs, that's one thing. I haven't seen any systems like that.
Terry Lorber
I'm not sure that post was so great. That guy was also opposed to using database constraints (such as foreign keys). I think he also objected to using primary key or unique constraints at the database level. Seems completely nuts to me.
RussellH
The Tony Marston post does not include the words "primary" or "unique", so, jump to your own conclusions.
Terry Lorber
+3  A: 

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.

Brian Schmitt
See, it's things like this that really confuse me.. Check out the summary.http://www.theserverside.net/news/thread.tss?thread_id=31953
Skittles
But until ORM and LINQ are supported for all languages we'll still be using SPs (although I wish LINQ would spread out faster!)
Robert Gould
This Serverside article misses the point (again) like so many others. People who argue against stored procs (for most but not all situations) are not advocating moving sql from stored procs into a DAL. They are advocating removing the sql all together! How can that not be more maintainable.
Craig
Craig, could you explain what you mean by "They are advocating removing the sql all together. I could be missing the point too.
Skittles
If you use an ORM like nHibernate the SQL is generated by the framework automatically behind the scenes. The developer is not required to write any SQL if they don't wish to. In my site www.jobtree.com.au the only SQL I wrote was for some nightly batch processes.
Craig
The problem with ORM like Hibernate is that you have to use hibernate to do data access and updates.
Ian Boyd
+36  A: 

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.

Steven A. Lowe
I agree, this is still very true of any sort of distributed application. I do feel that its not as important as it once was for server-centric applications, as a recompiled DLL dropped in an app server performs the same function.
cfeduke
That applications shouldn't be connecting to the DB. They should be connecting to a service layer.
flukus
LOL - i would agree except that (a) some of these apps are older than SOA, (b) some of these apps are real-time, (c) SOA just moves the problem, since the original assertion was that stored procedures are "obsolete"
Steven A. Lowe
"Applications shouldn't be connecting the the DB. The should be connecting to a service layer application."
Ian Boyd
@[anonymousstackoverflowuser]: uh yeah, right. see above. because absolutely everything these days requires at least three layers, including legacy systems. not. ;-)
Steven A. Lowe
+1  A: 

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.

marshall
+1  A: 

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.

Maxim
Since SQL Server 7 queries have been cached.
Craig
+1  A: 

This question also bleeds into one posted earlier today.

cfeduke
+2  A: 

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.

Will Hartung
+2  A: 

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.

FlySwat
+1  A: 

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.

Maudite
Exactly. SPs arn't an evil thing that should never be created (alot of non ORM users seem to be under the impression that we think this). A super complex query that is a common bottle neck in the application is a great use for stored procs.
flukus
if by 'query' you mean 'a set of T-SQL statements' then i must agree. But if you literally mean a 'query' i.e. a single SELECT then i must disagree. For a simple/silly example, deleting a user for an app may cascade into deleting every record that user ever created, in several tables...
Steven A. Lowe
Why shouldn't the be used to delete a user? There's a lot of queries that need to be run to delete a user (breaking FK's, deleting dependant objects where appropriate). i write the SQL once and then paste it into a SP. Now it's a canned routine. My "business layer" can call that, or copy/paste.
Ian Boyd
+8  A: 

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.

flukus
i like ORM tools, they speed development - but they are no substitute for sprocs for complex updates. You'll probably find - via profiling - that replacing your heavy ORM updates with a sproc will improve performance considerably. Assuming that your db operations are that complex, of course...
Steven A. Lowe
I would agree, If you find a bottle neck and an SP solves it then that would be a valid use case.
flukus
+1 for reminding us to use service layers - and for being agreeable ;-)
Steven A. Lowe
The only issue with this is in some enterprise situation where there may be various applications accessing the DB all running on different platforms getting them to all go via a service layer can be difficult. You could take an SOA approach but I think people just find SPs easier.
Craig
It's a whole separate issue, but why write two applications when you can write one? Split business logic into a separate application, you've given yourself twice the work and half the performance.
Ian Boyd
+6  A: 

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.

Murthy
+1  A: 

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" :)

onedaywhen
+1  A: 

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.

CJM
A: 

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)?

shahkalpesh
+1  A: 

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.

le dorfier
A: 

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.

jfar
A: 

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.

Ben Dempsey
+3  A: 

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.

Tom A