Re: [SQL] PERSISTANT PREPARE (another point of view)

>[snip]
> What's wrong with using complex views, stored procedures, functions and
> maybe even custom data types to accomplish what you want here?

Hi Steve,

Correct me if I'm wrong, but views can not accept parameters, and stored
procedures require defining sets of custom data types or some other
complicated ways in order to simply accept some parameters and return
some recordsets useful to the calling application.

In 90% of cases we simply call same SQL statements with slightly
different parameters and then pass it to business logic of the program.
SP's are fine, they allow complex looping, nesting, variable declaration
etc. but 90% of time we just need a SQL with few parameters and a
resulting recordsets.

Just try to return SELECT * FROM <table> WHERE <field> LIKE <condition>
using SP!
You need to define a SET OF <table type> and then populate the result
into this set consuming both time and memory (it states so in Postgre
documentation).
You can't write it as a view because it has a <condition> as a parameter.
What's the best solution having in mind that this query will be called
hundreds of times by all clients connected to DB?

Moreover, I've run absolutely same SQL statement as a PREPARED statement
and as a Stored procedure returning SET OF CUSTOM TYPE. It contained
several union queries over a database of 6 millions of records returning
a recordset of 1.5 millions of records. It took 5 min. for the prepared
statement to return the results vs. 16 minutes that was required by SP.
Memory consumption in case of prepared statement was around 300 MB while
it took over 800MB for SP.

It could be that there is a more efficient way to return recordsets from
SP's that I do not know, but I couldn't find it in Postgre documentation.

Besides, table definitions tend to change during time. How often did you
add columns to a table? In this case custom data types must be upgraded,
as well as EVERY stored procedure using that type. In case of prepared
statements, you can upgrade only those you need to. This is extremely
useful for building and improving reports.

[*** not important, just a presentation of something we found useful in
other engines ***]

There is an interesting concept in JET engine (with all of deficiency of
the engine itself); it allows building PREPARED statements over other
PREPARED statements automatically passing parameters by name to the
underlying PREPARED statement. This concept allows for realization of a
base SQL statement preparing data for profit&loss report, and then using
it in another SQL statement by adding joins to some specific tables
(sectors and employees for instance). That way within minutes you can
produce a profit&loss by sectors, or by employees or whatever.

What is the magic part, if we introduce new type of documents to our
program that do influence profit&loss then we simply change the BASE
prepared statement. And ALL reports based on it get "upgraded"
automatically.

Best regards,
Milan Oparnica

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql