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."

Comments

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

[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'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

[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 key for the large data pieces. Or if you add an index that makes a different SELECT statement faster than the current.

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

[AnyLoveIsGoodLove]

Ahhh business logic at the database... why should you do it? Let's start with some open source examples: Compiere. This is one of the most successfuly opensource products based on Oracle in the market place.... but did you ever read the mailing list for database independance? Yup it's a year's worth of mail, but no significant movement. I honestly don't thing there will be. Think about the steps it would take to change:

1) Develop Business Logic Tier (Jboss like tier). Any ever do this? Yup. Lot's of effort.
2) Convert all SQL to standard SQL (hmmm much easier to start this way. This ain't easy either)
3) Test againts all major DBs

Hmmm sounds like a lot of work. So class.. the lesson is:

Do you want to have an OPEN application that is platform independant? Do you want a product that can participate in the enterprise? Do you want to avoid the DB / OS religion wars?

Great then seperate your logic from your data from presentation tiers.. it's simple design and I've made a lot of money off of other people's lazy code

(On the other hand if you have a small app that no one out side your department will use, break out the MS Access. Funny story, I know a federal government agency that has 1200 (yes you read that correctly) Access dbs in production... BTW.. they fear centralization... surprise...

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

[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 queries while I've got someone on the phone who is hitting 'refresh' to see the changes, then I am going to do it in the application where I have better access.

In my experience, which is not the be-all and end-all, but is 6 years or real-world day to day web app programming...(judge that as you will) 90% of the queries are total 'junk' anyway.

I don't really see the need to put things like 'select * from story where storynum=956' in a stored procedure. Looking through most of my code, I would guess that at least 90% of my queries are similar to that.

I've got other routines that do things like aggregate hundreds of thousands of pieces of data- in that case I use stored procedures because they make the difference between 6 minutes and 60 minutes. (but then again, if I were a real genius, maybe it would only take 6 seconds...but then my server might catch on fire or something) but most of my work on the web is pretty straight-forward.

On the other hand, I used to work with a guy who was always 'trying to save cycles'. Like he didn't want the server to have to work too hard. He would spend days trying to cut something down from 500 milliseconds, to 20 milliseconds. Yes, I agree that is all well and good (depending on the frequency of the query...in the 500/20 example, we're talking about a DAILY routine). But we've upgraded servers twice since that time, and that 500 millisecond query IS a 20 millisecond query now.

Not to mention the fact that the server sits at 1% of processor capacity all the time. I hate to say it, but we're living in a fairly hardware-rich environment. Most (I said most) people don't need to optimize the crap out of something, because as the original story said, users are waiting on their connection- not my database access.

Okay- it all sounds like me making excuses for being a lazy coder. But man, you should see all of my pretty comments!

Last thing...where I work, we pretty much upgrade hardware based on TIME, not on NEED- it is one of those 'spend it or lose it' situations. So, saying that if I had optimized the code, I wouldn't have needed to upgrade is a moot point- we would have upgraded anyway.

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

[(negative video)]

Compiled SQL is faster. This is purely a performance enhancement.

One that can be critical for hierarchical data. A stored procedure will save one round trip from the app to the DBMS for each level of hierarchy that has to be traversed.

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.

Reminds me of something I read: "We have an n-1 tier approach. No client, just multiple layers of server." ;-)

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

[Crash6-24]

I use stored procedures for security and linited access. However, that can be carried to extremes. I was called in to diagnose why a project in production suddenly quit working. A developer had come up with a scheme where every stored procedure would verify the security level of the caller every time the stored procedure was called. The scheme worked fine when installed in a few peripheral procedures but crashed the application when installed everywhere. The problem was that the checking was single-threaded and locked a table every time the security was checked. Parallel operations became serial. Users who were feeding in information from hand-held scanners timed out. The coded-in-to-the-procedure security was removed.
I guess the lesson learned was to review the design by DBAs before implementing, and test under load before going in to production.

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 format for the database etc. With parameters, it just drops out. Same goes for null values etc.

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.

[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.

[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.

[PopCulture]

... and thats different from a programmer responsible for 20,000 lines of java/C++ code that they did not originally write because of how?

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.

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
* detail on how much each method pays

[more stuff]

OK, make me a stored procedure that can take this kind of structure all at once and do all the appropriate inserts/updates.

I had a design that made it quite easy to add new concepts to the order object and store, retrieve, and update it atomically without any one person having to know about all of the elements that might make up an order.

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

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.

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 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.

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

Separation of Duties

[Cornelius42]

If your project is large enough, you separate the development, and allow for your DB admins to create stored procedures.

If your project is small, one programmer, then there is no need to separate duties.

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

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.

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.

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.

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.

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 database, obviously. I'll argue that there are zero high level programming languages that allow the granulaity of control that a real RDBMS allows.

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.

You've never worked for a real company, have you? Front end app languages change more often than the weather, and in one company, many are usually used at the same time. One department uses VB, another uses Oracle Forms, another uses Java, another uses Perl. Companies don't change databases willy-nilly.

Seriously, I think that you should probably work with some real database people before you try building any apps on your own. You have a tremendous amount to learn.

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

[dubl-u]

You know nothing about database-backed application development. [...] You've never used a database, obviously. [...] You've never worked for a real company, have you?

Say, you may not get out much, but there are other ways to build an app besides on top of an SQL server. You'll note that Doom 3, Microsoft Word, and the Google search engine don't use them. You'll concede that those are all real apps produced by real companies, right?

I'll argue that there are zero high level programming languages that allow the granulaity of control that a real RDBMS allows.

A little reminder here: databases aren't made from magic pixie dust; they're written in other languages. An RDBMS can't be more granular than the language it's written in, it can only be less. Indeed, it probably should be less, as its purpose is to make development simpler, easier.

The only question here is when it's more cost-effective to use an RDBMS on a given project. Back before DBMSes existed, people wrote a lot of low-level code to get things done. But eventually people pulled some of that code into common libraries for managing records, and the DBMS was born. If you're doing the kinds of things that Oracle expects in a way that Oracle expects, using an Oracle server may save you a lot of money and development time.

But if you're Google, an off-the-shelf DBMS is a big waste of time. Oracle's a great general-purpose engine, but Google engineers don't give a damn about the general case. They are building an utterly specific application; the generality of a database server mainly gets in their way.

So ease off on the "If you don't use a database you must be an ignorant fool," crap, ok? The parent poster was clearly inflamatory, but he describes one perfectly reasonable way to build successful applications. His isn't the only way, and neither is yours.

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

[NineNine]

Say, you may not get out much, but there are other ways to build an app besides on top of an SQL server. You'll note that Doom 3, Microsoft Word, and the Google search engine don't use them. You'll concede that those are all real apps produced by real companies, right?

Notice that I said app development with a database back end. I know that not every app uses a database. Not by a long shot.


A little reminder here: databases aren't made from magic pixie dust; they're written in other languages. An RDBMS can't be more granular than the language it's written in, it can only be less. Indeed, it probably should be less, as its purpose is to make development simpler, easier.

Oh, I understand that, but Oracle & DB2 & other "real" RDBMS' aren't written in high level laguages. With Oracle, at least (my experience), you can control queries and other processes down to the memory paging level. You can't control where *exactly* you put memory with C++ or Java. They have pointers, granted, but that's nothing compared to the fine tuning you can do in Oracle (which I've only *seen* done... I was a developer, not a DBA).

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?

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 generally not have any ill effects on the network at large. If, however, you are using some sort of client side front end(Either application or something linke Crystal Reports) then it is definately better to offload the processing to the db server.

Easy to learn isn't an argument for or against a technology. That aside, relatively portable is logically equivalent to completely fucking unportable when translated from Marketing into English.

That may be true for a lot of technolgies, but I've been working with SQL(DB2, Oracle, MS, MySQL, Postgres) for 15 years and with a few exceptions it is a very portable language. I worked on it on OS/390's, HP/UX, Solaries, Linux and Windows.

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.

Re:Good or bad?

[Christopher Cashell]

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.

Erm. . . you must be dealing with some mighty tiny databases, tables, fields, and queries, if you think 2500 characters is a huge query. In this day and age, where more and more data is being stored in databases, 2500 characters is nothing.

Or, are you refering to the SQL query itself, as opposed to the result data it returns? Sure, a query might only be 2500 characters, but remember, often the data can be processed locally and reduced prior to transfer, and *that* can have a huge impact.

Depending on your application and requirements, I've seen situations where the query has to return multiple megabytes of data, which are then processed by the application into a usable format.

I'm personally familiar with a situation where a DBA was able to move that processing from the application to the database in the form of a stored procedure, reducing the bandwidth from an average of 6MB to about 4KB.

Oh, yeah, and the time it took for the database to process it directly was about 8% of the time required to transfer all of the data to the application, and then process it there.

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 parameter. You won't know that till it runs.

5) No unit testing frameworks.

6) No cohesive way to examine code flow. What you end up with is a mountain of code snippets scattered all across your database. Cross your fingers and hope each step gets excuted properly.

7) No real debugger. No stepping through the code, no breakpoints, no watches.

8) Most commercial databases charge you per CPU. This means your CPU cycles are best used to keep data integrity, process queries and return recordsets. Most middle tiers are not licensed on a per CPU basis so you can afford to throw a lot of CPU cycles into executing code.

9) Last but not least you can couple your middle tier using a high speed interlink so there is no real need to use SPs.

Feel free to add to the list. SPs are not good.

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 need to reduce the size of a retrieved dataset before it hits the network and a simple SELECT won't do. Other than that I avoid stored procedures like the plague.

DBA's in the organisations I've worked in have generally had an unrealistic view of their importance and technical abilities. They get away with a lot more than they should mainly because they are gatekeeper to the organisation's "crown jewels", the data, and because database management is a mess that most developers try to avoid.

I've found from a practical standpoint it's best to keep the business logic out of the database and treat the database as a simple data store. Your mileage may vary.

---

It's wrong that an intellectual property creator should not be rewarded for their work.
It's equally wrong that an IP creator should be rewarded too many times for the one piece of work, for exactly the same reasons.
Reform IP law and stop the M$/RIAA abuse.

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.

Portability for the sake of portability is a waste of time. More importantly, given that every vendor has non-standard extensions, and the SQL definitions (SQL-92, SQL-99) don't go far enough, you'll find that you almost always need to use some vendor-specific features, whether you're writing ad-hoc queries or stored procedures. At least with stored procedures, you only have to change it in one place when you migrate, rather than changing it all over your code. Tell me, how do you add an AUTOINCREMENT/IDENTITY/auto-numbering column to a table in vendor-neutral SQL?

SPs are not typically amenable version control and are maintained outside the rest of your code base.

Says you. In my team, all of our stored procedures are stored in the same code tree as everything else, controlled by the same source control. We take it even a step further, and check in all of our database object generation code (tables, keys, indexes, triggers, etc). So, just because you don't track your stored procedures in a source control system doesn't mean it can't be done.

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.

You're assuming that the speed benefit from stored procedures typically comes from the fact that they're compiled. While that's true, it's also not much of a benefit (you might win 50-100ms per query, yay!). No, what's more important is that your queries are centralized, so that you can optimize them more easily. What's easier to optimize: A single query to <insert operation here> in a stored procedure, with the procedure called by 100 different methods; or 100 different queries to <insert operation here> across all of your code? Yeah, sure, you can centralize that as well, but what's stopping a developer from writing his own query rather than using the optimized one you've already provided for him? With stored procedures, you can deny access to individual tables and force that person to use your stored procedure.

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 in the PL/SQL layer is that it helps make a very clean separation between the different tiers of the application.

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.

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

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.), I don't have any feedback at all.

If I knew a way around that one little problem, i'd move to stored procedures immediately.

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.

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?

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.

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.

security in depth

[moocat2]

Since the focal point is the webserver, shouldn't security be done there, rather than the DB?

Security should be done in every layer of the system. If you only did security on the web server, if crackers are able to compromise it, getting into the database is simple at that point. But if they can only run stored procedures, they then need to find a hole in the database which halves the likelihood of getting in.

Of course there are all the costs of maintaining the extra level of security. So you have to consider the cost/benefit of the security you put in place.

Braindead assertion...

[Anonymous Coward]

"[snip]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)?[snip]"

You've argued this because you've only been around for a we fraction of time and have never watched technology change from one generation to the next. If you stick the logic in the database, what is the cost of developing a new platform or changing web technologies? What's the cost of change if you don't put your logic in the database? In five years, are you going to assume your PHP code is a tomb of business process logic that can be trusted because its logic has been segmented and acts independently from data presentation hacks or frufy one-off hack jobs?

"[snip]Since the focal point is the webserver, shouldn't security be done there, rather than the DB?[/snip]"

Web software is a moving target. For every platform, web program, service, etc., you have to enforce the same set of rules. Stored proceedures are to databases what functions are to programming languages. Use them or your business will suffer from inconsistencies ranging from data management to more serious problems such as data access and theft. I bet companies that process credit cards or people who work on medical records probably have strong and justified opinions on the topic of data security... think the cost of implementing consistent database proceedures is bigger or smaller than the cost from a screw-up that lets the data walk out the front door?

You mention MySQL in your post... use a real database such as PostgreSQL for two months and let me know how your opinion on databases has changed at the end of your trial run. Compare and contrast this experience with your experience stemming from application development with MySQL. If you think MySQL in its current state is still a valid core for an organization at the end of that duration, you obviously suffer from some form of brain damage.

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.