Re: Stored Procedures

On Mon, 28 Feb 2011 14:00:24 -0600, JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>
wrote:

> I am starting to develop an application and I am considering to use stored
> procedures. Is there a performance penalty for implementing many DB
> operations with stored procedures? For example, if my app has 50 tables, is
> it a good idea to write store procedures for inserting, deleting and
> updating data for all of them?

External orders have more overhead: time of transmission, then
compilation and execution (AFAIK, both possibilities are left in cache after
1st processing), stored procs jump the 1st step.

The main advantages of stored procs are (IMHO):
* With good permissions, stored procs rights & users management, nobody can
issue external orders to do the same,
* Users can't override your security (in order to avoid OID dependency, I
use my own users system in which I can revoke one in 2 clicks - by disabling
the CONNECT permission.)

> What if, additionally, I include stored
> procedures for information retrieval (SELECT statements)?

For this one, I'm not sure: I kept complicated SELECTs that return a single
row as a result stored, while keeping simple SELECTs on the client side.

> What would be a
> good criteria to decide how far to go with stored procedures?

The complexity of what can be done with them and the invisibility of sensitive
procedures because of user's rights (or not) upon them, tables & columns.
Every big proprietaries databases also have them.

HTH
JY
--