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

Richard Huxton wrote:

>> Milan Oparnica wrote:
>>
>> It's simply to complicated to return recordsets through
>>server-side stored procedures. They are obviously designed to do
>>complex data manipulation ...

> Richard wrote:
>I'm not convinced it's always a win one way or another.
>You still haven't said what's "too complicated" about defining a
>function:
>
>CREATE FUNCTION users_at_dotcom(text) RETURNS SETOF users AS $$
> SELECT * FROM users WHERE email LIKE '%@' || $1 || '.com';
>$$ LANGUAGE SQL;
> Richard Huxton
> Archonet Ltd
-------------------------------------------------------------------
Hi Richard,

It sounds like you suggest not using PREPARED statement nor stored
procedures to fetch data. What do you think is the best way ?

The example you posted is the only situation where it's simple to use
stored procedures to fetch data.

--------------------------------------------------------------------
Try to write following simple scenario:

a. Data is retrieved from two tables in INNER JOIN
b. I don't need all fields, but just some of them from both tables

Lets call tables Customers and Orders.

Definition of tables are:
Customers (CustomID INTEGER, Name TEXT(50), Adress TEXT(100))
Orders (OrderID INTEGER, CustomID INTEGER, OrderNum TEXT(10))

Now I need a list of order numbers for some customer:

SELECT C.CustomID, C.Name, O.OrderNum
FROM Customers C INNER JOIN Orders O ON C.CustomID=O.CustomID
WHERE C.Name LIKE <some input parameter>

Can you write this without defining an SETOF custom data type ?
----------------------------------------------------------------------
NOTE! THIS IS VERY SIMPLIFIED REPRESENTATION OF REAL-LIFE STRATEGY.
----------------------------------------------------------------------
We sometimes have JOINS up to 10 tables.

Besides, using report engines (like Crystal Reports) forces you to avoid
queries where column order of the recordset can change. If you built a
report on a query having CutomID,Name,OrderNum columns adding a column
(CustomID,Name,Adress,OrderNum) will require recompiling the report if
you want it to give correct results.

Thats one of the reasons we avoid SELECT * statements. Another is
because some user roles do not have permissions to examine table
structures. In such cases SELECT * returns error.

I hope I managed to present what I meant by "too complicated" when using
stored procedures to fetch data.

PREPARED statements do not suffer from such overhead. They simply return
records as if the statement was prepared in the client.

I will repeat, it took 5 minutes for prepared statement to return
results of the same SQL that took 16 minutes for the stored procedure to
do so. SP was written to return SETOF user type. If you want, I'll send
you the exact SQL and the database. Later we tested other queries and it
was always better performance using prepared statements then stored
procedures with SETOF user defined types.

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