functions vs embedded SQL

It seems you might be referring to PL/PgSQL (not SQL/pgsql).
All the "PL"s , or Procedural Languages, are server-side programming, JDBC
and php are client-side.

PLs are used when you need the database to do some processing before it sends
the response to the client. PLs are most helpful when used to reduce the
number of seperate transactions a client needs to execute to perform one
function, or reduce the amount of unnecessary data that is sent to the client
(data that the client only needs as an intermediate step toward retrieving
the required data, or inserting the required data).

Moreover, PLs can be aggregate functions, or functions that operate on all of
the records in the result set. This can be very helpful if, for example, you
are performing a statistical calculation on the values in a column. You would
only have to return the result to the client, the server can do the
calculating (which is much more efficient, especially in terms of
communication between the client and the sever).

You need client programming either way. Without a client, the server is
useless :)

Regards,
Jeff Davis

oh typo-- I mean where language = sql or language = plpgsql

In MS SQL Server you get a performance kick when using stored procs because the optimizer doesn't have to figure out the best way to execute the query. The query plan is saved and when the procedure is called it executes the saved plan. Any such similar setup in Postgres? Generally I find that embedded sql makes for a more maintainable/portable application but deep down inside I like write stored procedures/functions so I'm looking for an excuse :)

culley

Jeff Davis <list-pgsql-general@dynworks.com> 11/06/01 12:51PM >>>

It seems you might be referring to PL/PgSQL (not SQL/pgsql).
All the "PL"s , or Procedural Languages, are server-side programming, JDBC
and php are client-side.

PLs are used when you need the database to do some processing before it sends
the response to the client. PLs are most helpful when used to reduce the
number of seperate transactions a client needs to execute to perform one
function, or reduce the amount of unnecessary data that is sent to the client
(data that the client only needs as an intermediate step toward retrieving
the required data, or inserting the required data).

Moreover, PLs can be aggregate functions, or functions that operate on all of
the records in the result set. This can be very helpful if, for example, you
are performing a statistical calculation on the values in a column. You would
only have to return the result to the client, the server can do the
calculating (which is much more efficient, especially in terms of
communication between the client and the sever).

You need client programming either way. Without a client, the server is
useless :)

Regards,
Jeff Davis

I'm pretty sure that Postgresql does NOT save the query plan with stored
procedures or views, so there's no performance gain from that. There is also
never a performance loss from running a stored procedure with a query plan
that's no longer optimal, as sometimes happens with MS SQL Server. (Or used to
as of version 7.0 anyway...) Any performance gains from stored procedures in
Postgresql would come from making fewer trips between the server and client, and
from anything done in them that happens to run faster than it would on the
client. There are also other more general reasons to use stored procs, but you
seem to be familiar with them. HTH,

Wes Sheldahl

"Culley Harrelson" <Culley_Harrelson%pgn.com@interlock.lexmark.com> on
11/06/2001 04:18:18 PM

To: list-pgsql-general%dynworks.com@interlock.lexmark.com
cc: pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
Sheldahl/Lex/Lexmark)
Subject: Re: [GENERAL] functions vs embedded SQL

oh typo-- I mean where language = sql or language = plpgsql

In MS SQL Server you get a performance kick when using stored procs because the
optimizer doesn't have to figure out the best way to execute the query.� The
query plan is saved and when the procedure is called it executes the saved
plan.� Any such similar setup in Postgres?� Generally I find that embedded sql
makes for a more maintainable/portable application but deep down inside I like
write stored procedures/functions so I'm looking for an excuse :)

culley

Jeff Davis <list-pgsql-general@dynworks.com> 11/06/01 12:51PM >>>

It seems you might be referring to PL/PgSQL (not SQL/pgsql).
All the "PL"s , or Procedural Languages, are server-side programming, JDBC
and php are client-side.

PLs are used when you need the database to do some processing before it sends
the response to the client. PLs are most helpful when used to reduce the
number of seperate transactions a client needs to execute to perform one
function, or reduce the amount of unnecessary data that is sent to the client
(data that the client only needs as an intermediate step toward retrieving
the required data, or inserting the required data).

Moreover, PLs can be aggregate functions, or functions that operate on all of
the records in the result set. This can be very helpful if, for example, you
are performing a statistical calculation on the values in a column. You would
only have to return the result to the client, the server can do the
calculating (which is much more efficient, especially in terms of
communication between the client and the sever).

You need client programming either way. Without a client, the server is
useless :)

Regards,
��� Jeff Davis

At 17:05 06/11/01 -0500, you wrote:

I'm pretty sure that Postgresql does NOT save the query plan with stored
procedures or views, so there's no performance gain from that. There is also
never a performance loss from running a stored procedure with a query plan
that's no longer optimal, as sometimes happens with MS SQL Server. (Or
used to
as of version 7.0 anyway...) Any performance gains from stored procedures in
Postgresql would come from making fewer trips between the server and
client, and
from anything done in them that happens to run faster than it would on the
client. There are also other more general reasons to use stored procs,
but you
seem to be familiar with them. HTH,

Optimization can only be achieved at database design level.
Most databases need to be re-designed to benefit from triggers, PL/pgSQL
and cron jobs.

/JMP