Daily Static

Technology, Dynamic Programming and Entrepreneurship

This page is powered by Blogger. Isn't yours?
Saturday, October 29, 2005
 
The myth of the stored procedure

I just came across an interesting thread on the Ruby on Rails blog here. I'm not going to touch the arrogance issue because that's too subjective. What I will address are two things:
1 - the need for stored procedure support
2 - the need for xml configuration

As the reader will have no doubt surmised, I don't believe in stored procedures, however that wasn't always the case. Having been primarily a developer and secondarily a dba for nearly 12 years now, I've had the opportunity to work on some massive systems. I've had the opportunity to lead the design and implementation of some rather large systems, intially I used stored proc's heavily, but over time the glaring problems with stored procs have become rather obvious.

As I see it there are three primary problems with stored procedures

They don't scale. I can practically hear the outrage at that statement. What are you talking about? Stored Proc's don't scale? You've have to be crazy! Before you go huffing off let me explain that statement. When I talk about scalability I am not referring to machine performance per se. Instead I'm talking about the position that on any system of sufficient size, the most important problem and least scalable resource will be the allocation of programmer time and their ability to comprehend the complexity of the system. I'm sure there are exceptions to this rule, but I am talking about the vast majority of systems, rather than a few special cases. Ok, so you believe it or you don't, how exactly do stored procedures fit into this picture? It's a generally recognized principle within the software development world that code duplication is bad. Cut and paste coding causes problems because logic is duplicated all over the place. When you make one change you then have to go and find all of the other places where that logic resides and duplicate your changes in the code. All software developers know this, there are a million ways to avoid this issue when it comes to software. But when it comes to databases, somehow that rule no longer applies? Stored Procedures are effectively cut and paste coding of SQL. It's not a problem if you only have one or two databases to deal with, just like if you only have one or two code files cut and paste isn't a huge issue. Multiply that by a few hundred or a few thousand though, and you have a serious problem. If you have ever had to propagate a changed stored procedure to a couple of hundred databases you know what I'm talking about. Now I'm not saying that stored procedures are all evil in all circumstances, but as with all things in computer science, their advantages and disadvantages must be understood and weighed. Generally I would say that building a system on top of stored procedures is a bad idea.

Stored procedures are not portable. This is kind of a no brainer, typically the database vendors don't even try to argue their stored procedure syntax is portable to other systems. Usually their argument goes something like "and to port this application, all you have to do is re-write the stored procedures". Generally I think that's probably not even true, but let's assume for a minute it is. Apply that logic to the same scenario above. So you have to re-write your stored proc layer then propagate those changes out to hudreds or thousands of databases? Hard to imagine how people get locked into a database isn't it? I can tell you from first hand experience that it's unlikely any significant system will be ported under those circumstances without a very serious reason. So the basic idea is to build your system in a way that you can easily use another database if necessary (this includes new versions of the database by the way).

Finally stored procedures don't facilitate reuse. This is related to the first issue, but isn't identical. In this case I'm not talking about duplication over a large number of databases, rather I'm talking about leveraging your codebase in different situations. This can be within the same database or within a different system. The point is anything you put in a stored procedure stays in a that stored procedure. There really are no good methods for high level abstraction within stored procedures. Now maybe there is some method that applies to some specific database, if so, it's not portable to other databases. On the other hand through careful use of abstraction in code you can reuse your logic in other situations.

I know there are other issues with stored procedures, but for me those are the big ones. Now to XML tags and configuration.

As with stored procedures, XML is one of those things that should be used at the right time under the right circumstances. XML was designed as an abstraction for marking up data, to me it was never designed as a programming language. I have had the misfortune of using the XML heavy frameworks (j2ee, asp.net, Zope). XML definitely makes those systems more configurable, but one thing it definitely doesn't do is make development faster. Wading through the morass of XML configuration has never sped me up, but has always dramatically increased the time to implement.

I'm sure there are situations where XML is useful, but as an Entrepreneur I am less interested in those situations and I'm more interested in the situations that I can leverage the power of DRY and sensible defaults to maximize my time. That philosophy more than anything else has really sucked me into Rails. The sheer joy of focusing on the problem and not the damn configuration files cannot be underestimated. If you really think XML is the solution, please stay in the XML frameworks. That is one direction I sincerely hope Rails never moves.


Comments:
I think this is a wonderful post and I plan to use it the next time I need to argue against stored procedures when developing new applications. I'm grateful to you for your contribution. I have made a post paraphrasing your entry on my own blog.

Now for the case with Ruby on Rails: Sometimes we don't have a choice in the matter. Sometimes we are dictated by other teams we don't have control over that we must use stored procedures, or they are part of a legacy system we must integrate with. It would be nice to be able to use RoR in this case. Without stored procedure support, we must chuck RoR in this case. That's what's at issue.
 
Make no mistake: Our mission at Tip Top Equities is to sift through the thousands of underperforming companies out there to find the golden needle in the haystack. A stock worthy of your investment. A stock with the potential for big returns. More often than not, the stocks we profile show a significant increase in stock price, sometimes in days, not months or years. We have come across what we feel is one of those rare deals that the public has not heard about yet. Read on to find out more.

Nano Superlattice Technology Inc. (OTCBB Symbol: NSLT) is a nanotechnology company engaged in the coating of tools and components with nano structured PVD coatings for high-tech industries.

Nano utilizes Arc Bond Sputtering and Superlattice technology to apply multi-layers of super-hard elemental coatings on an array of precision products to achieve a variety of physical properties. The application of the coating on industrial products is designed to change their physical properties, improving a product's durability, resistance, chemical and physical characteristics as well as performance. Nano's super-hard alloy coating materials were especially developed for printed circuit board drills in response to special market requirements

The cutting of circuit boards causes severe wear on the cutting edge of drills and routers. With the increased miniaturization of personal electronics devices the dimensions of holes and cut aways are currently less than 0.2 mm. Nano coats tools with an ultra thin coating (only a few nanometers in thickness) of nitrides which can have a hardness of up to half that of diamond. This has proven to increase tool life by almost ten times. Nano plans to continue research and development into these techniques due to the vast application range for this type of nanotechnology

We believe that Nano is a company on the move. With today?s steady move towards miniaturization we feel that Nano is a company with the right product at the right time. It is our opinion that an investment in Nano will produce great returns for our readers.

Online Stock trading, in the New York Stock Exchange, and Toronto Stock Exchange, or any other stock market requires many hours of stock research. Always consult a stock broker for stock prices of penny stocks, and always seek proper free stock advice, as well as read a stock chart. This is not encouragement to buy stock, but merely a possible hot stock pick. Get a live stock market quote, before making a stock investment or participating in the stock market game or buying or selling a stock option.
 
I read over your blog, and i found it inquisitive, you may find My Blog interesting. My blog is just about my day to day life, as a park ranger. So please Click Here To Read My Blog
 
Here it is... FREE advertising, FREE download. No cost to you! Get your FREE download NOW! Make money and get FREE advertising! This is a great program for the new year... Check this out now for FREE!

To find out more visit: multi level methods site. It successfully exposes FREE information covering traffic and multi level methods related stuff. Don't forget, FREE, FREE, FREE!!!
 
Hello I am the traffic man, zip, zip, zip. make a new resolution to get a flood of traffic to your website this year. Let me show you how to get FREE traffic to your site. Yes I said FREE, FREE, FREE!!! Don't delay.

To find out more, visit my mlm site. It successfully covers FREE information exposing FREE traffic and mlm related stuff. Don't forget - FREE, FREE, FREE. You have nothing to lose!
 
Hello I am the traffic man, zip, zip, zip. make a new resolution to get a flood of traffic to your website this year. Let me show you how to get FREE traffic to your site. Yes I said FREE, FREE, FREE!!! Don't delay.

To find out more, visit my home internet opportunity productBuild Your Business Online site. It successfully covers FREE information exposing FREE traffic and home internet opportunity productBuild Your Business Online related stuff. Don't forget - FREE, FREE, FREE. You have nothing to lose!
 
While you read this, YOU start to BECOME aware of your surroundings, CERTIAN things that you were not aware of such as the temperature of the room, and sounds may make YOU realize you WANT a real college degree.

Call this number now, (413) 208-3069

Get an unexplained feeling of joy, Make it last longer by getting your COLLEGE DEGREE. Just as sure as the sun is coming up tomorrow, these College Degree's come complete with transcripts, and are VERIFIABLE.

You know THAT Corporate America takes advantage of loopholes in the system. ITS now YOUR turn to take advantage of this specific opportunity, Take a second, Get a BETTER FEELING of joy and a better future BY CALLING this number 24 hours a day.
(413) 208-3069
 
Post a Comment