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

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.

Life without them...

[qurve]

...would be hell for me.

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

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

[msobkow]

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

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

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

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

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