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

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.

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

Replies are listed 'Best First'.
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]

Replies are listed 'Best First'.

Re^2: DBD::Oracle faster with bound sql than stored procedures?
by mpeppler (Vicar) on Nov 25, 2006 at 16:30 UTC

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]