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

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

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.

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

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.

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

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.

Re:Good or bad?

[johnstoj]

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

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

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