Skip site navigation (1) Skip section navigation (2)

Re: Under what circumstances does PreparedStatement use stored

From: Barry Lind <blind(at)xythos(dot)com>
To: Alexander Staubo <alex(at)byzantine(dot)no>
Cc: James Robinson <jlrobins(at)socialserve(dot)com>,pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Under what circumstances does PreparedStatement use stored
Date: 2004-04-09 21:21:11
Message-ID: 407713C7.6030407@xythos.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
You can call PGStatement.setUseServerPrepare() to cause the driver to 
use a prepared plan for that statement.

--Barry


Alexander Staubo wrote:
> Afaik: As of 7.2, PostgreSQL now supports prepared plans. However, I do 
> believe this requires explicit actions on part of the client (see 
> documentation section 43.1.2).
> 
> Unfortunately, PostgreSQL's JDBC driver does not support server-prepared 
> statements yet. Every time you execute some statement, the statement's 
> SQL text is sent in its entirety to the back end.
> 
> (Batching statements does not help much, other than to reduce the number 
> of client-server roundtrips.)
> 
> Alexander.
> 
> on 2004-04-09 16:51 James Robinson wrote:
> 
>> Subject pretty much tells it all -- under what circumstances does 
>> PreparedStatement use backend-based stored plans, and when are those 
>> plans removed from the database?
>>
>> I'm using JBoss which uses PreparedStatement for all of its queries, 
>> and it uses its own datasource implementation. A typical page hit 
>> involves:
>>
>>     1) connection yanked from the datasource, transaction started.
>>
>>     2) Various CMP finders / accessor SQL statements run using that 
>> connection / transaction, but typically only one execution of a query 
>> for each distinct query pattern. Inbetween each bean method the pooled 
>> connection is closed, but I highly suspect that the same underlying 
>> connection must be returned to the next bean in line enrolled in the 
>> same transaction.
>>
>>     3) session bean method completes, transaction committed, 
>> connection probably cleanly returned to the connection pool, but 
>> available to be yanked 'next time'. Idle connections are aged out 
>> periodically and finally closed.
>>
>>  From watching the statement logs on the backend-side,  I don't 
>> believe that I'm getting any backend stored plans in action (using PG 
>> 7.4.2 and JDBC from CVS tip on devel boxes). Within a single 
>> transaction, the odds of JBoss preparing the same statement more than 
>> once are low, but across transactions, they are high, assuming the 
>> same session bean methods called, so there may be some benefit if 
>> somehow, magically, the same preparsed backend-plan from the previous 
>> run was chosen and used.
>>
>> Or I may just be completely out of luck -- closing a PreparedStatement 
>> if/when backend cached-plans (can't remember their real name -- grr) 
>> are being used by the JDBC driver would then probably tell the backend 
>> to cleanup said plan, eh?
>>
>> I love O/R.
>>
>> ----
>> James Robinson
>> Socialserve.com
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>>
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)


In response to

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2004-04-09 23:04:51
Subject: Re: Under what circumstances does PreparedStatement use stored
Previous:From: Alexander StauboDate: 2004-04-09 20:26:53
Subject: Re: Under what circumstances does PreparedStatement use stored

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group