Re: stored proc vs sql query string - Mailing list pgsql-general

> > i have heard somewhere that writing a stored procedure, is much better than
> > firing a sql query(such as select * from table_name) onto the database.
> > is it true and if yes how?

stored procedures (functions on postgresql) eliminate a lot of
overhead.  they also provide a lot of covenience of transactions
manually opening and closing them.  There are also security and design
benefits.

> This isn't going to be true most of the time, I think.  Write SQL where you
> can, and where you can't (because you can't express something in SQL), write

stored procedures allow the application to do complex things in the
database without having complex sql inside.  it also allows multiple
application technologies to access the database without reimplementing
the query in 10 differnet places.  what happens when that query
changes?

> a procedure.  There are places where using a stored procedure can be more
> efficient, but I think starting with SQL, benchmarking and testing, and then
> determining what queries need special attention is the best way to go at the
> beginning.
>
> > also i want to know that is the performnance in java slower as compared to
> > cpp, given that the same things is being done.
>
> Java and cpp performance are not really related to postgresql performance.
> You will probably need to ask that on another list.  There are many other
> reasons to choose one language over another besides speed (in fact, I think
> speed is probably not the first thing to think about when choosing a
> language).

language choice is important such that it may expose none, some, or
all, of the internal libpq api (or implement it's own version of the
client side protocol).  cpp can often be faster because you can make
very thin wrappers over the libpq calls (STL is ideal for this).

stored procedures, particularly pl/sql funtions, have the enormous
advantage in that queries are first class objects inside the
procedural code....so you can

x :=  1 + 3;
select * from t where id = x;

without nasty string concatination or learning a whole API just to
read and write data to the database.  Another advantage is that the
can also executed by the query engine so you can do:

select outstanding_account_balance(account) from account where...

these two advantages alone mean huge reductions in code along with
corresponding savings in development and real dollars.  It has been
more or less proven that functional, declaritive style coding has less
errors and is more reliable than mixed sql/procedural applciation code
given developers with equal skill.  thus, I would argue the opposite,
use procedures everywhere, keep application code to an absolute
minumum, because it is expensive to write and changes frequently.

The main reason not to code your logic in the database is to keep your
code portable across multiple databases.   Around year 2000 I realized
pg is the only database I would ever want to use again in my
professional career.

merlin