|
On stored procedures...
Reading from a recent post, somebody pointed about costs of migrating from one database to another, and one thing came to attention: stored procedures.
Is there any real alternative to develop stored procedures in a universal way? I mean, I just don't like being locked in a trunk because I can't port my stored procedures easily. I must confess I'm deeply ignorant about databases and this kind of stuff, but I believe there should be a way to store database logic in a language-independent manner (even if you have to use a third language to do this).
(Maybe I'm giving away an idea for a new tool, but I doubt it)
Leonardo Herrera
Tuesday, November 18, 2003
One of the alternatives is not to use stored procedures, and many projects do just that. (Or they have just a handful, and try to avoid adding any more).
Portabella
Tuesday, November 18, 2003
Or try to avoid using platform-specific features in your stored procedures, such that porting them is relatively easy. At least, that's the theory. I have no real experience here.
Ian Olsen
Tuesday, November 18, 2003
Java Stored Procedures. Oracle, Sybase, and DB2 supports them, and over time more databases will.
--
Tuesday, November 18, 2003
You should also write your code in a pure logical format, so you don't get locked into a single language.
In eight years and dozens of projects, I've seen exactly *one* company switch database platforms, and that was an ill-informed political move.
If you're developing a corporate application, use their database. If you're building a business, choose a database and stick with it. If you're developing a shrink-wrapped app, you either include your own database or provide for the platforms you choose to support (look at Fogbugz).
If you eschew stored procedures, you definitely shouldn't be paying for an enterprise RDBMS - you're giving up a major reason for using them and, IMHO, for a poor reason.
Philo
Philo.
Tuesday, November 18, 2003
Leonardo,
As with any other languages SP languages is very different when doing non-trivial stuff. And when doing non-trivial stuff is where SPs shine.
To just encapsulate SQLs in SPs and return result set handles (ref cursors) is IMO a waste of time.
Triggers is IMO a good use of SPs; but they can be seriously hard to debug, when you have multiple levels of triggers. Beware.
Oracle has alot of "enterprise level" functionality built into its stored procedure language, which includes among other things
UTL_FILE filesystem read/write files incl. binary data.
UTL_TCP Client side TCP. You can´t implement servers.
UTL_SMTP Mail SMTP client functionality
UTL_HTTP Web client requests
DBMS_JOB Scheduling of jobs (ie. DB-based crontab)
DBMS_LDAP LDAP lookup functionality
All functionality is callable from the PL/SQL language, which means you can do callouts in triggers and so forth. This is were datbase based logic shines. I have not seen implementations of the above in any other databases.
So, what I mean to say is that if you do applications that
do something complex you probably benefit from implementing some of the functionality in SPs. I found in some cases SPs actually reduces complexity because many things can be done at the database level.
Writing your stored procedure code in a common language or program to the least common denominator will work against reducing complexity in your application.
Again, I only have extensive experience with PL/SQL.
Patrik
Tuesday, November 18, 2003
Just don't take it over the top.
Build your stored procedures as you would outside software. Keep it reusable, and clearly documented. This ease porting. Keep it relatively thin and cut them into smaller stored procedures, this also helps porting. Whenver performance isn't an issue, try to use as little platform specific features that can't be ported as possible, this will ease future porting. But don't go overboard, or you'll miss out on a lot of features.
Li-fan Chen
Tuesday, November 18, 2003
Everything comes at a cost. Everything is a trade-off. With stored procedures you get:
Here's the benefits as I see them:
(a) Precompilation. No need to continue recompiling the stored proc. It's compiled once, and once the query plan is generated it's stored in the procedure cache and not compiled again. This can/may greatly improve performance, depending on a number of factors. They're generally a performance boost.
(b) Centralized management of the data, and the permissions to access that data. All data access is done at the database level through stored procedures, rather than strewn about in hundreds of source files spread across dozens of applications. If data access rules and/or permissions need to be changed/updated, you simply do it at the database and don't have to worry about the hundreds of source files. The DBA can efficiently control exactly who has permissions to what data, and when they can/can't get at that data.
(c) Isolation from data structure changes. If the base tables change, the stored procs can give you another level of abstraction. You (depending on how things were setup) may be able to completely change the data model and how the tables are setup and you won't have to change the client code if you modify the stored procs appropriately. Take this one with a grain of salt as it's very difficult to get right, and it's not simply an artifact of using Gtored Procs. You can get the same benefits from using Views.
Of course, those benefits come with the drawbacks that you've mentioned:
(a) You have to be very careful to not use vendor specific, proprietary features of the DB or the Stored Proc language. This can make porting to another platform *extremely* difficult.
(b) Even if you're careful not to use any proprietary features, you've still shot yourself in the foot as most DB platforms have a proprietary language.
The options are, as previous posts have outlined:
(a) Use a non-proprietary, "standardized" language. Java comes to mind.
(b) Don't use stored procedures at all.
A model that I've seen successfully used in a lot of places, and used myself, is to place all business logic outside of the stored proc, say in a c# or java component, and use stored procs for simple INSERT/UPDATE/SELECT/DELETE statements that are easily ported from DB to DB. If you're careful not to put any business logic in the Stored Procs, and if you don't use anything vendor-specific (proprietary), then porting is simply no-brainer busy work that can be relatively easily automated with a few scripts to translate from one DB platform to another. I've seen this done, and I've seen it done well. Unfortunately, I've seen it done badly too. It's hard to get right.
Word of caution: If porting is not in your immediate future, then don't worry about it. This is one thing I agree with the XP herd on. Do the minimum you can to make sure the project gets done within the requirements.You can spend a boatload time thinking about the "right" way to do things -- What if this? and What if that? You can eat up a lot of time pondering the question of "What if we have to support DB2" or "What if we have to support Oracle" when in reality, the vast majority of systems never need to get ported to another platform. YMMV on that. If portability is a requirement then ignore the above paragraph. The reality is that most systems don't have portability as a requirement and therefore should be ignored.
Sgt. Sausage
Tuesday, November 18, 2003
This entry seems topical:
"There are several reasons for using stored procedures to access data, below I’ve attempted to capture the 3 that I always reference:
1. Ad-hoc SQL Script is brittle
...
2. Security
...
3. Performance
..."
Don't use stored procedures yet? Must be suffering from NIHS (Not Invented Here Syndrome)
http://weblogs.asp.net/RHoward/posts/38095.aspx
Just me (Sir to you)
Tuesday, November 18, 2003
Concrete examples:
* Dealing with parameter types, flexibility of parameters, passing in/out of recordsets,
Answer: One way to reduce code count is to borrow some standard procedures from distributed programming.
* Dealing with types:
Marshal state with you from call to call (it's wasteful, but DB is even more wasteful). Reduce the numbers of types (everything is varchar string and extra wide longs). Add store procedures that can take your marshalled strings and convert them to native formats. (most dbs can also accept a variety of date formats, so this is a bad example)
Example:
##US_DATE_START##03/01/99##US_DATE_END##
* On Debugging: Marshal the error messages if you have to, use these arrays to bring in debugging info and output debugging info. Unless your stored procedure language support it, you might have to resort to hacks like this:
(Assuming you'll be splitting on ##START## and ##END## to grab the value inbetween)
Example Arrays for In parameters:
##START_VALUE1##value1##END_VALUE1## ##START_VALUE2##value2##END_VALUE2##
##START_VALUE3##value3##END_VALUE3##
Example Arrays for output debug messages:
out = out + "##ASSERT_MISSING_PARAM_X##"
out = out + "##ASSERT_INVALID_PARAM_X##"
out = out + "##ASSERT_INVALID_STATE_X##"
Avoid any of these tricks if there are better ways. But some older dbs forces you to do hacks like this.
Li-fan Chen
Tuesday, November 18, 2003
> A model that I've seen successfully used in a lot of places, and used myself, is to place all business logic outside of the stored proc, say in a c# or java component, and use stored procs for simple INSERT/UPDATE/SELECT/DELETE statements that are easily ported from DB to DB.
Thanks. That's what I meant as well, and IMO it's a good model when done properly.
> If you eschew stored procedures, you definitely shouldn't be paying for an enterprise RDBMS
This is utterly bogus, IMO; you are generally paying for enterprise-level support and stability.
I do agree with the larger point that it's far more common to port the application than the DB, and so you should plan accordingly.
Portabella
Tuesday, November 18, 2003
For years, I have always used stored procedures. Whether it be in SQL Server or Oracle, I've always felt that DB code belonged in the database.
I'm begining to mellow a bit.
Although my client has primarily been an Oracle shop, they have been using more and more SQL Server. I've got thousands of lines of stored procedure code within Oracle that will now have to be migrated over to SQL Server.
I wrote my own database abstraction code so my applications are database agnostic, but obviously the stored procs are specific to Oracle. Had I contained all of the SQL within my application, I could have simply built the new database in SQL Server and given my application a new connection string and been up and running.
I certainly won't advise against using stored procedures, but I would advise that you at least consider what implications a migration will have.
Mark Hoffman
Tuesday, November 18, 2003
Regarding last post:
* Most commercial and open source DBs support either stored procedures calling external business logic dlls/scripts/executable objects.
* You can put the business logic outside of the db (but design in assertion and audits) and call simple DML/DDL stored procedures as mentioned in last post. It's important you have a clear plan of how to do the assertions and audits.
Li-fan Chen
Tuesday, November 18, 2003
You can simulate stored procedures with perl libraries rather easily; that will be portable to the vast majority of databases.
In theory, you could do that kind of thing with ODBC / VC++ / ADO / VB, but then you're tied to Winders.
Matt H.
Tuesday, November 18, 2003
I agree with portabella. Stored procedures are over-rated. You actually cause more problems, because your integrarting business logic into your reading/writing code. Rather then have 60 different stored procedures all over the place, you can have your queries in one class, or seperated out into a few classes. This is better for code re-use too, its a lot more difficult to have re-usable functions that multiple stored procs use, compared to having a couple generic methods that would be used throughout your data classes.
Also, if you need to go-back and add to your stored procedures, you can't dynamically "build" queries. If you do, you lose all advantages of having the stored procedure.
Vince
Tuesday, November 18, 2003
So if we're planning for the future, which is more likely:
a) Moving your application to a different RDBMS
b) Providing scalability in your application to handle additional loading
Because if you look at the advice in this thread, the two issues appear to be mutually exclusive - the things you have to do to provide for platform agnostics will seriously hurt your scalability.
I honestly think that planning for a change in database is like putting asteroid collision provisions in your disaster recovery plan.
Philo
Philo.
Tuesday, November 18, 2003
If you know your RDBMS sql language well enough, and with some careful planning, you don't have to dynamically build queries.
Philo
Philo.
Tuesday, November 18, 2003
> Because if you look at the advice in this thread, the two issues appear to be mutually exclusive - the things you have to do to provide for platform agnostics will seriously hurt your scalability.
Maybe at Ultimate Scalability levels, but many Web applications can be very performant without stored procedures, and with a high level of database agnosticity.
Portabella
Tuesday, November 18, 2003
> "I honestly think that planning for a change in database is like putting asteroid collision provisions in your disaster recovery plan."
You're spot-on, if the business plan calls for deployment in, uh, an asteroid field.
If you intend to sell your application to people who may not be interested in purchasing/supporting [wawa database platform] just for your application, you're in trouble if you don't plan accordingly or at least make some good architectural decisions that make it feasible.
If you're going to host the application yourself until the end of time, and you have no beef with [wawa deatabase platform] then sure, there's probably better ways to spend your development dollar.
Ian Olsen
Tuesday, November 18, 2003
Here's a big stored procedure benefit nobody's mentioned yet: protection from SQL injection: somebody putting clever input into a text field that issues an unintended command to your database.
If you're dealing exclusively with parameterized stored procedures, the vulnerability just goes away.
If you're building SQL statements throughout your application code, like your standard whipped together ASP application, good luck plugging that hole.
Ian Olsen
Tuesday, November 18, 2003
Well, I know this isn't usenet .... but I've been dying to know. I'm a mysql :) php web developer and I must update a mssql database and the IT guys give me a Stored Procedure to use (I use curl to call an ASP file on their server that calls the Stored Procedure) but what I don't understand is that I must pass the exact same parameters for an UPDATE as for an INSERT. I cannot for the life of me fathom that. I don't get all the table fields from a form when an update request arrives, only those fields that are to be updated ... I was wondering why they could not just accept the field/value list that I wanted to pass as opposed to the complete field/value list. What I do now is I update my table then do a select to get the latest profile and use their SP for the update (which is more like a brain dead delete/insert)
Me
Tuesday, November 18, 2003
> what I don't understand is that I must pass the exact same parameters for an UPDATE as for an INSERT.
You don't have to do so in SQL, you can specifically name the columns you want updated.
Portabella
Tuesday, November 18, 2003
Philo:
Your wrong. Explain to me how you can self join X times on a vertical table without doing an dynamic query.
Ian: this should never be an issue. Your supposed to use a PreparedStatement in java, and a Command object in ASP. That handles that.
Vince
Tuesday, November 18, 2003
"I honestly think that planning for a change in database is like putting asteroid collision provisions in your disaster recovery plan."
I honestly hope your not serious. Changing database platforms is not some super rare event.
That doesn't necessarily mean that you instantly write to the least common denominator. However, it does mean that you take it on a case by case basis. Is your system a enormous project spanning 40 locations with 100,000 users? Well, you're probably not gonna change databases.
Is your project a departmental application running on SQL Server because "it's what we have right now" while other departments all use Oracle? Well, then perhaps you might at least consider not using stored procs because a migration to Oracle might be in the cards.
For each case, there might be a specific performance or security reason to using stored procedures. On the other hand, there might not be. All I suggest is that you doing a little analytical thinking before you just jump in and write all of the DB code in stored procs.
Mark Hoffman
Tuesday, November 18, 2003
Well, thanks for all your comments. I'm quite at the same place before asking :-)
However, it's gratifying to know that my own opinions aren't too different from most of you. See, in my app I do everything through stored procedures, but about a year ago we almost were forced to migrate (contractual problems with our db vendor.) When we were evaluating the impact, boy I got a headache. Have you thought that every little "select" that you put in a stored proc moves business logic away from your application into your database? This just _feels_ wrong.
About some sort of tool to avoid this: Sybase PowerDesigner have the ability to store your database model in an agnostic format. I wonder if anybody has invented a method to store not only conceptual database models, but stored procedures as well. Maybe we should rely on tools for migrating between databases.
Leonardo Herrera
Tuesday, November 18, 2003
It's worth pointing out that the ISO SQL standard specifies a very powerful and complete stored procedure/trigger language in the PSM (persistent stored modules) package.
It's unfortunate that people have come to expect their database supplier to 'extend' a 'subset' of an old version of the standard, which invariably means that the important SQL constructs are missing and the procedures and queries become non-portable.
It needn't be that way. Some SQL implementations are more open, e.g. http://www.thinksql.co.uk
Greg Gaughan
Tuesday, November 18, 2003
[Can the relevant people please double check their use of "your" over "you're"? Correct spelling aids understanding.]
₪
Wednesday, November 19, 2003
In sybase powerdesigner the views and stored procedures goes in the physical (db specific) model not the conceptual model.
You can however have a logical physical datamodel which is then db agnostic and you can keep the views and nondb specific stuff common to all your db specif physical models (if you have the same realtional schema on more than one physical db) in the logical model.
You can introduce check constraint (table invariants) in the conceptual model already.
hth
k
Karel
Wednesday, November 19, 2003
Karel, yup, I knew that. That's why I was asking if there is a tool similar in concept but that applies to stored procedures. So far, the most probable answer to this is the PSM module pointed by Greg Gaughan. I'll take a look.
Thank you all for your responses!
Leonardo Herrera
Wednesday, November 19, 2003
Vince:
If you're doing X self joins a single query, then you've just given up DB independance. I don't believe there's any way to do complex self-joins that won't require tuning to a particular RDBMS's quirks for adequate performance.
Instead of doing dynamic self-joins in a stored proc (which can be done with native dynamic SQL in a stord proc, btw), you can just as easily manually dynamically join the data using temporary tables and views. Since it's all happening on the DB side and only the final results are sent to the client, it can be just as performant (and easier to troubleshoot where the inefficiency is) than a massive, tricky SQL SELECT.
Massive SELECT statements are terribly difficult to maintain. Massive dynamic SELECT statements even more so. If you find yourself using massive dynamic SELECT statements, you're likely doing something wrong. Since m.d.S.s's basically fubar your DB independance anyhow, you should probably be using some VIEWs on the DB side instead, at the very least.
Richard Ponton
Thursday, November 20, 2003
 Recent Topics
Fog Creek Home
| 
