On 8/15/07, Rohit <rpk.general@xxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
