first: thanks a lot for your answer. it already helped me a lot, but i still have some questions: Am Mittwoch, 9. Januar 2008 21:16 schrieb Kris Jurka: > On Wed, 9 Jan 2008, mljv@xxxxxxxxxxxx wrote: > > - I know there is a PREPARE Statement in Postgresql and read the docs. > > - in PostgresqlJDBC i have a prepareThreshold parameter which i left to > > default of 5. > > - in DBCP i have a property "poolPreparedStatements", set to true. Does > > ist just configure prepareThreshold of JDBC or does it maintain a > > statementPool of it's own? > > The postgresql JDBC driver does not have a statement pool of its own, so > the two options prepareThreshold and poolPreparedStatements are > complementary. By itself the JDBC driver will switch to a long term > prepared plan once you've used the exact same PreparedStatement object > prepareThreshold number of times. Since it doesn't pool things behind the > scenes, you've got to retain a reference to the same PreparedStatement > object which is difficult or impossible in many applications. ah! So it doesn't help if it's the same statement, it has to be the same object! So DBCP has a statement pool like a map, say Map<String, PreparedStatement> so it can fetch the reference to already existing prepared Statement by looking at the statement itself, right? But JDBC itself uses the "PREPARE" sql command, right? So the statement is not really unnamed its name is "<unnamed>" like prepare "<unnamed>" as select * from table; execute "<unnamed>"; But i can't see any DEALLOCATE statements in my log file. > This is > where the DBCP statement pool comes in. It proxies PreparedStatement > wrappers to one underlying PG PreparedStatement object so that you can hit > prepareThreshold. > > > In my Log files of postgresql each query is called like this: > > > > EXECUTE <unnamed> [PREPARE: select ...] > > > > I have not found anything about preparing "unnamed" statements. What > > does it mean? > > Unnamed statements are what the driver uses before it hits the > prepareThreshold limit. Once it has determined the statement will be > reused many times it changes to a named statement that has a longer > lifespan. What do you mean with "longer lifespan"? Doesn't the JDBC driver uses the PREPARE Sql Statement and therefore the prepared Statement has the same lifespan as the connection? If so, as connections are pooled and never closed, the prepared Statement will last forever. What if the table analyzes changes and a better execution plan could be found? kind regards, janning ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
