Summary
This is a one-sided analysis of the downsides of using stored procedures. PLEASE do not take this to mean that I'm in any way against their *judicious* use.
Advertisement
Errors in the stored procedures tend to affect the database globally, even for applications that are not using it
For one of my clients, a procedure originally written and optimized for accessing small record sets (using temporary tables) was never re-optimized. A bug in the database related to large temporary tables crashed the production database. It took a day for the vendor's best database experts to debug and fix the problem.
Version skew becomes more and more likely as the application code and database code are spread over multiple staging environments
Some of the environments may have new functionality in some subsystems and rolled-back functionality in others. Keeping track of the skew between application code and database code is completely manual (no tools exist to automate) and can easily get lost.
Behavior changes in a large codebase are more easily implemented in application code than in database code
As the code base grows and specializes, different parts of the application will begin to require slightly different behavior from the stored procedure. This will require either increased complexity in the procedure or complexity in the API (by forking into a new stored procedure).
Software application environments are much easier to debug
Database debugging tools are severely lacking.
Over time, applications from one group become dependent on stored procedures under the control of another application group
Changes implemented by the primary application group can break the dependent application. See the comment on siloing too.
Application teams and database teams tend to "silo" and politically protect their areas
May not always be an issue, but if the staff grows to where the two areas are separated then human nature will make it more likely as each group tries to minimize effort.
Solving problems using stored procedures versus using application code does not lower application complexity
Since application brittleness and poor application performance are usually due to increasing complexity, faster database access provided by individual stored procedures becomes moot.
I think there a lot more problems with stored procedures. Here are some more:
* They're lacking abstraction so they are hard to test. When the complexity of a stored procedure grows you have to fill numberous tables correctly in order to test them.
* The tools are powerful for some databases but not comparable to nowadays IDEs.
* Getting the data from the application to the database and back using stored procedures can be quite awkward.
I personally prefer to use them for performance tuning especially if there's alot of data to be shouvled around in the database. However, I always prefer to avoid them.
Many good points in this post, Dale. I have a pet theory that many of the problems that come along in the area of stored procedures and the database in general have to do with the artificial cultural separation between "programmers" and "database people" (which you allude to in one of your points). In my view, we're all developers, just with differing specialties and tools. And databases, in my view, are nothing but a special kind of code--even (or especially) the DDL.
To be sure, considered separately developers and database specialists operate under different paradigms. The unique qualities of relational systems create the most interesting differences; the constraints on how problems are solved in the procedural/OO world and the relational world are different, and lead to different kinds of thinking. Stored procedures are an interesting gray zone where the relational and procedural (and in some cases OO) worlds clash.
However, this need not be problematic. Developers and database specialists are smart people who shift between paradigms all the time: procedural, OO, declarative, relational, two-tier, distributed, replicated, scripted, static, dynamic, hybrid--anyone working on interesting systems today works within a variety of paradigms.
But for some reason there is this wall between developers and database specialists. The bureaucratic, political, and cultural entanglements that result from this separation, I believe, are the root cause of many database-oriented issues like the ones you describe in relation to stored procedures.
In my experience the projects that avoid the kinds of problems you describe come out of teams where there is an active two-way understanding between the people in these two areas. That is, the database-centric people are hip to the non-database development world, and the traditional developers are hip to the database world; specialists in each area are aware of the strengths, weaknesses, and constraints of the other area.
I have yet to see a DBA who wrote unit tests or effectively used version control. I even worked with one DBA who actively resisted putting the code for any stored procs into the application source control system.
Unless and until database people are disabused of the notion they have of their tool as the center of the universe around which the enterprise rotates stored procs will remain the boat anchors of the software development world. Sorry to insult all the useful boat anchors out there on sea-faring vessels wordwide.
That's an old argument and I have found it to be the least convincing, since it is actually quite rare (in my experience) that an application is ported to another RDBMS vendor (or at least not without equally significant changes or rewrites of the applications themselves).
If you're creating shrinkwrapped COTS or making open-source products, then...sure, you'd avoid vendor lock-in, but most of the time this has never been a compelling enough argument against s. procs to prevent me or my teams from using them.
Generally I agree with the preference AGAINST using stored procedures, however...
I think most problems occur when business logic, of the sort that changes frequently or is mostly algorithmic (as opposed to data-driven or data-centric) is encoded in these rather than in more-capable languages.
But, take for an example an environment in which the database is the "center of the universe" and a variety of applications, written in a variety of languages and/or shell scripts, are used to manipulate the data; in this scenario, a database-provided API of sorts is necessary to ensure that the same rules are applied no matter what application or script attempts to read, update, or delete data.
In this case, I would say stored procedures are essential and necessary; applications would be given only 'execute' level grants and thus the only way to corrupt the data would be through the procedures.
I would guess that the performance is about the same when the stored procedure replaces a parameterized SQL. Often, however, what you want to reduce is the number of calls to the database since they are network calls and normally consume most of the time. In my last project we used stored procedures for sorting out tree like structures and similar problems where it just isn't possible to do it in one SQL call.
well, little that has been said in the post applies solely to stored procedures, triggers, constraints and the like. database folks can and do frequently make the same complaints against parochial coders. coder: "it's my application, and you can't touch it!!!" 'you' is other applications, coders, database designers. remember: Codd's paper refers to "large shared data banks".
quote: Application teams and database teams tend to "silo" and politically protect their areas
coders are *far* worse. you may note: they *hate* to have the data strong enough to prevent them from messing it up (SP is part of the arsenal needed to protect data from errant coders). the whole point of database is *shared* access.
quote: Over time, applications from one group become dependent on stored procedures under the control of another application group.
how is this different from becoming dependent on client (application) code?
yes, coders are the enemy of data. consider: what's the ratio of incidents of data messing up code, to incidents of code messing up data???
coders who wish to have sole control of data (and that's what happens when data integrity is ceded to coders) ought not to use a database. just go back to the days of COBOL/VSAM, and be happy. today: java/object. same thing. it is the case that application coders (nearly) always have a client (in the programming sense) bias, and client code can never be trusted to maintain the data. except in the Goode Olde Days of sequential batch processing; then there was only (one notion of) code and data, and the code was both "client" and "server". those days are gone.
folks who know enough about data modelling and design rarely want to be coders; those coders who get control of database specifications are rarely skilled database designers. different mindset. that P/J thing again.
caveat: i'm not a big fan of SP. a database design should be at least 3NF, and thus doesn't need SP to keep itself protected from coders, users, and the rest of the great unwashed.
it also worth noting that database driven design is actually a *movement* (not so large as THE ALICE'S RESTAURANT ANTI-MASSACREE MOVEMENT!), but a movement nonetheless. soon, coders won't even be needed. the schema will be used to generate the application. people are doing it now. 3NF databases, and generated CRUD. that's what web bandwidth demands. there won't be any java/COBOL/PL1/BASIC/whathaveyou coders to argue with any more. it'll be lonely, but that's OK.
Go count to a hundred. You may do it in SQL for all I care. Come back when you can keep your bile in your stomach for long enough to say something substantive.
So... you're agreeing with the premise that app teams and db teams tend to silo?
>>how is this different from becoming dependent on client (application) code?<<
Basically it's the same. Except... application groups cannot be forced to use another application group's code.
>>what's the ratio of incidents of data messing up code, to incidents of code messing up data<<
That's irrelevant. Data is unimportant of it's own - code is what makes data important and/or meaningful.
>>folks who know enough about data modelling and design rarely want to be coders<<
Says who? I know that I enjoy both, however, I arguably also think I know enough about both.
>>it also worth noting that database driven design is actually a *movement*<<
Uh, ok. It's actually been around for quite a while too. Hence the popularity of such products as dBase, SQLWindows - Powerbuilder, MS Access.
----
Oh well, I guess hoping for non-ranting comments was too much. Funny enough, the post was originally my response to *coders* who wanted to implement *every* database CRUD access as a stored procedure.
>keep your bile in your stomach for long enough to say something substantive<
wasn't feeling much bilious. anyway, the OP was chock full of BoldFace type and Absolute Statements. maybe i should have strewn some smilies??? <G>
perhaps i should have provided the names of database driven design tools in my first reply. they are surely substantial. i just figured that people interested in database development would be aware of the movement, if not all of the participants. really, have a look at FireStorm/DAO. there really is another way to view the development problem.
and i do take issue with the tone of the OP and the bulk of the replies (and i suppose most of the repliers take issue with mine). the issue is simply put: a centralized, shared data store cannot be reliably controlled from client code. it's that simple. and client code is any code which resides outside the data store. a proper RDBMS will have little code, since its structure compels proper modification of data for the most part.
there are two kinds of stored procedure supported by industrial strength RDBMS: internal and external.
most folk think of SP as the internal type, where the text and its compiled version are physically in the database. the text is in the syntax of the language provided by the vendor. these are translateable with SwisSQL. it also translates triggers. there may be other products. one could develop largely the same thing with awk. not that i would.
the external type, DB2 is a prime example, is just a program in some 3GL which is bound to the database. these are perfectly portable; one just substitutes a connection to Oracle for one to DB2.
Flat View: This topic has 17 replies
on 2 pages
[
12
|
»
]
Previous Topic
