Re: [SQL] PERSISTANT PREPARE (another point of view)
Pavel wrote: try to write prototype and show advantages... Prototype of what, implementation into Postgre or just efficiency of PRESISTANT PREPARE idea ? ...but I see some disadvatage too. Mainly you have to manage some shared memory space for stored plans. It's not easy task - MySQL develepoers can talk. Implemenation on postgresql is little bit dificult - lot of structures that lives in processed memory have to be moved to shared memory. Is it solved in MySQL or they've just tried ? We could have only PREP STATEMENT definition stored in shared memory (probably something like stored procedures), and it could be run in local processed memory. We could even assume only fetching data would be used through PREP STATEMENTS for start, and later introduce data modification. Is there some simplified PG algorithm we could use to understand the amount of work needed for introducing such feature to PG? This feature is nice, but question is - who do write it? With a little help form PG developers and good documentation perhaps I could put some programmers from my team on this job. They are mostly C++ programmers but we have Delphi and Java if needed. Actually this problem is solved from outside - with pooling. I'm very interested to learn more about this solution. Can you please send me details or some links where I could research this solution ? Thank you for your reply Pavel. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
