Stored Procedures - Good or Bad?

superid asks: "I'd like to get opinions and real world experiences that people have had with database centric applications that rely extensively on stored procedures. I believe that most enterprise class databases such as Oracle, MS-SQL, PostgreSQL, DB2 and others implement stored procedures. MySQL has been criticized for not supporting stored procedures and will be adding them in MySQL 5. The ANSI-92 SQL Standard also requires implementing some form of stored procedure (section 4.17). So, I'm asking Slashdot readers: if you were architecting a highly data-centric web based application today from a clean slate, how much (if at all) would/should stored procedures factor into your design? Where are they indispensable and where do they get in the way?"

"The arguments for stored procedures are pretty straightforward: 1) Centralized code; 2) Compiled SQL is faster; 3) Enhanced security (as our application is over 15 years old, and consists of much legacy code, reimplementation and feature creep that now includes over 3000 stored procedures). At one time we had a client/server architecture so those three advantages were relevant. However, in the past 4 years we have moved everything to web front ends and I have argued that this is no longer true. Does it really matter if my business rules are centralized in stored procedures or in a set of php/asp scripts (ie, in the web tier)? Is it really important to shave compilation time when connection and execution times dominate? (and overall response is ok anyway?) Since the focal point is the webserver, shouldn't security be done there, rather than the DB?

In addition, you either have to have a dedicated T-SQL or PL/SQL coder who then is the weak link in your coding chain, or your pool of developers must become fluent in both your scripting language of choice as well as the SP language. I have experienced both of these approaches and found this to cause bottlenecks when 'the database guy' is unavailable and learning curve problems (bugs) with new coders getting familiar with the db language.

Finally, after staying with our DB engine choice for all these years we are acknowledging that they may not be around forever. Management has asked us to look into migrating our data and business logic to another DB choice. We'd sure love to just be able to point the web tier at a new data source but that is unattainable due to a convoluted tangle of db specific code."

I don't use em unless I have to

[ph4rmb0y]

I like to keep business logic in one place as much as possible. You are almost assured to have some in your app, so I try to keep all logic there.

Stored Procs and triggers make can make the code simpler and more efficient, but spread out the workings of the application and unless properly documented, more difficult to understand.

Just my $0.02 CDN

Re:I don't use em unless I have to

[nz_mincemeat]

Stored Procs and triggers make can make the code simpler and more efficient, but spread out the workings of the application and unless properly documented, more difficult to understand.

As a developer I've found otherwise. The reason being that when you're examining a bit of code with embedded SQL you often lose context of what table structures it is trying to refer to.

Of course my DBA is very good in helping out and training the developers in SP usage, so YMMV.

Re:I don't use em unless I have to

[innosent]

The other major issue is security. If your app has the ability to view tables in the database, then breaking your app, or finding the login and password your app uses compromises the entire system. It is much better to implement security policies in the database/stored procedures, and only allow users/apps to call stored procedures, possibly even using the procedures to validate login information on each procedure call. If someone managed to get the (non administrative) password to your database, would you rather have them be able to do anything they want (or even just view anything they want, such as credit card or health information), or have them only be able to do exactly what they were able to do without the password?

Especially if login information is used as a parameter (I always use username and password as parameters to look up access levels in all user-executable scripts for this reason), if the user cannot access a single table in your database, then they can only do what the stored procedures allow them to do. This allows you to have a central place where security logic is executed, and business logic may either be in the procedures (ideal), or in the app. Just remember, leaving business logic up to the app may pose a risk if the app has a flaw, if the procedures allow the user more access than the app does. If your database security model has more flaws than your app could, then maybe you should consider switching databases.

Business Logic In Stored Procedures

[yintercept]

I agree with the point about including all business rules in a single location.

My tendency with data intensive applications is to put all of the business logic in Oracle stored procedures. I then have a variety of front end applications accessing the stored procedures. When the integrity of the database is the main concern of the application, I might write all of the business logic in a Java, PHP or C++ layer, hoping that no-one dinks with the data.

The big advantage of putting all of the business logic

Good and bad of stored procs

[einhverfr]

First, I think that stored procs are not only often good but lead to better, more powerful, secure, and flexible applications that would be feasible without them. But on the other hand, they lead to hard to maintain applications which are explicitly tied to one database. So they are necessary but often misused.

Triggers are more important and usually use stored proceedures to ensure that the information in the database is always meaningful or that some other automated activity happens on an insert or update.

Unless I absolutely have to, I try to avoid having my application call stored proceedures directly. A relational database manager *should* be able to hide the stored procedures behind a view allowing a nice standard interface for your data. This means that if you have to move to another RDBMS later, porting is much more simple and mostly confined to the backend.

BTW, I agree with the points about having your business logic in one place. Stored procedures allow you to move business logic which is inherent to the database into the database, thus making it available from all clients regardless of the language they are written in. For a single app/db pair this is not an issue but if you have a large DB with many different clients, it is a larger issue. Maintaining your application in one location is a LOT less work than reimplimenting it all in every one of your apps.

Triggers, BTW, as I mentioned before are very powerful mechanisms. They are not called by the app directly but are run every time a row is inserted, updated, or deleted (some RDBMS's also allow select triggers, though some have alternate ways of implimenting this). They can be used to impliment additional security restrictions, enforce referential integrity, or more advanced stuff such as send email when a new order is added to the database. Again, this is done regardless of whether the order is filed using a web app or a Windows app for the rep in the call center. Since the logic is unknown to the app, it isn't even aware of what happens after the order is placed. Talk about clean interfaces..... This requires stored procedures.

So, these are the great things about stored procedures. But when they are used badly, you end up with the stored procedures reducing the agility of the application because they tie it in too closely to the database. What do you do when your app is tied to Sybase and your customers want MS SQL Server? What if *all* your logic is in that database? Do you rewrite *all* of it for MS SQL Server? Probably Not. You are stuck and out of your investment.

In my opinion, it is important to maintain a clear line between what is the database's job and what is the application's job. If this line is blurred, bad things can result. Stored procedures are a very easy way to blur this line.

I design my apps with the following layers to them:

UI
Application-specific logic
Database Access Layer
Information Presentation (SQL API using views)
Information Management and Data Logic (usually stored procedures)
Information Storage (highly normalized, usually).

This allows the database to be a server app in its own right, and the client logic to run in the apps themselves. HERMES (see my sig) is mostly built this way, but there are a few things I need to change before I am happy with the interfaces. This is one reason it no longer supports MySQL.

Re:I don't use em unless I have to

[innosent]

Most of the time the DB isn't even accessible from the outside. Why worry about being rooted then ?

There's your answer... Most != All. The DB is connected to something, which is connected to something that is accessible from the outside. But attacks don't just happen from the outside, either. Probably 90% of the attacks you really need to be concerned about with most DB applications are an inside job. Can your company trust every user that has some access to the database?

Re:I don't use em unless I have to

[dubl-u]

Can your company trust every user that has some access to the database?

Yes, if the only thing that has access to the database is the application logic.

I feel that you're better off putting all of your business logic in one place. That can be the database, of course. I know a very clever DBA/developer who built a financial exchange system entirely in the database, accessing everything via stored procedures. It was very cool, as the database took care of many of the hard parts of building a system like tha

Re:non-proprietary stored procedure languages

[abirdman]

I agree. However it should be noted that just changing the prodcedure language doesn't avoid proprietary lock-in. Writing a stored procedure in any language requires writing to a specific API with specific structures in a specific context for accessing, manipulating, and returning data. The fact it's written in C or Python or PHP or PL/SQL may make the stored procedure easier for someone to understand (if they're more familiar with those languages-- and I agree with a previous poster that PL/SQL beyond the

Re:I don't use em unless I have to

[Gilk180]

This is not a problem with the SQL, but with the coding. It makes things very easy to read and more easily maintainable if you isolate each SQL query/command or small sets of queries/commands in their own functions/class.

Not only does this make the main logic more readable, it allows you to modify the SQL implementation without touching a lot of code.

For instance if you split one table with a column with a few large pieces of data that are repeated many times into two tables, one with a reference to a k

Re:I don't use em unless I have to

[severoon]

Stored procedures aren't good or bad...they just are. Passing a value judgment on whether they're good or bad is completely dependent on the situation.

I would argue that the three main points you made in favor of stored procedures are not points that apply to every case (I don't think they were intended to, either, but hear me out):

1. Centralized code. There are lots of ways to divide code up. I would argue that good n-tiered designs for web apps already use methods of organizing code into deployable/organizational units (layers, design patterns, component object models, etc) that render irrelevant the contribution that stored procedures are able to make in this regard.
2. Compiled SQL is faster. This is purely a performance enhancement. Performance enhancements like this belong at the back of the development cycle once you can measure the performance of the app; maybe it's perfectly acceptable. If it's not, then you look for bottlenecks and focus the energy where you get the biggest bang for the buck. If the biggest bottleneck is compilation of SQL, then stored procedures is your answer. Until the performance analysis is in, though, I think implementing performance enhancements maximizes investment of time and resources and minimizes return. Besides, much of the time a good, scalable design makes performance considerations irrelevant...you can have the cleanest code and just scale it up over hardware (within known constraints, of course) until performance meets requirements.
3. Enhanced security. Depending on stored procedures as a key element of security is obviously not desirable. Having said that, in your particular case, though suboptimal, you have to look at the big picture to see if the business justification is there to leave it in for this reason. Having said that, I would try to design the app, security-wise, as though stored procedures don't exist to whatever extend possible. Just as with the business rules, you should be free to change DB vendors from a security standpoint as well.

I would argue that business rules and business logic should be implemented in a vendor-independent way. Also, I would implement the business functionality of the app so that it can support a web front end, but also someday a desktop UI, a programmatic web services front end, etc. That's the soul of n-tiered architectures, they're supposed to bring that kind of flexibility along with the use of tiers...this kind of flexibility is the point.

If an app is not flexible in this way but claims to be an "n-tiered architecture", I'd argue that it is only nominally so. Looking like an n-tiered app without providing any of the benefits is a Pyrrhic victory for the architects and designers. That would be inconsequential except for the (usually large) investment of company resources.

Re:I don't use em unless I have to

[Atrax]

> Compiled SQL is faster.

Actually, this depends on the database in question these days. SQL Server 2k does a pretty good job of keeping embedded queries hot, so the performance gain is waay less impressive than it was in, say, SQL 7.0

[cue MSSQL Bashing in 3...2....1.....]

Haven't really kept up with competing RDBMSes recently, but it wouldn't surprise me if competitors were also narrowing the gap

> Enhanced security

One incredibly common security hole being SQL Injection, I have to agree with this, but with the following caveat :

I've seen developers create stored procs which do a bunch of string concatenation within the SP, then EXEC the resulting string. This is just as injection prone as doing it in a script in the first place, but the developers in question often cite SQL injection as their one of their reasons for using SPs in the first place.

Again, back to the problem with lack of knowledge on the developer's part causing security holes, rather than the platform.

Re:I don't use em unless I have to

[1001011010110101]

I've seen developers create stored procs which do a bunch of string concatenation within the SP, then EXEC the resulting string. This is just as injection prone as doing it in a script in the first place, but the developers in question often cite SQL injection as their one of their reasons for using SPs in the first place

Only if any part in the string is tainted (like deriving from user input). Otherwise dynamic SQL is pretty safe. Otherwise I agree, I think pearl is pretty neat on this aspect.

Re:I don't use em unless I have to

[1001011010110101]

Pearl being my coding sister :) (Of course, I meant Perl)

Re:I don't use em unless I have to

[1001011010110101]

I'm an Oracle DB guy (trying to dive into J2EE, better late than ever), and while I'd agree with your general point of view, I think you are missing something:

1. Centralized code. There are lots of ways to divide code up.

Sometimes is nice to assure that no matter what tools access your data model, only valid/complete information gets into it (usually this is the case with schemas that have existed in a company for some time). Sometimes there are different front ends to the same data, incoming interfaces, etc. They tend to grow on databases as time passes by :). It also helps you reusing business logic in different technologies/applications.

2. Compiled SQL is faster. This is purely a performance enhancement. Performance enhancements [...]

Usually coding thru the stored procedures/triggers is a good way to have access to all the features in the database and programming languages, performance and productivity wise...some things are not available thru a interface to external engines and you need to be pretty close to the engine to get them. These things can make a huge difference.

3. Enhanced security. Depending on stored procedures as a key element of security is obviously not desirable.

Stored procedures are a very good idea if they are the only way to access the related data schema. Just be assured to deny direct write and/or read access to the tables.

[...]Should be free to change DB vendors from a security standpoint as well.

One thing that I've seen around and found quite appropriate: When deciding whether to choose exploiting or not DB specific features, the DB cost is a factor. Why buy a brand name DB and not exploit the features it gives you? The cash you spend on it should be used for something, either performance/resource-wise, or easier development. Otherwise just use an el-cheapo engine like postgress or mysql.

Re:I don't use em unless I have to

[msobkow]

Well, you certainly know how to pitch the Oracle monolithic viewpoint on database architectures. It works, I did about 7 years on Oracle 5-7.2 before I ended up working with other RDBMS for a while. I'm not knocking your opinion, just saying that it's specific to Oracle.

DB/2 UDB used to have relatively poor stored proc performance, because they ran out-of-process. I think that's changed with the latest releases, but I'm not sure. Yet you could still get blazing performance for certain apps by using pr

Re:I don't use em unless I have to

[1001011010110101]

Well, you certainly know how to pitch the Oracle monolithic viewpoint on database architectures Well, I worked as a tech presale some time ago and that shit ends up sticking to your brain eventually :) I dont think performance is the main issue here..at least not the performance of the queries running. Precompiled queries and executions plans are not a big factor (in fact, at least in Oracle I think they are both re-parsed periodically to assure they are accounting for the updated table statistics, index c

Re:I don't use em unless I have to

[msobkow]

That depends on your fundamental architecture. For smaller applications environments your app servers and RDBMS are often on the same box. Not great for security, but it often brings the price down to a point where the project can go ahead.

With all of your "traffic" in-server many of the vendors stacks will let you use IPC messaging instead of IP messaging, which can boost performance rather significantly.

The other thing to consider is that for environments like the full IBM stack, you're expected to have a cluster of DB/2 UDB servers with a hefty, hefty backbone in the data center.

Like I said, the vendors have different approaches. Oracles is monolithic -- get it all into one big server. DB/2 is pure database, and expects to have other architectural components in use when building an application. Sybase tries to walk a line that can live in either variant, with interesting ideas of it's own. Postgres has some really slick custom data type support.

The point is that within the overall application cluster, you can get the same kind of performance out of any vendors RDBMS is you're using the appropriate application stack in a properly configured cluster. You just don't architect the solutions for the different products in quite the same way is all.

Re:I don't use em unless I have to

[Hangtime]

Enhanced security. Depending on stored procedures as a key element of security is obviously not desirable. Having said that, in your particular case, though suboptimal, you have to look at the big picture to see if the business justification is there to leave it in for this reason. Having said that, I would try to design the app, security-wise, as though stored procedures don't exist to whatever extend possible. Just as with the business rules, you should be free to change DB vendors from a security standpoint as well.

SQL injection is a very large problem in the enterprise. Stored procedures facilitate better safety. Yes, you can test for all the characters in your code but as soon as your app and password are compromised their is open reign on your database if you gave it data reader, data writer, dbo or heaven forbid SA. All new applications are build on stored procedures not only for data modfication but also SELECT queries. The application itself has no rights to the underlying tables. This ensures if indeed the application was ever compromised the most anyone could do is what the application does today. Also, they would have to figure out the XML strings to manipulate the data with a stored procedure.

Re:I don't use em unless I have to

[nettdata]

Stored procedures facilitate better safety.

I agree totally. I've seen too many "enterprise" apps that reside on DMZ boxes that have JDBC connections straight into the data tables in the DB. Not really that great from a security standpoint, IMO.

I prefer to make all remote calls via Stored Procedure API's, via a "proxy" database schema that only has execute permissions on the procedures, and no direct data table access.

This also means that the middle-tier devs can write code for a published API, and the

Re:I don't use em unless I have to

[Westley]

Hang on a sec - you're conflating two issues:

1) General security - yup, SPs win hands down here.

2) SQL injection attack - using prepared statements and parameters, this *shouldn't* be a problem with a Java or .NET app. (I would hope this functionality is available elsewhere too.) You don't write any of the parsing or formatting code - you let the DB vendor do that. (If they've screwed that up, of course, you're vulnerable - but then they might equally have screwed up SP security.)

Given an app which treat

Re:I don't use em unless I have to

[Westley]

I know what you mean - admittedly the problems with the databases I've seen haven't been security (usually) so much as just horrible design. Databases don't evolve cleanly :(

The great thing about using parameters though is that not only do they help to secure you against SQL injection attacks, they actually reduce the amount of work you need to do, too. For instance, suppose you have to put a date/time in the database. Using straight SQL this is a bit of a pain - you need to make sure you use the right for

Re:I don't use em unless I have to

[CaptDeuce]

Stored procedures aren't good or bad...they just are. Passing a value judgment on whether they're good or bad is completely dependent on the situation.

And in theory, there's no difference between theory and practice. :-) It's true that every situation is different but from my perspective, sprocs (stored procedures) should be the only way that application programmers can alter a database. I'm an SQL programmer. I carry a coffee mug.

In my opinion, applications shouldn't have any more detailed knowled

Re:I don't use em unless I have to

[bigman2003]

You've got some good points- or at least I agree with you, for whatever that is worth.

My experience has usually been "get the damn thing working" by doing all of the sql in the application.

Then, when things are looking good, customer is happy, you're happy...you can start moving to stored procedures.

Some people might feel that this is wrong, because it should 'be done right the first time'. But usually the reality is that it just has to get done SOON. And if I can sit there and dink around with some qu

Why stored procedures are bad.

[killjoe]

I know there are exceptptions but in most cases....

1) Stored procedures are not written in an object oriented language and are almost always not written in an object oriented way.

2) Stored procedures are not checked into a version control engine.

3) There is no sane way to organize them beyond manimg tricks. No breaking up your stuff using directories for example.

4) No global compilation. No way to check ahead of time whether you just broke another SP by passing a string instead of a number in as a param

Re:Why stored procedures are bad.

[bit01]

I'd add:

10) Stored procedure languages are usually kludgy and encourage bad programming practices.

11) Applications written in two or more languages are usually a significant maintenance headache. Not only do you require experts in all the languages but the interfaces themselves can be problematic. This is particularly true for applications that store significant state both in and out of the database. Synchronisation can be a major issue.

I use stored procedures when I have to, usually only because I n

Re:I don't use em unless I have to

[pHDNgell]

There are many reasons to use them. Performance is always one of the first reasons that people give. When we have applications that run in our enterprise, it is very easy to update a stored procedure if you find a problem.

Wow, that almost sounds like a bug. I hope you have a good process around that.

That's where I've always had a problem. Rolling out a new version of my code requires some DB support. The DB support has to be synchronzied and sometimes reversable. When dealing with stored procedures,

Good or bad?

[Anonymous Coward]

Bad, of course.

I only put triggers or constraints or whatever in the database for one reason: to make sure only valid data enters the database.

For instance if ColumnA must be between 5 and ColumnB+34, that should go in the database. The database itself should guarantee the data is clean. "Data logic" you could call it.

Business logic and everything else should go in the higher layers. There is some ambiguity about what is "data logic" and what is "Business logic" but it's usually pretty clear.

Why? Maintanence. The stored procedures tend to rust in place over time. If you're an "agile" developer you'll go nuts not being able to refactor business logic or have unit tests check your database procedures. If you're a "BDUF" (big design up front) shop, you might like it, but thankfully many are moving away from that.

Re:Good or bad?

[saberworks]

I totally disagree. Stored Procedures have a lot of benefits.

Speed

Code Reuse - if everyone uses stored procedures, it's less likely that your developers will just write another (duplicate?) query when they don't want to go mucking around in all your php/asp classes/functions.

Bandwidth between the database server and your web server will be reduced - instead of passing huge queries across the link, you send a simple stored procedure call.

Plus, PL/SQL is really easy to use and learn and is relatively por

Re:Good or bad?

[johnstoj]

Don't be assinine. A modern production environment is running at least 100MBit Ethernet, if not 1GBit. A HUGE query, say 2500 characters, is 2.5KBytes. Gee, thats pretty rough.

It's not the query he is talking about, it's the transference of the data set from the database to the application server. This is where the data will be processed if the logic is in the app server.

Re:Good or bad?

[l810c]

Don't be assinine. A modern production environment is running at least 100MBit Ethernet, if not 1GBit. A HUGE query, say 2500 characters, is 2.5KBytes. Gee, thats pretty rough.

What kind of shop are you working in? 2.5k is HUGE? Try 500+ users all hitting a database with queries and reports that average 100-200k or more. And that's With highly optimized queries and stored procedures.

If your using a web server or some other application server, you could have them connected via a separate backplane and g

Re:Good or bad?

[nzkoz]

You're talking about what Rod Johnson calls 'Persistence Logic' vs 'Business Logic'.

If the code is about rules of your application, stick it in your application code. i.e: "A User can only have 3 Widgets checked out at a time unless they're a Super-Widget member"

Alternatively if it's about data, it should be in the DB. i.e 'Users have a Type which can't be null unless the user is Inactive'.

I've seen both sides abusing this, one company I worked for refused to use stored procedures or Referential integ

Re:Good or bad?

[bwt]

You give the best reason to use stored procedures: to insure "data logic" is preserved. This comes in serveral forms:
- more complex constraints than foreign keys and check constraints
- enforcement of denormalizations
- enforcement of data movement (eg to a decision support system, archive, system interface, etc...)

Your statement that business logic should go in higher layers doesn't rule out SPs, because these are a reasonable choice to BE one of the higher layers.

In some cases, "business logic" can be configured as data in tables. In almost all cases it generally depends on business process state strored in tables. Depending on the amount of data required to make the business logic decision, it is sometimes superior to implement this as a stored procedure. Consider something that loops over all rows in a large data set, and makes a decision about each row. In a stored procedure, you might be rate limited by the speed of memory access and CPU. For the middle layer to implement it, you may have to pipe the large data set over the network, which might be orders of magnitude slower.

My experience is that overall system performance is usually a very strong driver of implementation strategy here. There is no reason SPs can't be just as maintainable as any other middle tier code. The only technical difference is that they run on the most centralized shared resource, but they have much faster data access. So it's a scalability for speed tradeoff.

In fact, often stored procedures are MORE maintainable, because reality in a heterogeneous enterprise environment is that no matter what you pick for your middle layer, somebody you have to integrate with won't be able to deal with it. Your spiffy java API for checking whether a series of transactions is properly ordered isn't callable from their COM object or vice versa. What ends up happening is you end up reimplementing solutions in multiple places, which is fine until the business rules change. Contrast this with the fact that just about every language supports a database connectivity that allows calling stored procudures. So their perl code logs into your database and calls your API -- easy.

Stored Procedures are a must

[Anonymous Coward]

Particularly for an application where you are returning large amounts of data, stored procedures hold a distinct advantage over dynamic SQL queries in that, if the SP is designed correctly, the database has pre-optimized the query plan at compile-time and runtime execution is therefore much faster. It also allows for underlying table structures to change without impacting your application logic.

Also, when it comes to long-term database maintainability, putting your database logic in stored procedures allows the db admins to get an accurate overview of what objects/tables are in use and which are no longer needed. At my company, where we have over 20 databases, this is an absolute must.

Generally speaking, I use dynamic SQL during initial development and move to stored procs for QA and production.

Re:Stored Procedures are a must

[Bedouin X]

SPs can also make switching web development platforms pretty easy if all of your special functions and dynamic queries are already coded.

I can't speak for PL/SQL but T-SQL is pretty simple to pick up and anyone who knows a 4th generation language should have no problems designing their own logic. It is probably best to have a database czar to manage the creation of objects on the database though. If the database guy is the bottleneck it's time to get a new database guy, that simple.

Re:Stored Procedures are a must

[Bedouin X]

I may be incorrect but views are still not as fast as SPs as SPs are compiled code. Also, you can't really pass arguments to views unless you're using dynamic SQL, which brings you back to square 1.

Re:Stored Procedures are a must

[RangerFish]

Views don't let you change the format of the data, all they allow you to do is to present data from multiple tables as if they were in one table.

SPs are good

[TommyTyker]

I actually miss them in my current job with mySQL. I used to like the way you could run a stored proc every X period to copy "live" data to "public" areas. Or, archive "public" data after "x" period of time. But then again, I am a micro$oft developer at heart, and all this Perl, CGI, Java, (even COBOL) on old RS6000 systems gives me a headache sometimes.

Postgres supports

[Sir_Real]

One of my favorite thing about postgres is it's support of plpythonu (python stored procedures) and the recently added java support.

Just define the function (in java or python) and SELECT it with whatever arguments you've designed it for. I don't know what the overhead involved is, but we used it more for convenience than anything else.

I was told basically, "the fewer the better" and "keep them confined to the innermost loops."

Two answers.

[MisterFancypants]

You're going to get two answers. You'll get the "always used stored procedures" answer from people who actually do real database work and the "never use stored procedures" from the people who hack small websites in PHP.

If I were a bit more of a tinfoil hat wearing man, I'd be Slashdot makes some of these "Ask Slashdot" topics up because the ensuing flamewar will cause more page hits than usual.

Re:Two answers.

[Anonymous Coward]

That's funny, I was going to make the comment but instead of "people who actually do real database work" I was going to say "people who think it's still 1980 and who think changing software should take an act of congress" and instead of "people who hack small websites in PHP" I was going to say "people who use dynamic languages to deliver high-volume applications in weeks instead of months".

I guess it's a matter of perspective.

Re:Two answers.

[TheSpoom]

From the perspective of someone who, at the risk of killing my ego, is in group two, why are stored procedures a good thing? I have a feeling that my later level database design courses in college will tell me, but I'd like to know.

Re:Two answers.

[Bedouin X]

They let you abstract data access procedures. It's kind of like asking "why are functions a good thing?"

Because a person who knows what the hell they are doing can write a good function (or stored procedure) containing what the hell they know so that a person who doesn't know a hell of a lot about what the first person knows but a lot about something else on a higher level can interface with that logic and get a result without having to be a guru.

Or something like that...

Re:Two answers.

[puppetman]

Actually, I'm a DBA who is responsible for about 20,000 lines of stored procedure code that I did not originally write, and I still think they are a bad idea.

Data constraints in the database, and appliation logic in the application.

Re:Two answers.

[puppetman]

It would be better if that logic was stored in stateless Session EJBs; one place for all that logic that can be accessed from anywhere.

Throw in Hibernate to get rid of the SQL-specifics of the database and reduce the OO-RDBMS nightmare.

I actually do more Java coding than database management (I've set it up so it's pretty much self managing); I wouldn't let it get all over the place, because I would be the person writing the session beans.

Re:Two answers.

[vegetasaiyajin]

Throw in Hibernate to get rid of the SQL-specifics of the database and reduce the OO-RDBMS nightmare.

Yes. Get rid of SQL specific and go to HQL specifics. I do not know why products like hibernate call themselves Object Relational mappers.
They are Object Tabular mappers. They can only map tables or views to objects, but not other types of relations such as queries.
For something closer to a true Object Relational mapper, try iBatis SQL Maps. Ironically, they say their product is not an object relation

Re:Two answers.

[Daniel Dvorkin]

If I were a bit more of a tinfoil hat wearing man, I'd be Slashdot makes some of these "Ask Slashdot" topics up because the ensuing flamewar will cause more page hits than usual.

Based on the sentence preceding the one I'm quoting, I'd say the main flamebait here is your post. How do people like me, who develop corporate LAMP sites with a great deal of "real database work" going on behind the scenes, fit into your neat little conception of who is and is not a real DB developer? Asshole.

Re:Two answers.

[Unoti]

...people like me, who develop corporate LAMP sites...

No offense, but doesn't this mean you put together web sites in PHP, as the grandparent suggested?

How to be a "real" DBA

[0x0d0a]

What offended me was his strong implication that the two -- PHP/MySQL programming and real DB development -- are mutually exclusive. It's an attitude that I see a lot on Slashdot, and it pisses me off.

Just:

*) Ensure that your company blows inordinate amounts of money on software with expensive support to cover your ass. It doesn't matter whether it works better, but you should be able to point at someone else to blame if something goes wrong. This also means that you don't actually have to understand the product, and can trust your DBMS vendor's salesmen for all your information, which will chew it up into bite-size pieces and spoon-feed it to you.

*) Act very self-important, talk down your nose to anyone that *doesn't* waste as much money as you, and treat your money-wasting as a mark of pride ("I have a $N million dollar budget this year!")

*) Place "real" before every class of software you work with, and vaguely insinuate that there are technical (especially reliability) failings in any cheaper product than the one you use. It worked marvelously for "real UNIX admins" for years.

*) Mention "Fortune X" frequently. For example "Fortune 500 DBAs with $5M budgets like me use *real* database software -- *we* can't afford downtime".

*) Avoid, if at all possible, knowing anything about software or computers other than database interfaces and possibly a scripting language or two. It's also good to choose a single OS, learn only that, and then slip occasional comments about how other OSes are unreliable.

*) Slowly begin slipping corporate acronyms into your speech. Use "ROI" whenever it's appropriate, and even when it isn't, to make it sound like other people are just slipshod and guessing numbers. "Enterprise class" is also good, since it implies, with only very vague factual requirements, that you work at a larger and more stodgy company that the person that you're trying to put down.

You'll be well on your way to doing "real" database work in no time. :-)

Version management

[Anonymous Coward]

What about version management of stored procedures? Yes I know it's possible, but it creates pain. Everyone must have their own copy of the DB otherwise an error by one developer modifying a SP breaks it for everyone, even if they have their own copy of the app. Scripts must be written to ensure that the latest SPs can be applied at the same time as code updates.

I don't like using SPs, but I think version management for me is the nail in the coffin.

Mark

Good.

[Pig Hogger]

They're double plus good, of course.

The idea of a database is to put the whole data-relation logic in the database, if only to insure atomicity of operations.

Because as soon as you rely on an external process to maintain data integrity, you're bound to fall prey to some sloppy programmer who does not understand the data relationships and will not properly maintain the data integrity.

At least, when you use stored procedures, you can concentrate the data integrity logic in only one place, which is easier to control and manage.

Re:Good.

[philipsblows]

I agree that they're good.

Aside from the virtues already espoused, implementing a database application within the database app means that as long as you can send a "SELECT ..." query to the database, you needn't re-implement your logic across multiple languages, platforms, etc.

What you get is a nice API for your data that is testable and perhaps even stable outside of the choices and requirements of the client app(s).

Re:Good.

[pHDNgell]

Because as soon as you rely on an external process to maintain data integrity, you're bound to fall prey to some sloppy programmer who does not understand the data relationships and will not properly maintain the data integrity.

This doesn't make sense. At my last job, I built what's essentially an ERP system. We did order capture and processing. An order was basically the following:

* billing address
* shipping address
* list of one or more line items
* list of one or more payment methods

Re:Good.

[Tony]

Don't mix up data integrity with business logic. You don't need a stored procedure to do all that; stored procedures make sure the billing address makes sense (like ensuring the zip code and city match).

Stored procedures are great when you have complex relationships between data than can't be defined by simple constraints. Any time you need to apply procedural logic to ensure data integrity, you need a stored procedure.

Taking the aggregate information and dealing with it is the job of the business logic, which is the middle tier. But the middle tier should be unable to insert data that is incorrect, no matter how poorly written the middle tier is.

Life without them...

[qurve]

...would be hell for me.

Most of the development I do at my job is Coldfusion+Fusebox with SQL Server on the backend (I don't care if you hate MS, don't bother knocking SQL Server) and stored procedures just make life easier. They're also handy in the instance that you may have multiple front-ends written in multiple languages accessing the same database in many cases. Making a change to the way data is returned is far easier to do in one stored procedure than in X number of front-ends. One of the main reasons we don't use mysql is because the stable versions don't have them.

Re:Life without them...

[joelgrimes]

Well, that's exactly my situation, and I've always avoided Stored Procs because if the query is written in the cf, then I can see the actual sql being passed to the server in the cf debug info while the page is being executed - and if there's a problem with it it's pretty easy to find.

When we use stored procs, I don't have that luxury - i just get the sql server error number and description, but not the query itself. Worse, if there's a problem that doesn't result in a db error (wrong data inserted, e.g.

Re:Life without them...

[XorNand]

If you're using MS SQL Server, use the SQL Profiler, chief. It's an app that's installed as part of the SQL client tools, along with Enterprise Admin and Query Analyzer. I use it as a sproc debugger all the time.

Stored Procedures vs adhoc queries in apps

[jfroebe]

If the applications are written in one of the various scripting languages, then this argument doesn't apply:

One major problem with enterprise applications is that when a problem is found in an adhoc query (poorly written, a bug with the DBMS, performance related, etc) then the application would normally have to be recompiled and pushed out to the entire enterprise (could be tens of thousands of computers to push to). This isn't desirable.

Moving the queries into stored procedures (where possible) allows you to correct the stored procedure at a central location and roll it back to the 'old' stored procedure if necessary with minimal effort.

A good rule of thumb: use stored procedures for compiled applications

Jason L. Froebe

Where I've used Stored Procedures:

[temojen]

If you have views set up to restrict which rows a user can select, but you don't want to restrict user creation (with a certain group, and with pre-defined rights) so that your webserver doesn't need administrative rights, a stored procedure fits the bill.

(PL/PGSQL example)

BEGIN WORK;

CREATE GROUP "salespeople";
CREATE GROUP "customers";

CREATE TABLE customerAddresses (
customername name references pg_users(usename) primary key,
address varchar(50)
)

GRANT SELECT ON customerAddresses TO GROUP "salespeop

Middle Server

[Cranx]

I almost always use a middle layered server that buffers calls between applications and the database. It's job is really to coordinate transactions, users and to perform tasks that go faster natively than through a database. A middle tier invariably makes it FAR simpler switching from one database engine to another than trying to port stored procedures.

Don't Like Them...

[puppetman]

Stored Procedures are hidden logic, and they're difficult to write, debug and maintain. In some databases, they are nearly impossible to debug without the equivilent of printf statements.

You also place more burden on the database, and lose the ability to scale horizontally. It's easier to add more web/app servers to you site than it is to add more databases.

Not sure I agree with the point about them being tied to a specific database; I don't think it's practical to write vendor-neutral SQL, so you will al

Re:Don't Like Them...

[Thomas Charron]

Spoken from the mouth of someone who doesnt have a clue..

WHY isnt it as easy to add more database servers then it is to add more web servers?

It's becouse you're not looking at the applications from the side of someone who manages data and databases.

It can work both ways. And in reality, it should work both ways..

Ever seen a database having rows locked by 100 different web servers becouse they DIDN'T use any form of stored procedures?

Depends

[Billobob]

If it can magically make 503 errors go away - yes if it does anything else - no

Is it that big a deal?

[Miniluv]

Stored procedures that execute the sme query as one sent over the normal client process usually execute faster. The DB server knows more about the query and the table structure, and therefor can optimize it better.
Having the queries in one place is also generally an advantage, and if the vast majority (or entirety) of your queries are in those stored procedures then migrating from one DB to another means NO messing with DB specific code (and every query ends up being DB specific if it does much of anything

Both

[3770]

1) I'd use both and I wouldn't use security as an argument to use stored procedures. Mere "mortals" should not have access to the database server at all. Just beware of SQL injection attacks (Google it if you don't know what that is).

2) Stored procedures aren't always the fastest because you can't do array inserts with stored procedures, for instance.

3) Queries are cached. So the second time a query is executed it won't be compiled. Just make sure that your queries are parameterized. Don't put your values in the query with string concatenation. Use parameters. Otherwise queries can't be cached. You will also be vulnerable to SQL injection attacks.

4) Use stored procedures when you will gain a clear performance advantage. You may want to try to implement it in your data tier first, and if that isn't fast enough, move it to a stored procedure.

5) Buy or make a code generator that will generate data tier code for you (and possibly other code).

6) As for database compatibility, if you implement it as stored procedures, you are screwed for sure. If you use normal SQL you are probably screwed anyway. Check out this chart [mimer.com] this chart for compatibility. And that only points out the parts of these databases that follow the standard. They do have plenty of non standard features as well. If you want to try your queries for standards compliance you can go here [mimer.com].

I have plenty more where that came from, but the wife needs the computer. Good luck though.

Stored Procedures often more harmful than helpful

[1010011010]

Implementing your application logic as stored procedures has some detrimental side effects.

1. SPs turn your database into an application server, centralizing things that needn't be, and raising load on that central machine.
2. SPs invite use of vendor-specific features, and therefore lock-in and loss of portability.
3. SPs are not typically amenable version control and are maintained outside the rest of your code base.
4. SPs represent "premature optimization." There may be a time and a place for SPs, but they are used a lot more than needed in many applications. For example, one application at my company has over 1,000 SPs, and quite a number are just wrappers for simple select statements.

Prepared statements and vendor-neutral SQL are the way to go for portability and controllability of the development process. Use SPs judiciously, if at all, and only when there's a highly compelling need to do so(e.g., order of magnitude speedup, etc).

Re:Stored Procedures often more harmful than helpf

[Osty]

SPs turn your database into an application server, centralizing things that needn't be, and raising load on that central machine.

When was the last time you saw the CPU sustain a high load on your database server? Database machines are constrained by I/O (disk and network) and memory, not by CPU in most cases. Therefore, your 8-way SQL box is sitting at 10% utilization. Why not get a bit more use out of it?

SPs invite use of vendor-specific features, and therefore lock-in and loss of portability.

As always, it depends

[Percy_Blakeney]

Do you have multiple applications accessing the database, or is it just the single web-based app? If you just have a single app, then it doesn't really matter much -- put it wherever you want. On the other hand, if you currently have or plan on eventually having multiple apps (web-based in PHP/ASP, desktop-based in C++/Java, etc...) then I'd create some stored procedures in the database. It will allow you to keep the database-oriented logic close to the data, thereby reducing how much duplication of near-identical code you'll need to write into the different apps.

In addition, depending on your situation, it shouldn't be too hard for the developers to learn how to write stored procedures for the database. Once you know one language, learning another isn't that hard. The developers might write some inefficient code at first, but they'll get better very quickly. Plus, it will give them a better idea of how the database really works and performs, improving their overall designs.

Of course, IANADBA (I am not a DBA), so take it with a grain of salt.

I like stored procedures

[Kentamanos]

Besides the optimization the DB might do on SP's as opposed to dynamically created SQL statements, SP's are nice from a security point of view.

You have to be extra careful with dynamic SQL due to SQL injection bugs that we all know about. This isn't really an issue when you're dealing with stored procedures that take defined data types as opposed to creating SQL on the fly based upon your data (which could have injected SQL).

Controlling which DB accounts can use what stored procedures is also handy mechanism for determining permissions. Stored procedures represent what all your application might do, so picking which DB connections (which have credentials) can access these is a nice place to control those permissions.

Granted, you can still do lots of stupid things to mess up security :).

Also, there are places where SP's are not really possible. Severely dynamic reports are a good example (assuming you allow that functionality in your application). There's definitely times when you HAVE to generate SQL on the fly. In any event, try to create a "data access layer" in your code and if you have to dynamically generate SQL, run all sorts of checks on it with regexp's etc to check for injection.

It depends (as always)

[chochos]

One case where I find that SP's can be useful for storing business logic is when your system will have different front ends using different technologies. For example, if you have a web frontend with PHP or Java but also have a rich client written in .NET or VB.
Of course you can also solve this using an additional tier (like an app server and use web services) and it could be easier to maintain, but if performance is too much of an issue, then you could go for SP's for some of the logic.
I don't think it has to be an all-or-nothing decision, though. You usually end up with some logic in the app code and sometimes some logic in SP's.

Stored procedures == Database API (a good thing)

[FlyerFanNC]

I'm an Oracle DBA, and I like creating packages of stored procedures and functions (and especially table functions) that represent an API of sorts to the database. This means that the application code doesn't care how the data is stored, and the DBA is free to rearrange the data for tuning purposes, without requiring any app changes (assuming the API remains constant).

In the past I've supported keeping more of the business logic in the database, but I no longer believe this is an optimal design. Now I keep business logic out of the database as much as possible and limit the stored code to enforcing data integrity and making the database look like a "black box" to the apps.

portability

[MORTAR_COMBAT!]

I've found that, say, writing an app with a lot of code in Oracle PL/SQL, using cursors, etc, means your app will only and forever support Oracle, without a whole lot of re-write and likely re-design.

So unless you like vendor tie-in... stay away from db-specific stored procedures.

Like any good tool, it can be used for evil

[Anonymous Coward]

A stored procedure can be a wonderful thing:

triggers that keep history tables

triggers that keep referencial integrity that a foreign key simply can't do

stored procedures that loop through large amounts of data that only return a small amount back

A stored procedure can be an evil, evil thing:

implementing complicated algorithms that can't be debugged

creating HTML

I've seen both sides. It's like asking if javascript is a good thing for web browsers.

John, who gets a 503 when trying to log on :-(

My take

[ZeroConcept]

Stored procedures are a performance optimization, consider the following scenario:

Retrieve the 20th page using a page size of 50 records for all the SKUs under a catalog (potentially millions total) for a specific user which could or not have visibility permissions for each SKU. Assume the security provided by the database is too coarse to fulfill the business requirements, therefore some set of rules must be evaluated to determine SKU visibility for a particular user.

That query would normally be very fast if implemented as a stored procedure because:
1) Only one round-trip is needed.
2) You don't have to move all the data to a middle-tier and then filter out information.
3) RDBMSes are usually faster at filtering data out (by using indexes, denormalization, etc.) that what a developer could code in a middle-tier to filter out information.
4) Most RDBMSes are very good at scheduling tasks, caching, managing memory, etc. The more you move logic away from it, the more you would have to implement it yourself.

You could send all the SQL statements to the database and achieve the same effect, but it might make debugging harder and you still have all the SQL logic in some place, only a different one.

On the flip side:
1) It's harder to write stored procedures than it is to write code in a managed language like Java or C# (thirty-line SELECT statements are not very intuitive).
2) Generally speaking, the compiler of a managed language does a better job at catching errors than a compiler for stored procedures, where a lot of the errors will be caught at runtime.
3) Stored procedures are not portable.

My advice is, if you are only using the RDBMS as a persistence device and your data size is not huge, avoid stored procedures and create some sort of middle-tier object model. Only when performance is a impediment, use stored procedures. You might as well use a hybrid approach, try to model as much as you can in the middle-tier and implement stored procedures for those tasks which are performance intensive.

I work with people that worship UML and patterns as well with RDBMS Gods that can plow through pages and pages of stored procedures without blinking. As much as I love ULM and patterns there are some tasks that must be done in the RDBMS for performance reasons, and tasks that are simply more maintainable when done in the middle-tier. Both approaches have advantages and disadvantages, the trick is to use the best approach according to the situation.

Re:My take

[Osty]

It's harder to write stored procedures than it is to write code in a managed language like Java or C# (thirty-line SELECT statements are not very intuitive).

This depends solely on your own discipline. I've seen quite a bit of horrible T-SQL code, but in 90% of the cases the worst part about it was not the logic. That could be followed, somewhat. It was the formatting. In the same way that you wouldn't write C code all on a single line (even though you could), you shouldn't do that with SQL code, ei

Advantages of stored procedures

[Orion Blastar]

As a programmer, I find that making a change to a query or table can cause me rewriting code in every application I've developed.

With stored procedures, I just refence the stored procedure name and leave the query tinkering to a DBA.

The only thing that I have to make changes for is when the DBA changes a column name in a table or a parameter for the stored procedure. Also when a stored procedure is in use, and it needs to be changed, I have to make the program use a second procedure name and switch procedure names each change, because if the procedure is changed as my program is running, it will break if a parameter is added or removed.

I had to work on a docket calendar program for a law firm and we used stored procedures with the reports. The managers and lawyers were always adding things to the reports which needed changes to the stored procedures. We eventually maxed out the max number of tables allowed, and each stored procedure was five pages long with if else statements because of all the things that the managers and lawyers wanted.

Using regular queries would not work because of the flexability that T-SQL had to meet the law firm's demand. MySQL would not have cut it. The reports were in Crystal Reports.

(Flame Bait) SP = bad, RDBMS = bad

[Trinition]

I'm asking for it, aren't I?

Personally, I think way too much stuifdf is stored in RDBMSs. I work as a Java programmer in a non-IT industry, and everyone is happy-go-lucky about making every object map to a table. But its a huge impeadance mismatch. We have layers of DTO, DAO, VO, etc. in the way.

I think the world would be a better place if most of the typical day-to-day was stored in an object-oriented, transparent database, and the relational database was left for storing things where an RBMS really shines (arbitrary relational queries, etc.).

Once you've gone the way of an OOBMS, you have objects, so naturally all of your logic stays in your objects. The fact that your objects happen to be persisted for you is irrelavent. All you car eis that you have your objects.

Depends on your design methodology...

[JohnA]

Personally, I find Stored Procedures to be a very difficult thing to manage in the long term of software development.

If you are designing a web application, then I find it much more maintainable to utilize DAO interfaces & impls [sun.com] since this allows you to make changes that might be necessary should you experience an unexpected change in your environment.

Need to move from MySQL to Oracle? Simply override any db-specific code from your ANSI Impl, and go.

Although if there is no chance of an environment change, stored procedures become much more attractive.

Good for what they're for; crap otherwise

[sparks]

Generally a database should be where the data is kept. Nothing else. If there is some functionality which is absolutely 100% to do with how the data is stored, then it *might* make sense to use a stored procedure for it. Better that than filling your actual business logic with the minutae of a particular DBM.

On the other hand, you should never, ever put actual application logic in a stored procedure. The reasons are several. The most important is that stored procedure languages are all, to a greater or lesser extent, crap. This comment will cause me to be flamed to death by those who only know PL/SQL etc, but the fact is it's true. They are not general-purpose programming languages.

Sure, you might not RIGHT NOW want to fork off sendmail from your application, but some day you might. Or, horror of horrors, maybe you'd like to write directly to a system file? Or use a neat SNMP library you found? Although there are twisted, hacker-like ways to do these things in most DBMs they are hardly the model of reliability or professionalism. [1]

Secondly, they tie you in at a fundamental level to a particular database vendor. Database software is generally neither Free nor free. They want you to put your business logic in their stored procedure language because it will only run in their database products. Lock in is bad. OK, you'll be locked in whatever you do, but I'd rather be locked into Java or Python than PL/SQL.

Thirdly, you are losing control of your application's performance. You have very little control over how the code will be optimised or run.

Fourthly, you are breaking abstraction. It is very, very hard to write stored procedures which aren't entirely dominated by the structure of the underlying database.

Finally, assuming you probably will have to have a middle layer between the client and the database anyway, it's a bad idea from a maintainability point of view to bits of the same functionality among your layers.

[1] have you ever written a cron job to run a query to dump a table to a file to be parsed by a Perl script to send an email? You might be an Oracle Portal user.

Re:Good for what they're for; crap otherwise

[NineNine]

Generally a database should be where the data is kept. Nothing else.

You know nothing about database-backed application development. I would recommend that you find some people who know what they are talking about and learn from them. Find an old Oracle or DB2 DBA or developer and talk to him. You know nothing about what a database is.

Thirdly, you are losing control of your application's performance. You have very little control over how the code will be optimised or run.

You've never used a databa

Re:Good for what they're for; crap otherwise

[sql*kitten]

RDBMS can't be more granular than the language it's written in, it can only be less.

You're right, that's why JVMs don't have garbage collection or strong typing, because JVMs are written in C, and C doesn't.

Oh, wait. It looks like software can have features that its host language doesn't. How 'bout that?

Stored Procedures vs. Not

[glh]

In my experience, this is a difficult question to answer. There are many factors that you should consider in making a decision to use all stored procedures or not.

First, every business has different needs. Every software development group is also different in what they can or cannot provide. There are camps on both sides- many people in the database discipline will say "put everything in the database" while hard code developers will sometimes opt for queries in code.

Some considerations:

1) Consider the needs of your application. Is there a good chance your application will need to talk to another database platform or backend at some point in the future? This could be an argument for not using stored procedures. AS far as centralizing business logic goes, that can be done in just about any tier.

2) Where is your current bottleneck? How possible will it be to scale out your database server? If you are in a web farm scenario, your database server may be under significant load. Putting more logic on the database server can be a lot more expensive- it is typcially a lot cheaper to sacrifice performance on the backend for scalability. In other words, if you can keep your database server relatively load-free you can always add more web servers. I currently support a site that has over 2000 concurrent users at any given time, and currently our DB is the biggest bottleneck. It is a lot cheaper to cluster web servers than DB servers, since the DB is centralized and web servers can be duplicated easily.

3) Consider the experience of your staff and the culture of your IT department. If you have a lot of developers/dba's that are used to programming with stored procedures, and management is used to that paradigm, it may be difficult to change architectures without a compelling reason. "If it ain't broke don't fix it".

I'm sure there are other considerations, but those are probably the most three important ones I can think of right now.

Meeting

[cubicledrone]

DBA: "I think we should use more stored procedures"

Project Manager: "That would be consistent with our n-tiered strategy"

Programmer: "But all of our business rules are in object libraries"

Division Manager: "Could I get the ranch dressing? Thanks."

DBA: "The right way is to put business logic in stored procedures. It's faster"

Project Manager: "We're standardizing on faster programs across the enterprise"

Programmer: "But we'll have to re-write the entire system!"

Division Manager: "Pass the pepper. Thanks."

DBA: "Oh, that's alright. We were going to upgrade the servers too. We can finish both jobs at the same time"

Programmer: "We're upgrading the hardware too?! So basically we're building the entire company from nothing again, right?"

Project Manager: "You're not being much of a team player"

DBA: "The new system will be more robust and have new features"

Division Manager: "Lets get the system built as soon as possible. We're laying off the entire division in a few weeks"

Programmer: "WHAT?! I just signed a new car lease!!"

Division Manager: "Pass the croutons please"

Re:Meeting

[mitchy]

Project Manager: "So we need to start this project off as soon as possible, as this will become the foundation of several different applications."

Programmer: "Wow, kewl, we should do it all in Perl."

DBA: "We will definitely need to have a robust database if it is to power all these different apps."

Programmer: "MySQL rules man! Besides, it is free."

Division Manager: "Hmm, free is good. Can you manage the data in MySQL?"

DBA: "Well, actually MySQL doesn't do..."

Programmer: (interrupts DBA) "We don't need any of that relational integrity crap, that's just marketroid speak for Oracle, dude! All we need are a bunch of tables. I can wrap all of the SQL in a Perl package."

DBA: "Then you will have dirty data, and we can only write apps in Perl if we have to use that library for access. This is really putting all of our eggs in one..."

Project Manager: (interrupts DBA) "So this is free, right? I like free. My stepson talks about Perl, so it must be a totally hip language." (snorts)

Division Manager: "So all of the database logic is now going to be written in Perl, right?"

Programmer: "Absolutely! The database is only there to store data. We can check everything in the application. It will all work perfectly, because I am 3l337."

DBA: "But that's like just using flatfiles, how are you going to..."

Division Manager: (interrupts DBA) "Hey, if this is free, and you can do all the code, then we can cut half of our database specialists to save costs. That is a great idea."

DBA: "You can do that, it is your company and all, but without triggers and some sort of stored procedures..."

Programmer: "What's a stored procedure? Trigger? Isn't that a horse from the 60s?"

Project Manager: "Heh, that's funny."

DBA: "And if the next application needs to be done in Visual Basic, or some other language? Then you will have to write the whole thing over again because..."

Programmer: "Look, this is easy, me and my buddies from the high school can pound this out over the weekend."

Division Manager: "Now THAT's team spirit!"

DBA: "But how can you ensure consistency of data? Your database cannot even enforce minimal compliance of..."

Programmer: "Look, grumpy dude, I don't make mistakes. My code would never create dirty data, and besides, I'm 3l337."

Project Manager: "Wow, my budget is going to look great after this."

DBA: (leaves room, goes home and beats dog)

A good read...

[Skim123]

A good discussion on this topic can be found at this blog entry: Don't use stored procedures yet? Must be suffering from NIHS (Not Invented Here Syndrome) [asp.net]. The content of the blog post (by Rob Howard [asp.net], a former Microsoft employee who most definitely knows his stuff) is definitely a good read, but the real gems are in the comments, which there are plenty. There's an equally interesting thread on this discussion by another blogger with his entry called Stored Procedures are Bad, M'Kay? [asp.net]. Both worth reading, if nothing else for the comments.

the myth of portability

[jonnosan]

having your apps portable across DB's is one of those things people always think is important but in my experience it's a total crock for an app that is going to be installed in a single location, i.e. a commerce website or an enterprise specific app.

You are just as likely to want to change your middle tier as your DB. More likely, in my opinion. If your business logic is in sprocs, then it's as easy to call from Java as it is from .Net.

Stored procedures not complied, but separation def

[Grimace1975]

Procedural code and SQL code are two separate programming language processes. The first directs the computer from a singular point of view. For instance "do this, then that, then go here and check this. etc.". And the other deals with groups of items. such as "everyone wearing blue shirts go to room 103", or "we don't need these anymore".

As far as intermixing these code bases, your procedural business logic and data business logic should be split when it makes sense. The database is optimized for merging and managing sets of data, and procedural code is good for binding this to a functional form. The business logic should be split into these two zones and implemented appropriately. It would be inappropriate to return a set from a database then loop through that set searching for some name or value. And at the same time it would be unwise to return two sets and join them in your code. With experience it seams cleaner to maintain these two zones of code. This doesn't mean that you need to use stored procedures though.

As far as stored procedures, they are a convenient way of separating these two types of languages, another way is to in place the Sql code into your procedural code, but it seams advisable to centralize this type of code in one place for visibility, and manageability. If stored procedures are not available or undesirable, then using classes or function that are located in some central, or locatable place, is recommended.

As far as for speed, implementing the data and logic in the appropriate place will speed your application, but stored procedures will not in there own right speed anything up. At least in MsSql server, stored procedures are not precompiled. They exist as plain text, just like issued queries. They do however get their own query cache, separate for the issued query cache, which could be of a little assistance.

Anyways. I am over talking about this. Take it, as u wants it.
-- Grimace1975

Good, like most things if they are used properly.

[IpSo_]

It's a slippery slope...

I used to work for a large web hosting company that wrote there billing software frontend in Delphi, and kept all the business logic in stored procedures on MS-SQL. It got so bad, they even ended up having a stored procedure that generated HTML/Text invoices for customers! Ever tried doing text layout in a stored procedure? It was absolutely nuts, but once they had started putting all the business logic (and much more) in stored procedures, its hard to stop without "splitting the code-base".

They were also scared to upgrade from MS-SQL 6.0 to anything newer for fear of it breaking their stored procedures. They tried at least once and failed miserably. As far as I know, to this day, they are still running MS-SQL 6.0.

This whole issue basically put a strangle hold on the company, it took forever to "innovate" and they eventually got bought out. The new parent company has spent over a year trying to migrate away from this "stored procedure" mess.

I think stored procedures are good, but only in very specific circumstances. If you design and code your application properly, there is usually very little need to start down the slippery slope of stored procedures.

Stored procedures are for data integrity...

[aralin]

We use stored procedures for data integrity. You get only SELECT right on any table at best. If you want to do any DELETE/UPDATE/INSERT you have to call a stored procedure that ensures the data integrity. Basically we use the PL/SQL to define operations that are permited on that particular data model. It has several advantages.

1. If the database schema changes, you don't need to rewrite the application
2. Your data are secure and the model is always in stable state
3. The operations on the data model are well defined and well optimized. Nobody is reinventing a wheel.

Of course, any business logic has to go to your applications.

Data Model Abstraction

[FritzAtlanta]

I am have been an Oracle DBA/Developer for more than 10 years. I also got into Java and more hardcore OO programming around 1996ish. In my opinion, from a design and implementation standpoint, the use of stored procedures is extremeley useful to provide an abstraction layer for data access. This is valuable because the stored proc can handle the renormalization of tabular data to best fit whatever object model is used in the application. Simplifies the application and protects it from necessary database changes (normalization/denormalization) in terms of how the object is implemented into tables. The great benefit of this is that it lets dbas and other database developers implement the data model independently of the application! This forces the relational data model design to resources that can program it most efficiently. Obviously there will be a small number of cases where this is not a viable approach to follow, but in general, it is a huge time saver, risk reducer, and productivity enhancer.

keeping the logic in the code vs. performance

[flacco]

my instinct is to put the logic in one place, and since i need that logic in the code, my preference is to centralize it there instead of duplicating it in the db. this extends even to operations that databases are traditionally used for, like aggregating data.

i'm quite unsure of myself about this, though. at the moment i'm working on a budgeting application, and both performance and productivity are becoming an issue.

example: to aggregate budgets over a time period, i retrieve the budget objects for each budget period individually, and accumulate the aggregate data in code. this takes quite a bit of extra coding, and execution time is quite slow, however; doing aggregation queries in the db would certainly give better performance, and it would be a lot easier to slap together some queries instead of writing all that code.

so, the way i look at it, it comes down to a question of science vs. engineering. the scientific impulse is to adhere to the theories of keeping logic in one place, and respecting the objects' ownership of their data. the engineering impulse is to use the technique which is faster and easier to implement.

i guess at heart i'm more of a scientist than an engineer.

i'd love to hear others' takes on this question, btw.

Great for *processing* data

[PizzaFace]

I understand some people use SPs for everything, as a security measure. I'm not that paranoid.

Stored procs are great, though, for processing lots of data on the server, without wasting resources (network bandwidth, client memory and CPU) sending the data to a client for processing and then posting results back to the database. Even if you have to do record-by-record processing instead of set operations, it's much more efficient to use a cursor on the server than a loop through a resultset on the client.

Stored procs are good at encapsulating a series of operations done periodically, such as end-of-month processing. In some databases you can define an event on the server that will automatically fire the SP. With such an event on the server rather than on a client machine, you don't need to worry about whether the correct client is running and connected when the event is supposed to fire.

Complex reports often require more data crunching that just a single query. I use an SP to process the data and leave the results in a temporary table. My client program runs the SP, then reports with a simple SELECT on the newly generated result table.

Depends on the project and the team.

[tunesmith]

I regularly freelance for projects where we have one template programmer, one middleware programmer (me), and one DBA. With the same team, we've tried projects with stored procedures and without. In general we've moved away from sprocs.

The first time we tried sprocs, we basically treated them as functions. I would pass in a bunch of arguments (db column contents) to sprocs that would insert a new Activity, for instance. This got old very fast. It was much faster for me to write the business object that would insert itself from the middleware layer, than it was to wait for the DBA to create the sproc, after which I would have to create a middleware layer to the sproc anyway. It also didn't make financial sense for the client, because DBA's usually charge a higher hourly rate than middleware programmers.

The other sprocs were ones where I would supply several search criteria to a sproc (basically portions of a where clause), where he would assemble it into a sql query and then return the result set back to me. That was a bit more useful, but ended up kind of silly too, because it wasn't efficient to involve the DBA in actual application logic - we kept on having to go back to him whenever someone wanted to add a new dropdown to the search form.

If you're been an intermediate programmer, you've painted yourself in the hellacious corner of trying to dynamically generate a sql query that may or may not join across multiple tables. It ended up being a lot easier for the DBA to simply create a view for each family of search queries. Then I'd assemble the sql query on the middleware layer. Easy then, because I'd never have to worry about dynamic joins - the view would already have the joins, and I'd only be querying against the single view. And if there was a query change, I wouldn't have to involve him unless is actually required adding new columns to the view.

Right now the complexity of our projects don't require the remaining cases where a sproc would really make a huge positive difference. One such case would be a multi-step atomic transaction where we were worried about performance. A sproc would be perfect for that. But in general you can do just fine with inserting into tables and selecting from views without having to deal with the cost of having a significant amount of your project in sprocs.

Finally, an important tiebreaker between having logic in sprocs and in middleware is a pragmatic one - system resources. If you're making a lot of changes, you're going to be dealing with source code management - trunks, branches, and multiple staging installations. It's much easier to do this with your code repository than your database. With ost companies I've worked with, it's a lot easier to set up a new vhost for a code installation than it is to set up a third oracle installation. If you have a lot of quick changes to make, it's easier to make them in the codebase.

Beyond that though, it really depends on the team. If we had a full-time DBA rather than our 10-hour/week guy, and a less competent middleware programmer than myself, and a project with more fixed requirements, then we might defer more to sprocs. But our DBA is swamped, our projects tend to have ever-changing scopes, and I'm quite comfortable with MVC and keeping the control layer thin, to be able to respond quickly to the scope changes without having to majorly rework business objects on the model layer. It works well for us - and these are for large scale bank intranets, not simple little webapp one-offs.

Many people that know too many buzzwords think that "the business object layer" by definition MEANS the database and sprocs. It doesn't have to. It can just as easily mean the Model layer of an MVC middleware layer. With my work style, it's faster to leave it there and then use the database for storage and data-level calculations that can be embedded in the queries themselves.

Stored Procedures Solve a Non-Existent Problem

[smack.addict]

Stored procedures solve a problem that predates languages like Java and C#. Once upon a time, you built client server apps using a client built in C, C++, or (god help you) PowerBuilder or VB. Database code either had to go in the user interface or the database. There was no other choice. So, to centralize business logic, stored procedures came about and people began placing the logic there.

In a three-tier or web architecture, stored procedures have no place. Centralize your business logic in business objects on the server. This makes your application independent of any underlying table structure or persistence mechanism. You can get the speed of stored procs by using prepared SQL in your database mapping code. These days, you can use mapping tools like JDO to avoid any database mapping code.

Where stored procs still have a place is inadministrative functionality, such as background batch processing. That's it.

Re:an important question..

[firebus]

why are you posting if you don't have an answer?
you are only giving me a headache.

Re:an important question..

[DaveJay]

A bit trollish, but I'll respond: perhaps he's actually smart enough to seek outside opinions, even though he thinks he knows the answer.

That, or he's preparing some kind of presentation/paper to justify the use of stored procs to a boss who doesn't believe in them (or vice versa), and is seeking real-world examples to bolster his point.

Just a couple of possibilities.

Re:What's the point of this question?

[bwalling]

There are no real negatives to stored procedures from a development standpoint.

Other than the fact that they suck, there are no negatives! Seriously though, the database is for data, other languages are for business logic. Why split your business logic between your class library and your database server? Keep the logic in one place. Isn't that why you created the class library in the first place?

Re:What's the point of this question?

[arkanes]

An RDMBS (as opposed to just a database) is actually for manipulating data, not just storing it. Otherwise you'd just use flatfile for everything and implement all the relational logic in your app code. The database can execute stored procs far, far faster than your app code can perform the same functions. Using database side stored procs gives you the exact same advantages as a class library with additional performance and security options. There's no loss. Why not use them?

Re:What's the point of this question?

[innosent]

Thank you, just to add a bit:

Would you want to send 5 million rows to the application just to check a few fields in each of them, and how they relate to records before and after them? Hell no, sending 5 million rows uses a ton of bandwidth, even for small row sizes. Also, SQL is a language for set operations, while most (99%, Lisp/etc are other 1%) application languages are designed around a single value, not a set of values. For example:
To check if array a[50000] is a subset of array b[2000000], in most languages, you must somehow iterate through both arrays (sorting would help, if possible) and see if each value in a (all 50000 of them) also exists somewhere in the 2 million values for b.
In SQL, something like SELECT COUNT(*) FROM a WHERE a.key NOT IN (SELECT key FROM b) would do the same thing, only much faster than downloading 2,050,000 rows and comparing them. Each vendor handles internal set operations differently, but all of them optimize their internal data structures and abilities to do exactly these types of operations.

Re:An example of good

[imroy]

I've always wondered about the real speed difference between MySQL and PostgreSQL. You said that you were able to take out a lot of code because PostgreSQL was to do more fo the checks for you, and the stored procedures handled the remaining high-level details. Back in the day, the MySQL developers publically trashed the idea of transactions, instead recommending people emulate similar functionality in their client-side code. And that they did.

Now, consider two similar DB-based applications. One is connecting to MySQL with all sorts of client-side code to emulate transactions and referential integrity (foreign keys, etc). The other is connecting to PostgreSQL where most of the work is done in the DB engine. Foreign keys are used, as are transactions and stored procedures. Thus the number of app->db requests will be far fewer than the client-heavy MySQL app. Now, even though the MySQL server may beat the PostgreSQL server in raw speed from simple selects, how would that change when you factor in the extra work that the MySQL app has to do? And what about more complex queries? I've found that PostgreSQL can handle complex queries (sub-selects, unions, aggregrates, etc) very well. It's much more efficient to do a single big multi-row query than lots of single-row queries. It probably takes longer for the programmer to write the query, but once that's done the optimizer gobbles it up and produces a plan that gives you all your data at once. Very nice.

I'd bet that all or most of the MySQL/PostgreSQL benchmarks don't take into account the very different ways these two DB's are used in real apps. MySQL is traditionally used as a simple data store with an SQL interface. Whereas PostgreSQL can become a more intergral part of the application, with stored procedures, triggers, foreign keys, etc.

Re:Square hole, round peg..

[Chazmyrr]

It's just you. I use stored procedures to:

1)Provide a consistant interface to applications regardless of changes to the underlying database structure.
2)Enforce row level security.
3)Enforce data integrity.

Trees in relational tables without recursion is a problem that has been solved for years. http://www.sqlteam.com/item.asp?ItemID=8866 [sqlteam.com] It is trivial to implement nested sets with a few lines of SQL and after the tree is constructed, a simple SELECT will give you children, descendants, or ancestors.