Re: PERSISTANT PREPARE (another point of view) - Mailing list pgsql-sql

>[snip]> What's wrong with using complex views, stored procedures, functions and> maybe even custom data types to
accomplishwhat 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