Subject Re: [firebird-support] Good DB design: When to use stored procedures?
Author Lucas Franzen
Zd schrieb:
> Hi,
> I've read in the FB papers that using stored procedures is highly
> recommended because it optimizes network traffic and makes query
> execution faster.
> The question is: I have over 50 tables in my DB and a lot of
> different queries acessing them in different ways. If I started
> making stored procedures for each dynamic query I'm executing now
> from my program, I'd end up with 200 stored procedures at least.
> So is it a good idea to put EVERYTHING into stored procedures (like
> even a simple update or insert into statement which puts a few rows
> to the DB)?

There is no absolute rule when to use sps and when not.
Personally I wouldn't use SPs for select unless they're too complex for
standard SQL.

Updates and delete within stored procedures are useful if you have
dependencies and want to update several tables in one go.
IBObjects for example allows you to keep request live on all tables
within joined queries by the means of calling a stored procedure when
inserting, deleting, updating, so the stored procedures can take care of
the appropriate DML statements.

A good point for using stored procedures is when doing calculations that
can be done at server side, so you just have to transfer the parameters
to the server and the results back from the server and let the
calculation happen at server side. This way you're minimizing
unnecessary network traffic tremendously.