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

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

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

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

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?

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

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]

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.

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.

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

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.

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.

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

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

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.

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.

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.

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)

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

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.