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

2008-07-28 Thread Milan Oparnica
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

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

2008-07-27 Thread Pavel Stehule
Hello 2008/7/28 Milan Oparnica <[EMAIL PROTECTED]>: > 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 diff

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

2008-07-27 Thread chester c young
> > 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

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

2008-07-27 Thread 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 $$ be

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

2008-07-27 Thread Milan Oparnica
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 xcache to track this, but little bit dangerous. could query the database first but the whole idea is to take a load off the db. Thnx for support. The w

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

2008-07-25 Thread chester c young
1. like the idea because right now I'm having to track which prepared statement (sets) are in which session. using xcache to track this, but little bit dangerous. could query the database first but the whole idea is to take a load off the db. 2. perhaps "global" could mean simply that the def

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

2008-07-25 Thread Pavel Stehule
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 fr

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

2008-07-25 Thread Milan Oparnica
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

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

2008-07-25 Thread Milan Oparnica
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

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

2008-07-22 Thread Jeff Williams
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 (Custom

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

2008-07-22 Thread Scott Marlowe
On Tue, Jul 22, 2008 at 12:43 AM, Pavel Stehule <[EMAIL PROTECTED]> wrote: > 2008/7/20 Milan Oparnica <[EMAIL PROTECTED]>: >> 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 m

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

2008-07-21 Thread Pavel Stehule
2008/7/20 Milan Oparnica <[EMAIL PROTECTED]>: > 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 m

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

2008-07-21 Thread 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 o

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

2008-07-21 Thread Craig Ringer
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 S

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

2008-07-21 Thread 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 co

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

2008-07-20 Thread 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. >

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

2008-07-20 Thread 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. >

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

2008-07-20 Thread Milan Oparnica
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 dev

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

2008-07-17 Thread Richard Huxton
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 s

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

2008-07-16 Thread Pavel Stehule
Hello 2008/7/16 Milan Oparnica <[EMAIL PROTECTED]>: > 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 fina

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

2008-07-16 Thread 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-typ

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

2008-07-13 Thread 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

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

2008-07-13 Thread Milan Oparnica
>[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 oth

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

2008-07-11 Thread Steve Midgley
At 04:31 PM 7/11/2008, [EMAIL PROTECTED] wrote: Date: Fri, 11 Jul 2008 23:31:03 + From: Milan Oparnica <[EMAIL PROTECTED]> To: Subject: PERSISTANT PREPARE (another point of view) Message-ID: <[EMAIL PROTECTED]> [snip] What could we gain by introducing a kind of global prepared statement are

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

2008-07-11 Thread Tom Lane
Milan Oparnica <[EMAIL PROTECTED]> 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 t

[SQL] PERSISTANT PREPARE (another point of view)

2008-07-11 Thread Milan Oparnica
Hi, We are new to Postgre, actually we are migrating from MICROSOFT DBMS technologies to...hopefully Postgre. 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. Reason for posting is implementatio