Re: [GENERAL] 7.3 Prepared statements

Charles,

You can have the jdbc driver use stored plans, but that is not the
default behavior.  There is a method on the org.postgresql.PGStatement
object to enable using a stored plans.

It is not the default behavior in 7.3 for the following reasons:

1) The functionality on the server is new and therefore there are
probably a few bugs yet to be found.  I didn't want to destabilize the
overall jdbc driver by relying on new functionality.

2) The implementation on the server will not allow all current uses of
JDBC PreparedStatements to work (i.e. prepared statements that do
multiple sql statements in one call, ususally done to reduce number of
network roundtrips when needing to issue many sql calls at the same time).

3) The current implementation on the server will cause a decrease in
performance if you only use the PreparedStatement once.  Since the
implementation of server side prepared statements requires multiple
roundtrips to prepare, execute and close, there will be more network
traffic and thus decreased performance if the JDBC prepared statement is
only used once.  And my experience is that the vast majority of JDBC
prepared statements are only used once.

4) This is version one of the functionality.  We can learn from it to
understand how it can be improved for 7.4.

thanks,
--Barry



Bruce Momjian wrote:
> I would ask on the jdbc lists.  I am Cc'ing them, and removing the
> general list.
>
> ---------------------------------------------------------------------------
>
> Charles H. Woloszynski wrote:
>
>>Bruce:
>>
>>Do you mean that, under JDBC with PG7.3, it will use the stored
>>execution plan, or that we should ask the JDBC driver maintainers to
>>work to use this feature to use the stored executiong plan?
>>
>>I am obviously interested in helping make this happen to improve the
>>overall PostgreSQL solution.  Should I contact someone specific about
>>this to see if there are plans to make this happen in the near future?
>> I think I have a solid framework to test its effectiveness with decent
>>performance monitoring tools already in place.
>>
>>Thanks,
>>
>>Charlie
>>
>>
>>
>>
>>Bruce Momjian wrote:
>>
>>
>>>Sorry, I don't know if it does that yet, but I am sure it will if it
>>>doesn't already.
>>>
>>>---------------------------------------------------------------------------
>>>
>>>Charles H. Woloszynski wrote:
>>>
>>>
>>>
>>>>I realize that this is a bit off topic, but your answer got me thinking.
>>>>Do JDBC Prepared statements get the same saved execution plan support?
>>>>We currently use PreparedStatements in our framework for JDBC access.
>>>>We currently do not retain the PreparedStatement between uses, but if
>>>>PostreSQL caches the execution plan, we may need to change our design.
>>>>
>>>>Thanks,
>>>>
>>>>Charlie
>>>>
>>>>
>>>>Bruce Momjian wrote:
>>>>
>>>>
>>>>
>>>>
>>>>>A view is just syntaxic sugar added to a query that references the view.
>>>>>A preparted statement actually saves the execution plan for repeated
>>>>>execution.
>>>>>
>>>>>---------------------------------------------------------------------------
>>>>>
>>>>>Francisco J Reyes wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>On 17 Dec 2002, Neil Conway wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>On Tue, 2002-12-17 at 10:19, Ericson Smith wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>1. What is the lifetime of the plan created?
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>From the PREPARE reference page:
>>>>>>>
>>>>>>>Prepared queries are stored locally (in the current backend), and only
>>>>>>>exist for the duration of the current database session.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>What is the difference/advantage between a prepared query and a view?
>>>>>>
>>>>>>
>>>>>>---------------------------(end of broadcast)---------------------------
>>>>>>TIP 4: Don't 'kill -9' the postmaster
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>--
>>>>
>>>>
>>>>Charles H. Woloszynski
>>>>
>>>>ClearMetrix, Inc.
>>>>115 Research Drive
>>>>Bethlehem, PA 18015
>>>>
>>>>tel: 610-419-2210 x400
>>>>fax: 240-371-3256
>>>>web: www.clearmetrix.com
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>--
>>
>>
>>Charles H. Woloszynski
>>
>>ClearMetrix, Inc.
>>115 Research Drive
>>Bethlehem, PA 18015
>>
>>tel: 610-419-2210 x400
>>fax: 240-371-3256
>>web: www.clearmetrix.com
>>
>>
>>
>>
>>
>>
>
>