I always thought that stored procedures were there to move data logic from the applications to the data store. So that you could take advantage of the same logic (bugs and/or fixes) in all applications that dealt with that data. So, when you have a Java application, a .NET application, a perl CGI script, etc., all accessing the same data, providing various views (and/or methods of access), they can use the stored procedures to do common work, whatever that may be.

True, they somewhat tie you to a particular vendor. But the same can be said about tie-in from .NET or Java, or that evil Larry Wall. And, if you write your stored procedures in, say, Java, you may not be as tied in as you may think - most of the logic will be moveable, only the actual APIs would be different (and not always all of those).

I suppose it depends on precisely what you're putting in stored procedures...

Anyway, back to the original question - is your application living on the same machine as the server? If so, stored procedures won't offer as much benefit - part of their appeal is that they can deal with large quantities of data with much less network overhead.

If you're paying for Oracle it's because you need Oracle. And having paid for it you'd better use it.

If you don't know who 'Ask Tom' then perhaps you should find out. His advice is invaluable in building large scalable Oracle databases.

Personally, I'd use stored procedures for virtually all database access. I'd also put all the business logic I could into the the procs. Coupling your data and business logic protects your application.

It's the front ends that should be easily swapped out and changed not the database.

As for maintaince nightmares. Yup, I've seen those, Embedded dynamic SQL in Java code was the worst.

As for the Oracle client C library, yes its blisteringly fast.

Why on Earth would I swap my RDBMS? Well, because IBM or MS or whomever offered a much better deal. I've heard that these companies (including Oracle, of course) compete quite a bit ;-)

What you do now can seriously impact how easy it is to switch, should your non-technical management decree it. If you take advantage of Oracle-specific functions, you can drastically speed up your queries, speed up your development, and hurt your ability to switch should the need arise. Kind of a trade-off.

Personally, though, while I understand the concept and need for the ability to switch, I'd still go with whatever gave me the maximum benefit under the current infrastructure, without impacting maintainability in a negative way, and then tell management what the costs were to switch our application for when other sales guys approach them to get us to switch. It still may come out as a benefit to switch because the switch itself is a one-time charge, while the licensing and support is an ongoing charge.

The cost of database licenses is a relatively minor component in the total cost of the whole project. If you have a pool of Oracle developers who understand your application and business then you're pretty well locked in.

Again, this is from a large project/large corporation perspective.

That seems a little silly to me. Hypothetically speaking, of course.. If I bought Oracle, then MS offered me a better deal, I'd have sunk costs from buying Oracle. It wouldn't make any sense to switch to Oracle. I think the nightmare of transporting the data from one database to another more than offsets the nightmare of tying yourself to a vendor's stored procedure methods (which, by the way, exist in all major RDBMS software).

Why on Earth would you want to swap your RDBMS? If you're paying for Oracle it's because you need Oracle. And having paid for it you'd better use it.

'cause it's life. Sometimes changes happen due to cost, politics and various other reasons. It just happens.

As for what is swapped out, a well designed system can have any layer of responsibility removed and replaced due to light coupling. i.e. Dont' do database calls from your template system.

Why on Earth would you want to swap your RDBMS?

I never wrote that. I'm exploring a new platform of curiosity, trying so see what it has to offer. So far it seems very promising.

If you don't know who 'Ask Tom' then perhaps you should find cout.

I bought one of his books and I will continue to read his columns.

As for maintaince nightmares. Yup, I've seen those, Embedded dynamic SQL in Java code was the worst.

I second that.

As for the Oracle client C library, yes its blisteringly fast.

Do you mean replacing DBD::Oracle with some kind of compiled C routines?

Andreas
--

Do you mean replacing DBD::Oracle with some kind of compiled C routines?

No, DBD::Oracle uses the Oracle client software. You don't need to use any 'C' code.

Hi Jonadab,

With all due respect, it doesn't sound like you've worked with stored procedures or even understand their purpose very well. Stored procedures have pros and cons just like any other tool.

Pros:

1. You don't need to recompile and push out a new application or configuration file to all of your users every time you need to make a change to your sql
2. stored procedures are 'black boxes' that receive a specified set of parameters and return one or more result sets. How you do that can change without making modifications to the application (DBAs often are more likely to help you optimize your SQL if they don't have to wade through your (insert favorite programming language) code.
3. The query plan of the stored procedure will be stored in the database, thereby eliminating the compilation and optimization time unless there is major changes in the data
4. Moving to another DBMS is actually EASIER if you use stored procedures. Why? You call an equivalent stored procedure in the other DBMS (you're going to have to change the SQL anyways so why not separate the sql from your code as much as possible)?
5. and so on....

As a DBA of more than 15 years, I hear the argument "stored procedures are bad" at least once a month. They are just like any other tool... it depends on how you use them.

IMHO, many developers don't like stored procedures simply because they think they lose control of the SQL to the DBAs. In most companies, your local DBA has too much to do then worry about your stored procedures EXCEPTION: if you want to update the stored procedure SQL code in production, you better have tested it thoroughly in your DEV/UAT environments.

If your DBA is a "Little Napolean" and he/she wants total control, then that DBA is either inexperienced or just plain nuts.

'++' x 1e6.

There is an annoying little phrase that crops up around here all to often in the context of anthropomorphising code, languages, idioms, documentation tools and particular pages of documentation: "XXX is your friend.".

As much as that phrase annoys me in most contexts I see it used, I'm gonna use it.

Befriend your local DBA, because if he's your friend, your life just got a whole lot easier.

---

Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.

Lingua non convalesco, consenesco et abolesco. -- Rule 1 has a caveat! -- Who broke the cabal?

"Science is about questioning the status quo. Questioning authority".

In the absence of evidence, opinion is indistinguishable from prejudice.

I'll expand that a bit: befriend everyone you work with. :)

Jason L. Froebe

Team Sybase member

No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1

Perhaps what I am against is stored procedures used badly. I will freely admit that badly is the only way I have seen them used to date, but I'll concede that it may be possible to use them in a less problematic way that I have not seen.

I will stand by this, though: in the absense of strong evidence that a particular section of code is causing a user-noticeable performance issue, the maintainability of the code is more important than its speed.

---

Sanity? Oh, yeah, I've got all kinds of sanity. In fact, I've developed whole new kinds of sanity. You can just call me "Mister Sanity". Why, I've got so much sanity it's driving me crazy.

Stored procs, or SQL code located in a module off to the side have the exact same maintenance issues and the same potential coupling problems with the rest of the code.

Using stored procedures can make the code more modular, and more efficient, in particular for RDBMS systems where multi-version concurrency and long running transactions aren't handled well. In that case the stored procs allow for well-defined, short lived transactions that can be (mostly) independent of one another, avoiding most deadlock and similar problems.

It is my considered opinion (based on 20+ years of experience :-) that they are good for you.

Now triggers are a different story - they are a form of stored procedure, but their behavior and nesting can make the system's schema be rather difficult to understand (lots of nested rules buried in the triggers, sometimes).

Michael, Sybase (and somtimes Oracle) DBA.

Is speed really your most important consideration?

In fact, it is my considered opinion that stored procedures are a maintainability nightmare and should be avoided at pretty much all costs.

You end up with the next developer who has to touch that bit of code spending three days figuring out how it works first, instead of three minutes.

I keep having this horrible nightmare where this big, "flexible", "write-once, run (poorly) everywhere" application full of embedded sql comes to get me.

If you actually read my post, I don't like having SQL embedded directly in the application, either. I much prefer having an abstraction layer, wherein all the code that has SQL in it is off to the side in a (probably custom, unless your needs are quite simple) module that the rest of the code uses.

You end up with the next developer who has to touch that bit of code spending three days figuring out how it works first, instead of three minutes.

Yes, but only if you assume (s)he has not previous knowledge of the technology in question (i.e. PL/SQL, the Oracle database). In my opinion, that makes her/him unqualified for the role as developer for that platform. Besides, those three days spent learning a new technology could be the best investment that person made in a long time.

Apparently your experience with stored procedures differs from mine. Considerably. I'm talking about someone who is *comfortable* with SQL, someone that other people on the application's mailing list turn to with their hardest SQL-related questions, spending three days (well, three shifts) figuring out how on earth this set of inter-related stored procedures work. And he's still not confident changing them that he won't break anything.

Maybe I just ran into some very-badly-written stored procedures (and, I probably should mention at this point that it was not on Oracle, although I doubt if that makes much difference), but that was my experience, and it left a really bad taste in my mouth. Each of those several multi-page stored procedures could have been a very small snippet of code in a high-level language and one or two short SQL queries, coming altogether to six or eight lines, and instead it runs onto five or six *hundred* lines, without comments, because it's doing stuff in SQL that SQL was never designed for.

SQL is designed for getting information from the database and putting information into the database. When you start messing around with 30+ variables and your loops and conditionals are nested more than about two deep, it's time to use a language that was designed for writing such logic. Use the right tool for the job.

---

Sanity? Oh, yeah, I've got all kinds of sanity. In fact, I've developed whole new kinds of sanity. You can just call me "Mister Sanity". Why, I've got so much sanity it's driving me crazy.

.. spending three days (well, three shifts) figuring out how on earth this set of inter-related stored procedures work. And he's still not confident changing them that he won't break anything.

That sounds more like bad use of technology than bad technology itself.

SQL is designed for getting information from the database and putting information into the database.

Yes. But we're also discussing PL/SQL, which was designed to do a whole lot more than data retrieval.

Use the right tool for the job.

Exactly. And apparently when using Oracle, that will often involve PL/SQL.

Andreas
--

As justifications go for not using a particular language feature, this is pretty weak.

It's akin to never using regexes or hashes, because you might choose to swap the application to using a langauge that doesn't provide them at some unspecified point in the future.

Also, your perception of the mainenance problems are very short sighted. SPs come into their own when they are written and maintained externally to the applications that uses them. To any given application they become just another library (use DB::XYZ qw/ xyz_fetch xyz_update /;). In this respect they have the same maintenance issues as any other CPAN or in-house module, which is usually seen as a plus not a minus.

As for "permature optimization" aspect. There is a worse evil than premature optimisation. It is the assupmption that all optimisation *must* be premature.