The Joel on Software Discussion Group (CLOSED)

A place to discuss Joel on Software. Now closed.

This community works best when people use their real names. Please register for a free account.

Other Groups:
Joel on Software
Business of Software
Design of Software (CLOSED)
.NET Questions (CLOSED)
Fog Creek Copilot

The Old Forum

Your hosts:
Albert D. Kallal
Li-Fan Chen
Stephen Jones

Why are stored procedures undesired?

In the naming conventions of the spec, Joel makes this off-hand remark.

"... and we hope never to use stored procedures."

Why is this?

It seems that every book and article on n-tier programming or SQL Server in general touts the need and benefits of stored procedures...

Why not with CoPilot?
Jeff K. Send private email
Friday, August 19, 2005
Maybe they are hard to debug? Or hard to port?
Daren Thomas Send private email
Friday, August 19, 2005
I may have no idea about what I'm talking about, but here it goes.

Stored proc equals programming code. So now you have programming code to maintain in your database as well as your application. 2 places to look for a bug in code? No thanks!
Michael Sica
Friday, August 19, 2005
gilf Send private email
Friday, August 19, 2005
He wants to keep server activity down to a minimum and have all the work done on the clients' computers.
Steven T Cameron Send private email
Friday, August 19, 2005
Keep the work on client computers? 

SQL, from the client app or in a stored procedure STILL run on the server... except in some cases you can get the sp to run faster. 

Why wouldn't you want sp?  I don't know... they isolate db access/errors to sp, and if they are tested and well kept, they can make things nicer.... the best I can tell here is it is a design decision
I forgot my posting name
Friday, August 19, 2005
My experience with stored procs goes back to Oracle 8i, so some of the issues I had may not apply any more - dunno.

There are 3 resons that I know to use them.

1. As an abstraction layer for data access

This sounds good in theory, but in practice we ran into deployment issues - they are just like APIs and need to be versioned.  So if you want to introduce a change that requires a couple extra parameters, or god forbids, make a change that does not change the interface but changes the semantics, you need to deploy the alternate set of stored procs first, with new names or something, then deploy the new code, then get rid of the old ones...  Too much trouble.  Of course, if you can afford downtime for deployment, it's not a big deal, but I've worked on 24x7 stuff.

2. As a way to centralize buisness logic.

Then there are performance issues - database machines usually are the bottleneck, and it's easier to design a scalable distributed architechture if the CPU- or memory-intensive business logic is executed elsewhere.

3. DB performance

If your logic requires multiple complex queries that are not easy to express as a single request to the DB, it may be faster to write it as a stored proc, compile and execute on the DB side without the overhead of making multiple external queries.  I am not sure that this holds water, but there are probably leitimate cases for this.  I personally think that unless there's a noticeable performance gain, the deployment issues outweight the potential gains
Lev Brouk Send private email
Friday, August 19, 2005
Well, for something like FogBugz, I could see not using SP's because that limits the types of DBMS you can allow.  For, it would seem its all up to Joel since its hosted on the central server.

Didn't Raymond Chen blog about why stored procedures are bad?  Or did he say they are good?  I remember the blog post vaguely, and that it triggered a long comment discussion.  Maybe it was Eric Lippert, and Chen was just in the replies.
Clay Whipkey Send private email
Friday, August 19, 2005
Correct me if I'm wrong, but aren't SPs more secure as well in terms of preventing injection attacks?  May not apply to fogbugz though.
Friday, August 19, 2005
Former COBOL Programmer
Friday, August 19, 2005
They are more secure than doing a string builder, but I don't think they are anymore secure than using a parameterized query.

Of course I could easily be wrong.

I usually end up using SPs only at a very late point after all the SQL has been debugged and you can identify what pieces need to run as SPs for performance reasons.
Steve Barbour Send private email
Friday, August 19, 2005
I've written before about cases where stored procedures can be undesireable. You should use them when it makes sense and not when it doesn't.  :)  I'm not sure whether or not I would agree with Joel in this specific scenario.

We don't use stored procedures because we sell systems that are installed in 1000's of retail stores across the US. Getting the code synchronized correctly is hard enough without having to also deal with stored procedures in 1000's of DB's. There are other reasons as well but the gist of it is that stored procedures (like triggers and replication) are great when you have a centralized database and a DBA around to main it all. When the databases are remote with no more than a dial-up connection for support related tasks, they become much less desirable. Imagine wanting to make a report change in the application and having to send new code AND a DDL change to 1000's of stores?
Friday, August 19, 2005
I don't understand.  Why are stored procedure changes not part of the code?

Bankstrong Send private email
Friday, August 19, 2005
Because Stored Procedures reside in the database.  Their syntax and source are Database centric.  They are typically maintained by a DBA, not a programmer.  In fact, I suspect each SP 'language' is specific to its vendor -- Sybase SP's can't run on Oracle and vice versa.

Thus it is difficult to handle Stored Procedures as if they were code.  And SP's 'break' the model of the Database as a repository of data -- make a query, get a result set.  Instead now your Database has code, local variables, temporary tables, etc.  As others have pointed out, you increase the complexity and reduce the maintainability of your system when you use SP's.

So the benefit of using them has to be pretty large -- like getting query performance you couldn't get any other way -- to offset the cost of using them.
Friday, August 19, 2005
How common is it to have major rev without schema updates? You have to upgrade the tables in this scenario, then upgrading the stored procs is free.
son of parnas
Friday, August 19, 2005
We do this in code:
 - check file version
 - revise stored procedures, schema, etc.
 - change file version

Admittedly, this is all for sql server.  If we ever switched to Oracle, we would have to make huge changes (but further db revisions would be handled similarly). 

This *seems* like the right way.  Besides everything else, changes are easily tracked in version control.

But you guys know tons - why is this wrong?

Bankstrong Send private email
Friday, August 19, 2005
Databases are supposed to hold data, not code, and stored procedures break that model. The practical ramifications of are the problems discussed elsewhere.

Why do people use stored procedures then? Perceived security, perceived performance, and percieved convenience. It *seems* like using a stored procedure to control user access to data is great, but that's mostly a hack because security models aren't part of the SQL spec. Security is a programmatic feature, *not* a data feature. But unfortunately, application environments tend to pass off the security problem to the storage system (OS or DB).

People also seem to think stored procedures are faster than the equivalent code in the application. My contention is---if they are, you probably don't have a good schema. You really ought to be select pretty much anything you need with a SELECT (against a VIEW). It's true that UPDATE and INSERT are weaker (since you can't really cascade multiple changes), but that's what transactions are for!

It also seems like stored procedures make application programming more convienent. Well, they do---but not because they are stored procedures. Abstraction makes programming more convienent. Put all your database access routines in a library (or even in another tier, in another language if you like) and you will have the same convienence. This, BTW, is one of the main preferred models of using EJB's in the Java world.
Robby Slaughter Send private email
Friday, August 19, 2005
> Why do people use stored procedures then?

Primarily performance. Paging tables over the network into application space is too slow.
son of parnas
Friday, August 19, 2005
"How common is it to have major rev without schema updates? You have to upgrade the tables in this scenario, then upgrading the stored procs is free. "

Pretty common. Even more common for minor rev's. Take the report change for example. The tables aren't changing. Just the way you query the tables and calculate something changes. With a report change you usually have a presentation change as well so code will automatically have to change. But having to make a SQL change too means extra cost when it really isn't necessary.

Here are some of the drawbacks to stored procedures in MY scenario (ie - 1000's of remote databases). The "enterprise server" scenario is much different.

1) Difficult to debug. In 1000 stores, there will always be at least 5 or more that are experiencing some sort of issue. Checking versions on code is easy. Peeking at stored procedures is more difficult (especially for an untrained tech support team over a dial-up connection). Having a customer send you their database in order to reproduce the issue is also difficult over dial-up. Like I said before, it is hard enough to get the right version of code in stores, let alone stored procedures.

2) Security - The database and code reside at a customer location. There is no extra security here because an advanced user can get at both easily. In an enterprise server scenario you could argue that you are isolating business intelligence on another layer and that it is more secure. That doesn't really apply here.

3) As others have pointed out, store procedures usually vary by DB vendor. Using SQL in an application data layer is more flexible and allows you to support multiple database vendors more easily.

4) Harder to deploy - We can use xcopy deployment for application file changes. Automating the execution of stored procedures is more difficult. We still have to automate SQL changes in general but we have found that these tend to fail much more often. Again, think about an update sent down to 1000 stores. How many will fail in a given night? 1,2,10? Reducing this failure rate is critical. By minimizing SQL database changes to be schema changes only (which don't occur that often for a mature product), we can minimize the failure rate for software updates. You will never eliminate all failures. But updates that only require files to be copied are less likely to fail than those that require SQL changes as well.

As a side note, we don't use triggers either for the same reasons. We've also learned that replication as designed for an enterprise server does not really work well for 1000's of stores. We typically will end up writing our own simpler replication systems because they can be optimized for our needs and we can build in automation capabilities. The out-of-the-box solutions simply aren't made to work without a DBA constantly watching them. We don't have that luxury.

What's really funny is that every time we get a new programmer or development manager and tell them that we don't use stored procedures and triggers they look at us like we must be stupid. It isn't until after we have told them why that it sinks in. There are so many people who have been brain washed into believing that stored procedures are required that it isn't even funny.
Friday, August 19, 2005
I agree with no triggers,as long as you can guarantee all code sets values through the same interfaces so integrity can be kept.

And if I wasn't upgrading tables everytime I wouldn't use stored proces either.
son of parnas
Friday, August 19, 2005
Ooops... one more reason:

5) Supporting multiple retailers - Each retailer has different ways of calculating totals, discounts, and various other reporting amounts. It is easier for a single code base to support mulitple retailers through configuration if the logic is in application code. Having this logic in stored procedures means that you have to try and pass in option values or maintain different stored procedurs for each retailer. With SQL code in the application I can easily throw a simple IF-ELSE contruct into the code and have the same report/function work differently based on a configuration setting. It's doable with stored procedures but it is a very big headache. Again, the enterprise server application doesn't usually suffer from this issue because it is only written to support one set of requirements.
Friday, August 19, 2005
Ooops.... last one. I promise! :)

6) Control - since our database resides at the customer's sites, they would be tempted to twiddle with the stored procedures. Yes. They really would and they do. We have retailers who would make changes to the calculations in the stored procedures instead of calling us to do it just so that they could save a couple hundred bucks. But of course, we would be the ones they call to figure out the problem when they mess it up! Having all SQL in the code means that they can't mess with it. If they were the ones supporting it then it might be a different story.
Friday, August 19, 2005
The JOS forum crapped out on me the other day. As I recall, it was running on MySQL.

Therefor no stored procedures support, so there better not be a need to use them.

The error report SQL statement that it popped into browser was pretty horrifying though. Might be easire to maintain with features such as views and stored procedures. Oh yes, MySQL doesn't have them.
captain damage
Friday, August 19, 2005
grover, in the end, someone will write stored procedures like today's dailyWTF:
Friday, August 19, 2005
" Databases are supposed to hold data, not code, and stored procedures break that model

Please, let me know what apps you write.  I don't ever want ot use one.
Friday, August 19, 2005
>> AllanL5: In fact, I suspect each SP 'language' is specific to its vendor -- Sybase SP's can't run on Oracle and vice versa.

Absolutely!  T/SQL (Sybase, MS SQL Server) is very different from PL/SQL (Oracle) and neither is strictly SQL92-compliant.  Even the T/SQL implementations vary now.

Other reasons: vendor implementations of particular features vary, so the syntax to do something like create a work table for a complex query will be different.

My previous employer developed a very complex app for a California state agency on Sybase using 1000+ SPs, then had to completely reimplement the data management for the next client on Oracle.  Not only was the SP syntax different, so was the client calling syntax AND simple things in Sybase, like scratch tables, were completely unavailable in Oracle at the time.  Necessitated a complete rework of the app's processing flow in a copuple of places.

If needed, say for multi-table updates or multi-stage transactions, SPs can be valuable but they very much limit cross-platform availability.
a former big-fiver Send private email
Friday, August 19, 2005
That's probably why Microsofties tend to recommend them so much.  ;)
Friday, August 19, 2005
The modern versions of some databases (including Oracle and Sybase) now allow stored procedures to be written in Java.  So that preserves most of the portability while still keeping the data shoveling within the database instead of across the network.
Friday, August 19, 2005
SP languages are certainly different between vendors, but then so is everything else ... locking model ... concurrency SQL feature availability ... optimization. When you make your database access generic you doom yourself to poor performance.

This is why it humours me to hear comments about databases being the source of performance problems, hence you move the code away from it, and you can make the SQL nice and generic.
David Aldridge Send private email
Friday, August 19, 2005
For inhouse software, the company is usually married to the database vendor anyway, so portability has a low priority.

But when it comes to software that is distributed to several external customers, database portability is much more important.  Lock your product into one database, and you lock yourself out from the clients that don't use that database.
T. Norman
Friday, August 19, 2005
Stored procs are overrated nowadays. For CRUD operations I think they're just clutter. Outside of simple CRUD they're fine to use. This way you'd have a managable sprinkle of procs for good reason.
Animal Send private email
Saturday, August 20, 2005

Yes, commercial pressures can certainly lead to a database independence requirement, but the comments still stand though -- that the result will be a poorly performing database layer which will then be held to be the source of the application's problems. In an environment like that you go into a region of circular logic, that the application code has to be kept in a different layer to the data because it is the database that is the source of performance problems.

I'm not having a bash at those who do database independence, because I understand those commercial pressures and I understand that all of these differences between vendors are very difficult and frustrating, but I am pointing out that it can lead to misunderstandings of the power, scalability and feature set of modern RDBMS technology.

You might like to look around at some of the testimonials that Oracle's HTML DB technology is getting. It's a web-based application development environment entirely besed on PL/SQL, served up through Apache. Solid, incredibly scalable, and easy to develop and deploy. Oracle only of course, but what I think it demonstrates is that application-code-in-database is not a model that is doomed to failure, and in fact is very economical and performant.
David Aldridge Send private email
Saturday, August 20, 2005
A couple of people have made the theoretical argument that using stored procedures is equivalent to putting code in the database is therefore a bad idea. I can't say I really buy that; I've always thought of SP as being on a separate layer  within the DB engine in much the same way that all my other code is 'layered' within the application.

As to the argument that database independence gets broken with SP, I would make the point that the right way to deal with this is similar to other cross-platform decisions. If I want or need my program to run on Mac, Windows, Linux, etc., then I can choose between something like Java and the cross-platform library of the week. Building from scratch for each platform would probably be a last resort, so let's ignore that for now. Java might be fine for certain types of apps where strict adherence to the platform UI is not necessary and where the presumed loss of performance is acceptable or non-existent. However, there are many situations where Java just won't cut it and sticking with Java simply because it's transparently cross-platform is probably a bad idea.

Cross-DB is similar in many respects. You can code up a true cross-DB app that just runs no matter what DB is installed, you can use a cross-platform library, or you can build different versions from scratch. Again, let's ignore that last one, leaving the choice between the generic cross-DB analogue to Java and the more complex, but presumably still manageable cross-DB library. There are lots of cases where the database neutral application will be just dandy, but I can't help but think that commercial enterprise-class applications should be making use of a cross-DB library.

I don't know if there is such a thing as a cross-DB library, but maybe it's time for someone to build one if it doesn't already exist. One feature would be the ability to code in some neutral language, obviously including SQL, such that the output can be targetted to any supported DB. The code editor would make it easy to embed target-specific blocks of code for those cases where there is a clear need to have target-specific code. 'Compiling' from that neutral language would be a matter of generating the relevant scripts for the selected targets and, in a development environment, it should provide the option to run those scripts against the development DB. Another aspect of the compilation process would be the creation of a data access layer that can be used the way a standard data layer is used now.

Obviously, there is more work when using a cross-DB library, but I would think that the results would be well worth the effort.
Ron Porter
Saturday, August 20, 2005
Most of the supposed advantages of stored procs are not true (these days).  See .  For some strange reason, many people in the industry keep repeating the same, incorrect, "advantages".  Endless repetition doesn't make something true, it just makes it look that way!
John Rusk Send private email
Sunday, August 21, 2005
"Databases are supposed to hold data, not code"

Who says?

Before RDBMS, databases were supposed to be one big table. Should we keep doing that too?

SP's are a way to process large quantities of data on the database server and return a reduced quantity of data over the wire.

Philo Send private email
Sunday, August 21, 2005
Mr Rusk, who is your cite?  A twelve year old that managed yes, next, next, next, finish.  Not saying he is but why does he have credibility?
Monday, August 22, 2005
"SP's are a way to process large quantities of data on the database server and return a reduced quantity of data over the wire."

You want to imply a simple dynamic SELECT query doesn't meet this definition?
Monday, August 22, 2005
What's so simple about a SELECT query? The Oracle 10gR2 SELECT specification is enormous, and includes subquery factoring clause, hierarchical clause, model clause ... then there's analytic functions, the flashback query clause ... all the elements that make a SELECT query really performant and powerful, and that Java/C# etc programmers have never heard of.

Sad truth is that most non-database application developers don't understand the architecture of the database, the consistency model, the locking model, the isolation levels etc,, and have the attitude of "well why should I learn all that stuff?". To which I reply, "Then why would you be trusted with database application development?"

David Aldridge Send private email
Monday, August 22, 2005
No.  A simple SELECT query does not meet that definition.

You are querying the rows to do some processing with them.  If that processing is done within an SP you don't have to push all that data back over the wire.  However if you pass the rows back to the client or middle tier for processing it has to travel over the network.
Monday, August 22, 2005
Don't forget that you can Prepare() an SQL statement and execute it multiple times too.

Stored Procs have their place, and that place is mainly doing set operations on the DB side of things or providing a standardized API for manipulating data when that API needs to be used by multiple disparate applications running on different platforms (and web services wouldn't cut it for some reason).

With good free ORMs like hibernate/NHibernate, there is no good technological reason to use Stored Procs for data access in most applications.

I'll grant that if everyithing "in theory" was true in practice, Stored Procs would have a lot going for them.  But 99% of developers don't write good stored procs and 99% of DBAs don't write good APIs for developers.

A data layer built with an ORM will beat the pants off of the common data layer built against SPs in almost every way.  The ORM will have functionality built-in like caching, cascading, lazy-loading, and enhanced queries.  I've *built* data layers based on SPROCs and you just never get the time to put all that stuff in yourself.
Richard P
Tuesday, August 23, 2005
Tom Kyte has a number of good threads on this topic; one of them can be found here 

Having dealt with a lot of midrange ERP packages developed during the client-server heyday of the late 80s/early 90s, and having seen how those vendors have found it near impossible to adapt when a new UI method comes in (the web; now PDAs) or their original development language goes out of favor (Centura anyone?), I have to agree with Tom and come down firmly on the side of "business logic in the database".    And his points about so-called "cross-platform" apps are generally on target too - they usually end up suboptimal for every customer regardless of what platform they choose.

sPh Send private email
Wednesday, August 24, 2005
Sorry about that link; Tom's Super Links(tm) really can only be embedded with tags not pasted direct.  Search for the following string

J2EE and Oracle's stored procedures

and it should be the first topic.

sPh Send private email
Wednesday, August 24, 2005
> or you can build different versions from scratch. Again,
> let's ignore that last one,
> [...]

I have talked to a number of high-end database consultants who have gone in to clients in distress and re-written their apps in 2 or 3 versions specific for target platforms, actually making them work on each platform and resulting in lower total lifecycle application development cost despite the seemingly higher front end outlay.  Of course by the time these guys get called in the spending on the failed version is already so high that anything looks good in comparision, but there you have it.

> but I can't help but think that commercial
>  enterprise-class applications should be
> making use of a cross-DB library.

How would you do that for Sybase and Oracle, which use 180 degree opposed locking philosophies?  Seems to me you would get the worst of both worlds, rather than the functionality you paid the vendor for.

sPh Send private email
Wednesday, August 24, 2005
I love using stored procedures.

It separates my code from my database, I can easily create stored procs, passing in parameters in my classes I build.

I get great reuse out of stored procedures.  I can debug my stored procedures (I use VS Enterprise 2003).

The list goes on and on.  Stored procedures are easy to write, easy to maintain - deployment is great because they can be scripted and run, etc..

I've seen inline sql in code - it's a fricken mess.
Steve Send private email
Wednesday, August 24, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz