Thread: PERSISTANT PREPARE (another point of view)
Hi,<br /><br />We are new to Postgre, actually we are migrating from MICROSOFT DBMS technologies to...hopefully Postgre.<br/>Our company is involved in ERP business software in Serbia and region, currently counting over 200 clients.Some of them have DB's over 4GB in size.<br /><br />Reason for posting is implementation of PREPARE statements.<br/>I've read a thread "# PREPARE and stuff PFC" on <font size="2">pgsql-performance 2007-06 list and I do agreethat it would not gain performance issues.<br /><br />What could we gain by introducing a kind of global prepared statementarea, is SIMPLICITY of DB DEVELOPMENT AND MAINTENANCE.<br /><br />Here is our point of view:<br /><br />We havean application layer running over db layer. Application layer consists of classes and interfaces and db layer containsdata and various data manipulation structures.<br />Application layer calls SQL statements expecting some datasetsas results (inventory list for instance). What it doesn't care about is HOW is query built (sorting, conditions,etc.) as long as it returns EXPECTED columns. Application simplly calls EXECUTE <statement> (<param1>,<param2>...).Developers working application layer do not interfere with developers working on DB andqueries. Plus MOST queries can be written to be reusable in various situations !!!<br /><br />The idea is: LETS SEPARATESQL STATEMENTS FROM APPLICATION CODE.<br /><br />This way, we can introduce fine tuning to each of our clients withouthaving to recompile our application. We can also work on improvements of queries performance and complexity withoutrecompile of the application layer.<br /><br />Since one company has one set of rules PREPARED statements apply toevery client connected to that database.<br /><br />Now, instead of preparing statements on each connection request (andwe use around 900 prepared statements), why couldn't we simply prepare these statements ONCE and keep them in some globalstorage for future everyday usage.<br /><br />We use this approach for forms & reports creation where Crystal Reportengine creates outlined report based on static prepared statement.<br /><br />This approach is probably not suitablefor large db systems with tons of data, but is very efficient in 90% of small and medium business size databases.<br/><br />Please consider this issue when planning your WISH LIST or hopefully To-do-task-list.<br /><br />Illbe glad to here comments on this topic as well.<br /><br />Milan Oparnica<br />MELANY SOFWARE TEAM<br /></font><br /><hr/>Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy! <a href="http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us"target="_new">Try it!</a>
Milan Oparnica <milan.opa@hotmail.com> writes:
> [ much snipped ]
> What could we gain by introducing a kind of global prepared statement area, is SIMPLICITY of DB DEVELOPMENT AND
MAINTENANCE.
> The idea is: LETS SEPARATE SQL STATEMENTS FROM APPLICATION CODE.
Most people around this project think that the best way to do that is to
push as much logic as you can into server-side stored procedures. That
gives you every advantage that a persistent-prepared-statement feature
would offer, and more besides:
* you can push procedural logic, as well as SQL, out of the application
* you can improve performance by reducing the number of network round
trips needed to accomplish a multi-SQL-statement task
regards, tom lane
At 04:31 PM 7/11/2008, pgsql-sql-owner@postgresql.org wrote: >Date: Fri, 11 Jul 2008 23:31:03 +0000 >From: Milan Oparnica <milan.opa@hotmail.com> >To: <pgsql-sql@postgresql.org> >Subject: PERSISTANT PREPARE (another point of view) >Message-ID: <BLU109-W5078B5ABDE57D183AA6695F8900@phx.gbl> >[snip] >What could we gain by introducing a kind of global prepared statement >area, is SIMPLICITY of DB DEVELOPMENT AND MAINTENANCE. > >Here is our point of view: >[snip] >Now, instead of preparing statements on each connection request (and >we use around 900 prepared statements), why couldn't we simply prepare >these statements ONCE and keep them in some global storage for future >everyday usage. Hi, What's wrong with using complex views, stored procedures, functions and maybe even custom data types to accomplish what you want here? It seems like you could build a lot of "prepared statements" using these tools, providing your application layer developers with a consistent set of interfaces to obtain data that are not tied to the data tables themselves. And allowing them to insert/update/manage tables via structured interfaces as well. Am I missing something? Best, Steve
>[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
Tom Lane wrote: > Most people around this project think that the best way to do that is to > push as much logic as you can into server-side stored procedures. That > gives you every advantage that a persistent-prepared-statement feature > would offer, and more besides: > It's simply to complicated to return recordsets through server-side stored procedures. They are obviously designed to do complex data manipulation, returning few output variables informing the caller about final results. Returning records through sets of user-defined-types is memory and performance waste (please see my previous post as reply to Steve for more details). Plus it's hard to maintain and make improvements to such a system. I hate to see 800 user types made for every query we made as stored procedure. I don't say it couldn't be done through sp but maybe you guys could provide us with much easier (and efficient) way to organize and fetch common SQL statements. Something between VIEWS and STORED PROCEDURES, something like PARAMETERIZED VIEWS or PERSISTENT PREPARED statement. Either way, it would be a place where we could use only PURE SQL syntax. I think it's 90% of what any database application does. > * you can push procedural logic, as well as SQL, out of the application Application developing tools usually offer bigger set of functions, objects, methods etc. than any DB stored procedure language can. There is also debugging, code version control software, team development software and lots of other stuff. It's just more efficient to keep the logic in the application part of the system. Just compare the IDE editors to any DB Admin Tool. >> * you can improve performance by reducing the number of network round> trips needed to accomplish a multi-SQL-statementtask>> regards, tom lane> I couldn't agree more. Such tasks are decidedly for SP's. I'm thinking about 90% of simple to mid-simple tasks (reports, logins, retriving customer and item properties, warehouse inventory and other stuff) that are simple matter of SQL or SQL's in a union with few parameters for filtering the data. I see programmers hard-coding such SQL statements in PHP, C++, Delphi or VB projects. Why? Is it to complex to have it implemented in Postgre engine? We have PREPARE statement, locally for the user. Is it possible to take it globally, for all users, and not to forget it when all connections dye? It is a way to get all of SQL statement out of the application not only "as much logic as you can". As a leader of our development team I find it HIGHLY (and I mean HIGHLY) DESIRABLE. Best regards, Milan Oparnica
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, returning few output variables informing the caller about > final results. Returning records through sets of user-defined-types is > memory and performance waste (please see my previous post as reply to > Steve for more details). Plus it's hard to maintain and make > improvements to such a system. I hate to see 800 user types made for > every query we made as stored procedure. Is this topic completely out of scope in Postgre ? If I'm missing something too obvious or too important, please let me know what. I run over and over through internet and Postgre documentation and still found nothing. Is there a better place to communicate with Postgre developers ? Sincerely, Milan Oparnica
Hello 2008/7/16 Milan Oparnica <milan.opa@gmail.com>: > 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, >> returning few output variables informing the caller about final results. >> Returning records through sets of user-defined-types is memory and >> performance waste (please see my previous post as reply to Steve for more >> details). Plus it's hard to maintain and make improvements to such a system. >> I hate to see 800 user types made for every query we made as stored >> procedure. > > Is this topic completely out of scope in Postgre ? > If I'm missing something too obvious or too important, please let me know > what. > > I run over and over through internet and Postgre documentation and still > found nothing. > try to write prototype and show advantages. I am able to undestand advantages of persistent prep. stamenents, 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. This feature is nice, but question is - who do write it? Actually this problem is solved from outside - with pooling. Regards Pavel Stehule > Is there a better place to communicate with Postgre developers ? > > Sincerely, > > 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 >
Milan Oparnica 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, returning few output variables informing the caller
>> about final results. Returning records through sets of
>> user-defined-types is memory and performance waste (please see my
>> previous post as reply to Steve for more details).
I'm not convinced it's always a win one way or another.
>> Plus it's hard to
>> maintain and make improvements to such a system. I hate to see 800
>> user types made for every query we made as stored procedure.
>
> Is this topic completely out of scope in Postgre ?
> If I'm missing something too obvious or too important, please let me
> know what.
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;
SELECT * FROM users_at_dotcom('archonet'); uid | uname | email
-----+---------+----------------------- 1 | richard | richardh@archonet.com
-- Richard Huxton Archonet Ltd
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.
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 * FROMusers 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
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 * FROMusers 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
I found this link from IBM DB2 developers showing why PERSISTENT PREPARE is a good idea and how could it be implemented. http://www.hoadb2ug.org/Docs/Favero20606.pdf It seems that main benefit (beside efficiency) is memory. Having number of connections all with dozens of PREPARED statements consumes lot of memory. If we put these statements in global space (perhaps not even in RAM memory until needed) we could reuse optimization plans as well as physical space needed to store them. I also found articles: http://archives.postgresql.org/pgsql-hackers/2008-04/msg00867.php and http://archives.postgresql.org/pgsql-performance/2008-03/msg00480.php all about users trying to explain PERSISTENT PREPARE (some refer to it as global prepare). There are also some guys who actually made some code for PERSISTENT PREPARE: http://archives.postgresql.org/pgsql-hackers/2008-03/msg01228.php and http://archives.postgresql.org/pgsql-hackers/2008-03/msg01219.php PEOPLE NEED THIS FEATURE !!! It is not discovering the wheel but it will simplify DB programming and even gain some performance. NONE OF POPULAR SQL DBMS (Oracle, MS SQL, MySQL, Postgre, INTERBASE, FIREBIRD) HAVE THIS FEATURE. WHY ?
Milan Oparnica wrote: > I found this link from IBM DB2 developers showing why PERSISTENT PREPARE > is a good idea and how could it be implemented. [snip] > NONE OF POPULAR SQL DBMS (Oracle, MS SQL, MySQL, Postgre, INTERBASE, > FIREBIRD) HAVE THIS FEATURE. > > WHY ? I suspect that people tend to use SQL or PL/PgSQL stored procedures instead. I'm not 100% sure SQL functions cache their query plans, but I know PL/PgSQL does. Exactly what is gained by the use of persistent prepare over the use of a stored procedure? What would the interface to the feature be through database access drivers like JDBC? Explicit PREPARE GLOBAL or similar, then invocation with EXECUTE ? How would users using increasingly common layers like Hibernate/JPA use it? I'm also curious about how you'd address the possible need for periodic re-planning as the data changes, though AFAIK SQL functions suffer from the same limitation there. I guess I personally just don't understand what the point of the persistent prepare feature you describe is. However, this post that you linked to: http://archives.postgresql.org/pgsql-hackers/2008-04/msg00867.php actually describes a query plan cache, rather than persistent prepare. The post assumes the app will explicitly manage the cache, which I'm not sure is a good idea, but I can see the point of a plan cache. There might be some heuristics Pg could use to decide what to cache and to evict (planner time cost vs memory use, frequency of use, etc) so the app doesn't have to know or care about the plan cache. However, I'm not too sure how you'd match an incoming query to a cached plan, and determine that the plan was still valid, with enough speed to really benefit from the plan cache. Then again, I don't know much about Pg's innards, so that doesn't mean much. Tom Lane responded to that post to point out some of the complexities: http://archives.postgresql.org/pgsql-hackers/2008-04/msg00868.php -- Craig Ringer
Earlier, I wrote: > Exactly what is gained by the use of persistent prepare over the use of > a stored procedure? Er, ENOCOFFEE. Sorry. The benefit is obvious with use of global prepared statements at the wire protocol level rather than via SQL EXECUTE . It's a lot more efficient than EXECUTE or SELECT function(params). It's pretty clear that there are some tricky aspects though, what with schema search paths, role priveleges, etc. -- Craig Ringer
2008/7/20 Milan Oparnica <milan.opa@gmail.com>: > Pavel wrote: > >> >> try to write prototype and show advantages... > > Prototype of what, implementation into Postgre or just efficiency of > PRESISTANT PREPARE idea ? really prototype > >> ...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 ? http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/ Do not forget to close prepared statements - Many memory leaks reported in MySQL Server turned out to be prepare statements or cursors which were forgotten to be closed. Watch Com_stmt_prepare and Com_stmt_close to see if you're closing all prepared statements. In newer versions you can also use prepared_stmt_count variable to track number of open statements diretly. You can also adjust max_prepared_stmt_count variable which limits how many statements can be open at the same time to avoid overload. > > 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? there is some complications with portability - shared memory is slow on windows :( but probably there isn't problem save plan into shared memory. Main difficulties is memory maintaining. > >> 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. PostgreSQL is solo C code http://wiki.postgresql.org/wiki/Development_information > >> 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 ? pgpool?? > > > Thank you for your reply Pavel. regards Pavel > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
On Tue, Jul 22, 2008 at 12:43 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > 2008/7/20 Milan Oparnica <milan.opa@gmail.com>: >> Is it solved in MySQL or they've just tried ? > > http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/ Wow, the discussion at the bottom of that page made me really think. In MySQL you rely on the statement cache to provide data really fast without worrying too much about transactional semantics. In PostgreSQL you set up a set of 1 or more slony machines to act as cache / increase parallel performance. Or just throw more CPU and memory at it along with memcached. Or both.
On 20/07/08 22:16, Milan Oparnica wrote:
> 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>
>
You can do this with cursors, but I'm not sure if you still get the
query caching?
CREATE FUNCTION test(refcursor, input varchar) RETURNS refcursor AS $$
BEGIN OPEN $1 FOR SELECT C.CustomID, C.Name, O.OrderNum FROM Customers C INNER JOIN Orders O ON
C.CustomID=O.CustomID WHERE C.Name LIKE '%' || input || '%'; RETURN $1;
END
$$ LANGUAGE plpgsql;
Then to use:
BEGIN;
SELECT test('curs', <some input parameter>);
FETCH ALL FROM curs;
END;
Jeff
Craig Ringer wrote:
> Milan Oparnica wrote:
>> I found this link from IBM DB2 developers showing why PERSISTENT
>> PREPARE is a good idea and how could it be implemented.
>
> [snip]
>
>> NONE OF POPULAR SQL DBMS (Oracle, MS SQL, MySQL, Postgre, INTERBASE,
>> FIREBIRD) HAVE THIS FEATURE.
>>
>> WHY ?
>
> I suspect that people tend to use SQL or PL/PgSQL stored procedures
> instead. I'm not 100% sure SQL functions cache their query plans, but I
> know PL/PgSQL does.
>
> Exactly what is gained by the use of persistent prepare over the use of
> a stored procedure?
>
Its easier to write and call prepared statements then SP's.
When writing, you just create parameters and SQL body of the statement.
When calling from the application or report engine, all you have to do
is "EXEC <statement name> (<parameters>)".
In case of SP's written as SET OF CustomDataType it's pretty easy to
call ("SELECT * FROM <proc> (<params>)) but its hard to maintain
CutomDataType when changing table structures.
In case of SP's written using CURSORS calling convention is not simple.
BEGIN;
SELECT <proc>('cursor_name', <input parameter>);
FETCH ALL FROM cursor_name;
END;
This usually requires using more complicating ways to define source
recordsets in reports designers. I'm also not sure how cursors use
indexes of the tables. I've run some tests on several millions records
with and without indexes and found out that there was no significant
difference in performance. Plus, for now, CURSORS remain READ-ONLY in PG.
There is one thing more. Prepared statements could be used as table-type (or view-type) datasource. That means we
couldrun prepared statements
over other prepared statements accepting same parameters. This makes
possible creating complex "low-level" BASE queries and dozens of
reporting queries that rely on them. Something like subquery structures:
SELECT A.Fld1,B.Fld2
FROM
(SELECT Fld1 FROM Table1 WHERE xxx) A INNER JOIN Table2 B ON A.Fld1=B.Fld2
WHERE yyy
> What would the interface to the feature be through database access
> drivers like JDBC? Explicit PREPARE GLOBAL or similar, then invocation
> with EXECUTE ?
>
They could remain the same. If possible, when calling EXECUTE <xxx> PG
could try local-then global (or reverse) PREPARED STATEMENTS pool, and
run the statement that was first found. This is greatly simplified. It
would require much detailed planning if this option is to be built in PG.
> How would users using increasingly common layers like Hibernate/JPA use it?
>
I don't now, I never used Hibernate/JPA. Somebody could speak for that.
Making PERSISTANT PREPARE statement available in PG doesn't mean that
everybody must use it.
> I'm also curious about how you'd address the possible need for periodic
> re-planning as the data changes, though AFAIK SQL functions suffer from
> the same limitation there.
>
Perhaps there could be several ways. It could be implemented within
VACUUM procedure. It could re-plan all global prepared statements
according to new circumstances. There should also be a manual
re-planning command of specific statement maybe even with passing
parameters to it so the optimization could be even more efficient.
> I guess I personally just don't understand what the point of the
> persistent prepare feature you describe is. However, this post that you
> linked to:
>
> http://archives.postgresql.org/pgsql-hackers/2008-04/msg00867.php
>
> actually describes a query plan cache, rather than persistent prepare.
> The post assumes the app will explicitly manage the cache, which I'm not
> sure is a good idea, but I can see the point of a plan cache. There
> might be some heuristics Pg could use to decide what to cache and to
> evict (planner time cost vs memory use, frequency of use, etc) so the
> app doesn't have to know or care about the plan cache. However, I'm not
> too sure how you'd match an incoming query to a cached plan, and
> determine that the plan was still valid, with enough speed to really
> benefit from the plan cache. Then again, I don't know much about Pg's
> innards, so that doesn't mean much.
>
> Tom Lane responded to that post to point out some of the complexities:
>
> http://archives.postgresql.org/pgsql-hackers/2008-04/msg00868.php
>
> --
> Craig Ringer
>
I think persistent prepare has to do something with global caching
plans. Preparing statement actually prepares a plan of its execution
giving us the possibility to execute it. With persistent prepare
statement it somehow these plans have to go from local to global. I
agree they shouldn't be managed by app itself. App can only create
requests for creation, modification, execution and removing of such object.
This post is about a point of view. I know we can use stored procedures
and views and other good features of PG to manipulate all data we need.
What I aim is: it can be done in more simple and efficient way with
PERSISTENT PREPARE implementation. Let me point the key benefits:
- huge number of database developers could use this technique without
getting involved with CURSORS and CUSTOM DATA TYPE SETS.
This means a lot of hard-coded sQL commands built into apps itself
easily transfered to database layer.
It also means thousands of Microsoft Jet based applications migrated to
PG. Why ? Because databases are getting larger as project advance and
companies grow, and PG could offer an easier way to upsize then their
own Microsoft SQL engine does.
It also means a lot of new and less experienced database programmers
turn their focus on PG. Its much easier to understand PREPARED
statements than SP constructions returning rowsets.
- EXEC(UTE) is a well known SQL command understood and implemented by
any database engine almost the same way. It's also natively supported by
report designing tools and ADO and ODBC drivers. It means that front end
app doesn't even have to notice if the statement is locally or globally
prepared.
This means that some modules of the system could create global
statements and other modules would simply use them, assuming that they
are in the DB as a part of "setup" process.
Lots of functionality is implemented in PG not all of them used by
developers. My opinion is that implementation of PERSISTENT or GLOBAL
PREPARED STATEMENT would be of much use.
Jeff Williams wrote:
> You can do this with cursors, but I'm not sure if you still get the
> query caching?
>
I can do it with cursors, I don't get query cache but that is for now
not an issue.
Issue is performance.
Although using cursors show better performance over using SET OF user
defined data types, it still swallows huge amount of memory.
Here are the results of a query (I post the query at the end just for
sake of complexity example) running on database of 1.6 GB of data and
around 5 million of records:
Time Approx RAM usage Swaping
PREPARE Statement 5 min. 300 MB None
SP with SET OF 16 min. 800 MB Heavy
SP with cursor 9 min. 800 MB Medium
Environment:
Run on Query Tool of pgAdmin III. It does no data modification.
Number of clients: 1
PostgreSQL version 8.3
Processor P4 2.66 GHZ
RAM memory 1 GB.
There where no other active programs or demanding services running in
the background.
I presume adding more RAM would increase performance but we usually have
10 to 20 clients working at a time. I guess that would put some load.
Besides, fetching records from cursor type SP requires transactional
approach in calling convention (BEGIN....statment...END). At this moment
I don't know if this can be done in our main report designer tool -
Business Objects (former Crystal Reports).
Best regards,
Milan Oparnica
Here is the text of SQL statement:
Note: All fields in join are indexed and every where field is also
indexed except boolean fields.
CREATE FUNCTION aaTestCurs (refcursor, insklid integer, indatdo
timestamp without time zone) RETURNS refcursor AS $$
BEGIN OPEN $1 FOR SELECT 10 AS
Poredak,U.UlID,U.DatDoc,DT.FKomis,DT.FSCObrac,DT.FNivelTot,DT.FNivelParc,
U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.KolAS NUMERIC),8) AS
Kol,SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8) AS TKol, SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,
U.Link,SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,
SU.PorezU,SUN.NNKol,SUN.NPCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0
AS NivelEdited, SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL, U.DTStamp AS
Tabela,U.DopSklID,DT.FPrenos,SK.FRemote FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON DT.DTID =
U.DocTip) INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN
Skladista AS SK ON U.SklID = SK.SklID) LEFT JOIN SasUlazNivel AS SUN ON
SU.SasUlID = SUN.SasUlID WHERE DT.FSCObrac AND NOT(SK.FSkipSCObr <> false) AND
NOT(SK.FSkipNivel <> false) AND U.DatDoc <= InDatDo UNION ALL SELECT 20 AS
Poredak,U.UlID,U.DatDoc,DT.FKomis,DT.FSCObrac,DT.FNivelTot,DT.FNivelParc,
U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.KolAS
NUMERIC),8),SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8), SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS
ImaIzmena,U.Link,
SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,SU.PorezU,SUN.NNKol,
SUN.NPCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0AS NivelEdited, SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS
LastSCena,SK.RacTipSKL,(U.DTStamp -(200000*case when DT.Rank > 50000
then -1 else 0
end)) AS Tabela, U.DopSklID,DT.FPrenos,SK.FRemote FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON DT.DTID =
U.DocTip) INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN
Skladista AS SK ON U.SklID = SK.SklID) LEFT JOIN SasUlazNivel AS SUN ON
SU.SasUlID = SUN.SasUlID WHERE NOT DT.FSCObrac AND NOT(SK.FSkipSCObr <> false) AND
NOT(SK.FSkipNivel <> false) AND U.DocTip <> 31 AND U.DatDoc <= InDatDo UNION ALL SELECT 28 AS
Poredak,U.UlID,U.DatDoc,FalseAS
FKomis,DT.FSCObrac,DT.FNivelTot, DT.FNivelParc,U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS
NUMERIC),8),SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8), SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS
ImaIzmena,
U.Link,SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,SU.PorezU,
SUN.NNKol,SU.RCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0AS
NivelEdited, SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL, U.DTStamp AS
Tabela,U.DopSklID,DT.FPrenos,SK.FRemote FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON DT.DTID =
U.DocTip) INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN
Skladista AS SK ON U.SklID = SK.SklID) LEFT JOIN SasUlazNivel AS SUN ON
SU.SasUlID = SUN.SasUlID WHERE U.DatDoc <= InDatDo AND NOT(SK.FSkipSCObr <> false) AND
NOT(SK.FSkipNivel <> false) AND U.DocTip = 31 AND((SK.RacTipSKL = 3
AND(DT.FMP <> false)) OR(SK.RacTipSKL <> 3 AND(DT.FVP <> false))) UNION ALL SELECT 30 AS Poredak,0 AS
UlID,O.DatDoc,FalseAS
FKomis,False,False,False,2 AS DocTip, 0 AS SasUlID,SO.ElID,-SUM(SO.Kol) AS Kol,0 AS NCena,0 AS TKol, 0 AS
SCena,SK.SklID,0AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,0 AS
Link, 0 AS FCena,0 AS Popust,0 AS Marza,0 AS MCena,0 AS MPorez,0 AS
KKol,0 AS PorezU, 0 AS NNKol,0 AS NPCena,0 AS NTotal,0 AS NTotPP,0 AS NTotPU,0 AS
NivelEdited, 0 AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL, 200000 AS Tabela,0 AS DopSklID,FALSE
ASFPrenos, FALSE AS FRemote FROM(Otpremnice AS O INNER JOIN SasOtp AS SO ON O.OtpID =
SO.OtpID) INNER JOIN Skladista AS SK ON SO.SklID = SK.SklID WHERE O.DatDoc <= InDatDo AND SO.ElID < 1000000
GROUPBY O.DatDoc,SO.ElID,SK.SklID,SK.RacTipSKL ORDER BY 3,Tabela,2; RETURN $1;
END
$$ LANGUAGE plpgsql;
-- Executing query:
BEGIN;
SELECT aaTestCurs('curs', 1,'31.12.2008');
FETCH ALL FROM curs;
END;
Query result with 1 rows discarded.
Query result with 1564318 rows discarded.
Query returned successfully with no result in 531563 ms.
I guess I didn't populate the cursor.
Hello
this is near my implemenation of stored procedures - it's not far from
your view on prepared statements. There result of any unbound select
is directly forwarded to client - there is little bit different
syntax, but idea is same
create procedure foo(params)
as $$
begin select a, b, c from tab1 -- unbound select select ....
end;
and you can call with CALL statement. Curent implementation of SRF
function in plpgsql sn't eficient for big results - it needs
materialised table in memory. Pipeline Oracle's functions are better,
but we are limited one thread architecture.
regards
Pavel Stehule
2008/7/25 Milan Oparnica <milan.opa@gmail.com>:
> Jeff Williams wrote:
>
>> You can do this with cursors, but I'm not sure if you still get the
>> query caching?
>>
>
> I can do it with cursors, I don't get query cache but that is for now not an
> issue.
>
> Issue is performance.
>
> Although using cursors show better performance over using SET OF user
> defined data types, it still swallows huge amount of memory.
>
> Here are the results of a query (I post the query at the end just for sake
> of complexity example) running on database of 1.6 GB of data and around 5
> million of records:
>
> Time Approx RAM usage Swaping
> PREPARE Statement 5 min. 300 MB None
> SP with SET OF 16 min. 800 MB Heavy
> SP with cursor 9 min. 800 MB Medium
>
> Environment:
>
> Run on Query Tool of pgAdmin III. It does no data modification.
> Number of clients: 1
> PostgreSQL version 8.3
> Processor P4 2.66 GHZ
> RAM memory 1 GB.
>
> There where no other active programs or demanding services running in the
> background.
>
>
> I presume adding more RAM would increase performance but we usually have 10
> to 20 clients working at a time. I guess that would put some load.
>
> Besides, fetching records from cursor type SP requires transactional
> approach in calling convention (BEGIN....statment...END). At this moment I
> don't know if this can be done in our main report designer tool - Business
> Objects (former Crystal Reports).
>
> Best regards,
>
> Milan Oparnica
>
>
> Here is the text of SQL statement:
>
> Note: All fields in join are indexed and every where field is also indexed
> except boolean fields.
>
> CREATE FUNCTION aaTestCurs (refcursor, insklid integer, indatdo timestamp
> without time zone) RETURNS refcursor AS $$
> BEGIN
> OPEN $1 FOR
> SELECT 10 AS
> Poredak,U.UlID,U.DatDoc,DT.FKomis,DT.FSCObrac,DT.FNivelTot,DT.FNivelParc,
> U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS NUMERIC),8) AS
> Kol,SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8) AS TKol,
> SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,
> U.Link,SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,
> SU.PorezU,SUN.NNKol,SUN.NPCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0 AS
> NivelEdited,
> SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL,
> U.DTStamp AS Tabela,U.DopSklID,DT.FPrenos,SK.FRemote
> FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON DT.DTID = U.DocTip)
> INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN Skladista AS SK ON
> U.SklID = SK.SklID) LEFT JOIN SasUlazNivel AS SUN ON SU.SasUlID =
> SUN.SasUlID
> WHERE DT.FSCObrac AND NOT(SK.FSkipSCObr <> false) AND NOT(SK.FSkipNivel
> <> false) AND U.DatDoc <= InDatDo
> UNION ALL
> SELECT 20 AS
> Poredak,U.UlID,U.DatDoc,DT.FKomis,DT.FSCObrac,DT.FNivelTot,DT.FNivelParc,
> U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS
> NUMERIC),8),SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8),
> SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,U.Link,
>
> SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,SU.PorezU,SUN.NNKol,
> SUN.NPCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0 AS NivelEdited,
> SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS
> LastSCena,SK.RacTipSKL,(U.DTStamp -(200000*case when DT.Rank > 50000 then -1
> else 0
> end)) AS Tabela,
> U.DopSklID,DT.FPrenos,SK.FRemote
> FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON DT.DTID = U.DocTip)
> INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN Skladista AS SK ON
> U.SklID = SK.SklID) LEFT JOIN SasUlazNivel AS SUN ON SU.SasUlID =
> SUN.SasUlID
> WHERE NOT DT.FSCObrac AND NOT(SK.FSkipSCObr <> false) AND
> NOT(SK.FSkipNivel <> false) AND U.DocTip <> 31 AND U.DatDoc <= InDatDo
> UNION ALL
> SELECT 28 AS Poredak,U.UlID,U.DatDoc,False AS
> FKomis,DT.FSCObrac,DT.FNivelTot,
> DT.FNivelParc,U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS
> NUMERIC),8),SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8),
> SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,
>
> U.Link,SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,SU.PorezU,
> SUN.NNKol,SU.RCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0 AS NivelEdited,
> SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL,
> U.DTStamp AS Tabela,U.DopSklID,DT.FPrenos,SK.FRemote
> FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON DT.DTID = U.DocTip)
> INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN Skladista AS SK ON
> U.SklID = SK.SklID) LEFT JOIN SasUlazNivel AS SUN ON SU.SasUlID =
> SUN.SasUlID
> WHERE U.DatDoc <= InDatDo AND NOT(SK.FSkipSCObr <> false) AND
> NOT(SK.FSkipNivel <> false) AND U.DocTip = 31 AND((SK.RacTipSKL = 3
> AND(DT.FMP <> false)) OR(SK.RacTipSKL <> 3 AND(DT.FVP <> false)))
> UNION ALL SELECT 30 AS Poredak,0 AS UlID,O.DatDoc,False AS
> FKomis,False,False,False,2 AS DocTip,
> 0 AS SasUlID,SO.ElID,-SUM(SO.Kol) AS Kol,0 AS NCena,0 AS TKol,
> 0 AS SCena,SK.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,0 AS Link,
> 0 AS FCena,0 AS Popust,0 AS Marza,0 AS MCena,0 AS MPorez,0 AS KKol,0 AS
> PorezU,
> 0 AS NNKol,0 AS NPCena,0 AS NTotal,0 AS NTotPP,0 AS NTotPU,0 AS
> NivelEdited,
> 0 AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL,
> 200000 AS Tabela,0 AS DopSklID,FALSE AS FPrenos,
> FALSE AS FRemote
> FROM(Otpremnice AS O INNER JOIN SasOtp AS SO ON O.OtpID = SO.OtpID)
> INNER JOIN Skladista AS SK ON SO.SklID = SK.SklID
> WHERE O.DatDoc <= InDatDo AND SO.ElID < 1000000
> GROUP BY O.DatDoc,SO.ElID,SK.SklID,SK.RacTipSKL
> ORDER BY 3,Tabela,2;
> RETURN $1;
> END
> $$ LANGUAGE plpgsql;
>
>
> -- Executing query:
> BEGIN;
> SELECT aaTestCurs('curs', 1,'31.12.2008');
> FETCH ALL FROM curs;
> END;
> Query result with 1 rows discarded.
> Query result with 1564318 rows discarded.
>
> Query returned successfully with no result in 531563 ms.
>
> I guess I didn't populate the cursor.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
1. like the idea because right now I'm having to track which prepared statement (sets) are in which session. using
xcacheto track this, but little bit dangerous. could query the database first but the whole idea is to take a load off
thedb.
2. perhaps "global" could mean simply that the definition is global - if called for session and not exist in session,
thensession prepares it first from the global def. there would need to be a back reference in case the global def was
changedor dropped.
3. don't understand your statement about how prepared statements can be used as subqueries - you mean something like
select .. from tab1 t1 where t1.col1 in( exec prep1(..) )
or exactly what?
chester c young wrote: > 1. like the idea because right now I'm having to track which prepared statement (sets) are in which session. using xcacheto track this, but little bit dangerous. could query the database first but the whole idea is to take a load off thedb. > Thnx for support. The whole idea is to have DBMS support as much SQL related functionality as possible. This way you wouldn't have to wonder if the client has prepared the statement already or not. If you stored it in the public schema then it MUST be there. > 2. perhaps "global" could mean simply that the definition is global - if called for session and not exist in session, thensession prepares it first from the global def. there would need to be a back reference in case the global def was changedor dropped. > Yes, this seems to be a good idea. Something like this would be easier to implement then having the whole process run in some shared memory space. The implementation could by like: 1. App cals SQL like "EXEC <statement_name>" 2. PG Engine looks first in local prepared statements as it does now 3. If it doesn't find it there it looks in public space 4. If it's found there copy both the SQL and the execution plan stored in global space to local process 5. Execute the statement as if it was prepared in local process. Simplified greatly, new implementation could "only" add steps 3 and 4 to current implementation of PREPARED statements. > 3. don't understand your statement about how prepared statements can be used as subqueries - you mean something like > > select .. from tab1 t1 where t1.col1 in( exec prep1(..) ) > > or exactly what? > Well, its best explained on the example. Note that such construction would require extra coding from PG developers. ##1 Lets pretend we have a prepared statement: PREPARE PS_Sector (InSector INTEGER) AS SELECT SectorID,SectorName,SectorTypeFROM CompanySectorsWHERE SectorID = InSector; ##2 Now we can build another statement over the firs one like this: PREPARE PS_SectorExpences (InDateFrom DATETIME, InDateTo DATETIME, InSector INTEGER) ASSELECT S.SectorID,S.SectorName,S.SectorType,E.ExpType,E.AmmountFROM PS_Sector AS S INNER JOIN CompanyExpencesAS EON S.SectorID=E.SectorIDWHERE E.Date>=InDateFrom AND E.Date<=InDateTo; Let me explain. Statement ##2 uses PS_Sector in direct join with a table with expences. By automatically passing "by name" the parameter InSector to the underlaying prepared statement it first returns results from it. Then it joins it to the table CompanyExpences by field SectorID. This may look complicated to implement but it's just a simplified version of implementing SUB-QUERIES. The same effect is gained if you simple replace the PS_Sector reference in ##2 by actual SQL statement it holds. PREPARE PS_SectorExpences (InDateFrom DATETIME, InDateTo DATETIME, InSector INTEGER) ASSELECT S.SectorID,S.SectorName,S.SectorType,E.ExpType,E.AmmountFROM (SELECT SectorID,SectorName,SectorTypeFROMCompanySectorsWHERE SectorID = InSector) AS S INNER JOIN CompanyExpences AS EON S.SectorID=E.SectorIDWHEREE.Date>=InDateFrom AND E.Date<=InDateTo; Only, this way, subquery can come with execution plan from ##1 prepared statemnt. Where could this be used ? Since it's obvious performance downgrade, you must have a good reason for using such approach. Mostly it's reporting !!! Good report relies on data it gets. Sometimes statements needed for reporting purposes include very complex data selection. Usually it takes a long period of time until "everything is covered" by your SELECT query. At that point you can implement the core logic into a "base perpared statement" and then use prepared statements over it to get various aggregations, or different point of views of data. But, the best thing is: WHEN YOU ADD SOMETHING TO BASIC LOGIC OF YOUR SYSTEM, YOU ONLY CHANGE THE BASE PREPARED STATEMENT. If you wrote it cleverly, than all statements built upon it WILL IMMEDIATELY SEE THE NEW IMPLEMENTATION. This is very powerful and efficient way of introducing improvements in the system not having to worry that majority of your reports will show false data until you find time to recompile them. NOTE. You don't want to manipulate huge database systems this way but its very usable for 99% of small and medium business apps (databases up to few gigabytes). It greatly depends on your internal database representation though. Best regards, Milan Oparnica
Pavel Stehule wrote: > Hello > > this is near my implemenation of stored procedures - it's not far from > your view on prepared statements. There result of any unbound select > is directly forwarded to client - there is little bit different > syntax, but idea is same > > > create procedure foo(params) > as $$ > begin > select a, b, c from tab1 -- unbound select > select .... > end; > > and you can call with CALL statement. Curent implementation of SRF > function in plpgsql sn't eficient for big results - it needs > materialised table in memory. Pipeline Oracle's functions are better, > but we are limited one thread architecture. > > regards > Pavel Stehule > Yeah, this would be pretty the same. I just didn't understand if this is already implemented in PG ? I've tried to create a PROCEDURE in QueryTool of pgAdminIII and I get following result: ERROR: syntax error at or near "PROCEDURE" LINE 1: CREATE PROCEDURE aatestubnd(refcursor, insklid integer, inda... ^ ********** Error ********** ERROR: syntax error at or near "PROCEDURE" SQL state: 42601 Character: 8 If I try with CREATE FUNCTION I get this result (as expected): ERROR: function result type must be specified ********** Error ********** ERROR: function result type must be specified SQL state: 42P13 Is this because of QueryTool's limitations or is this feature not yet implemented in Postgre ? Though, CREATE PROCEDURE should be a valid SQL 92 standard procedure. Best regards, Milan Oparnica
> > 2. perhaps "global" could mean simply that
> the definition is global - if called for session and not
> exist in session, then session prepares it first from the
> global def. there would need to be a back reference in
> case the global def was changed or dropped.
> >
>
> Yes, this seems to be a good idea. Something like this
> would be easier
> to implement then having the whole process run in some
> shared memory
> space. The implementation could by like:
>
> 1. App cals SQL like "EXEC
> <statement_name>"
> 2. PG Engine looks first in local prepared statements as it
> does now
> 3. If it doesn't find it there it looks in public space
> 4. If it's found there copy both the SQL and the
> execution plan stored
> in global space to local process
> 5. Execute the statement as if it was prepared in local
> process.
>
> Simplified greatly, new implementation could
> "only" add steps 3 and 4 to
> current implementation of PREPARED statements.
I think it only makes sense if the prepared statement is in the session.
For example, say the Globally submitted statement is stmt: "select * from tab1"
assume Session #1 has search_path=schema1 Session #2 has search_path=schema2
if session #1 attempts to exec stmt, it prepare and use schema1
if session #2 attempts to use stmt, if prepared globally, disaster
in submitted globally, it is not prepared at all until used, but, if ever used, prepared in the current session's
environment. really more like a rule who's preparation is remembered in the session.
Hello 2008/7/28 Milan Oparnica <milan.opa@gmail.com>: > Pavel Stehule wrote: >> >> Hello >> >> this is near my implemenation of stored procedures - it's not far from >> your view on prepared statements. There result of any unbound select >> is directly forwarded to client - there is little bit different >> syntax, but idea is same >> >> >> create procedure foo(params) >> as $$ >> begin >> select a, b, c from tab1 -- unbound select >> select .... >> end; >> >> and you can call with CALL statement. Curent implementation of SRF >> function in plpgsql sn't eficient for big results - it needs >> materialised table in memory. Pipeline Oracle's functions are better, >> but we are limited one thread architecture. >> >> regards >> Pavel Stehule >> > > Yeah, this would be pretty the same. > > I just didn't understand if this is already implemented in PG ? > no - I have workin prototype now - and I am searching sponsors for finishing this work. http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html http://okbob.blogspot.com/2007/11/first-real-procedures-on-postgresql.html But it's really similar to what do you want and it's can be interesting together with plpgpsm for you, because plpgpsm (SQL/PSM) allows one statement procedures, like: create procedure foo(...) as $$ select * from tab; $$ language plpgpsm; so this is standard syntax for persistent prepared statement :) regards Pavel Stehule > I've tried to create a PROCEDURE in QueryTool of pgAdminIII and I get > following result: > > ERROR: syntax error at or near "PROCEDURE" > LINE 1: CREATE PROCEDURE aatestubnd(refcursor, insklid integer, inda... > ^ > > ********** Error ********** > > ERROR: syntax error at or near "PROCEDURE" > SQL state: 42601 > Character: 8 > > > If I try with CREATE FUNCTION I get this result (as expected): > > ERROR: function result type must be specified > > ********** Error ********** > > ERROR: function result type must be specified > SQL state: 42P13 > > > Is this because of QueryTool's limitations or is this feature not yet > implemented in Postgre ? > > Though, CREATE PROCEDURE should be a valid SQL 92 standard procedure. > > 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 >
chester c young wrote: > Session #1 has search_path=schema1 > Session #2 has search_path=schema2 > if session #1 attempts to exec stmt, it prepare and use schema1 > if session #2 attempts to use stmt, if prepared globally, disaster > I'm sorry, I wasn't precise enough. When I said global I meant global in Schema scope. Just like standard stored procedures are. Did you see post from Mr.Pavel.Stehule about PROCEDURES ? Such implementation would have the same effect as global prepare. If it could even support nesting (calling procedure from within another procedure). Let's hope he does his implementation in 8.4. Regards, Milan Oparnica