Re: Writing most code in Stored Procedures

On 8/15/07, Rohit <rpk(dot)general(at)gmail(dot)com> wrote:
> I have few queries regarding the use of Stored Procedures, Functions
> and Triggers in an RDBMS.
>
> (1) When to use Stored Procedure? Writing an INSERT query in a Stored
> Procedure is better or firing it from the application level?
>
> (2) Can a Trigger call a Stored Procedure?
>
> (3) What type of code must reside in application and what type in
> RDBMS?
>
> (4) Is it faster to work at application level or at the database level?

One of the major advantages of stored procedures over application code
is that stored procedures can get to the data much more quickly than
an application can, in general. An application needs to talk to
PostgreSQL through some sort of driver (JDBC, libpq, etc.) and
typically data need to traverse a network to get from the database
machine to the application server. Stored procedures, on the other
hand, don't have the overhead of either the network or the driver.
However, stored procedures can be more difficult to debug, profile,
etc., so they might not be the best for really complex logic. They
tend to be really useful if you're doing something that requires lots
and lots of queries to the database, and don't need anything else
(data from other sources, user interaction, etc.), because that takes
greatest advantage of their quick connection to the data. Richard
Huxton's point that stored procedures are typically best for data
integrity types of functions, whereas business logic should often be
in application code is an excellent rule of thumb.

-Josh