Re^2: DBD::Oracle faster with bound sql than stored procedures?

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:

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
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.
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
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)?
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.

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

Comment on Re^2: DBD::Oracle faster with bound sql than stored procedures?

Replies are listed 'Best First'.
Re^3: DBD::Oracle faster with bound sql than stored procedures? by BrowserUk (Patriarch) on Nov 25, 2006 at 14:42 UTC
`'++' 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.	[reply] [d/l]
Re^4: DBD::Oracle faster with bound sql than stored procedures? by jfroebe (Parson) on Nov 25, 2006 at 15:10 UTC
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	[reply]
Re: DBD::Oracle faster with bound sql than stored procedures? by jonadab (Parson) on Nov 25, 2006 at 15:27 UTC
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.	[reply]
Re^2: DBD::Oracle faster with bound sql than stored procedures? by mpeppler (Vicar) on Nov 25, 2006 at 16:30 UTC
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.	[reply]