Steve wrote:
> Okay. Darn. While I don't write the queries for the application, I do
> interact with the company frequently. Their considering moving the
> queries into the database with PL/pgSQL. Currently their queries are
> done through ProvIV development using ODBC. Will context switching be
> minimized here by using PL/pgSQL?
Yes, yes, yes! :-)
Or maybe, depending on what you are doing. Moving application code into
the database has the potential to supercharge your system depending on
how it is structured.
Our company has done very detailed performance measurements on the
subject. We converted our COBOL based ERP to PostgreSQL by writing a
libpq wrapper to allow our COBOL runtime to read/write queries to the
database. If you don't know much about COBOL, let's just say it has a
'one record at a time' mentality. (read a record...do something...read a
record...do something...). It is these cases that really want to be
moved into the server.
Here are some rough performance numbers, but they are a pretty good
reflection why pl/pgsql is so good. The procedure in question here will
build a bill of materials for a fairly complex product assembly in an
order entry system. Since all users hate waiting for things, this is a
performance sensitive operation.
The baseline time is the COBOL app's pre-conversion-to-sql time to build
the BOM.
BOM-ISAM: 8 seconds
Using SQL queries instead of ISAM statements, our time suddenly leaps to
BOM-SQL: 20 seocnds.
A long, long, time ago, we implemented prepared statements into our
driver using the parameterized interface.
BOM-SQL (prepared): 10 seconds
We converted the COBOL code to pl/pgsql. The logic is the same, however
easy record aggregations were taken via refcursors were made where
possible.
BOM-PL/PGSQL: 1 second
Even the commercial COBOL vendor's file system driver can't beat that
time when the application is running on the server. Also, pl/pgsql
routines are not latency sensitive, so they can be run over the internet
etc. In addition, having the server execute the business logic actually
*reduced* the cpu load on the server by greatly reducing the time the
server spent switching back and forth from network/processing.
Of course, ours is an extreme case but IMO, the benefits are real.
Merlin
