Thread: Prepared Statements
Hi,
i am trying to understand "Prepared Statements". I am asking because i want to
understand the impact of "Prepared statements" to my application.
Actually i use Hibernate, DBCP Connection Pool with Postgresql-JDBC Driver and
Postgresql 8.1.
- 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?
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?
many questions, but i was googling a lot and "Prepared Statement" is a
somewhat ambiguous expression, isn't it? Can someone clarify this to me,
please?
kind regards,
Janning
straight from jdbc2.1 doc http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html Statement Statement object to submit a set of heterogeneous update commands together as a single unit, or batch, to the underlying DBMS i.e. execute Statement without parameters http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html PreparedStatement An element in a batch consists of a parameterized command and an associated set of parameters when a PreparedStatement is used. (i.e. A Statement with params) http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html CallableStatement Multiple sets of input parameter values may be associated with a callable statement and sent to the DBMS together. (For procedures) http://java.sun.com/j2se/1.4.2/docs/api/java/sql/CallableStatement.html Viel Gluck Martin ----- Original Message ----- From: <mljv@planwerk6.de> To: <pgsql-general@postgresql.org> Sent: Wednesday, January 09, 2008 2:02 PM Subject: [GENERAL] Prepared Statements > Hi, > > i am trying to understand "Prepared Statements". I am asking because i want to > understand the impact of "Prepared statements" to my application. > > Actually i use Hibernate, DBCP Connection Pool with Postgresql-JDBC Driver and > Postgresql 8.1. > > - 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? > > 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? > > many questions, but i was googling a lot and "Prepared Statement" is a > somewhat ambiguous expression, isn't it? Can someone clarify this to me, > please? > > kind regards, > Janning > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ >
On Wed, 9 Jan 2008, mljv@planwerk6.de 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. 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. Kris Jurka
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@planwerk6.de 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
On 1/10/08, mljv@planwerk6.de <mljv@planwerk6.de> wrote: > > > > > > 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? > An unnamed prepared query is just a normal one-shot query, but it allows the use of bind variables. You'll find more details in the protocol section of the postgres docs <http://www.postgresql.org/docs/8.3/static/protocol.html>. Regards, Isak
On Thu, 10 Jan 2008, mljv@planwerk6.de wrote: > 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? Exactly. > 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. The JDBC driver does not use SQL level PREPARE / DEALLOCATE calls, but instead uses protocol level commands that are pretty much equivalent. The logging process tries to log sql and protocol level commands the same way which is why it shows up that way in the log. Perhaps the server should log the protocol level deallocate as well? > 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? > Data and stats changes do not trigger a replan. Kris Jurka
Kris Jurka <books@ejurka.com> writes:
> On Thu, 10 Jan 2008, mljv@planwerk6.de wrote:
>> 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?
> Data and stats changes do not trigger a replan.
Note that this is no longer true as of 8.3: a stats update from ANALYZE
(either manual or autovacuum) will trigger invalidation of cached plans.
regards, tom lane
Am Sonntag 13 Januar 2008 00:46:50 schrieb Tom Lane: > Kris Jurka <books@ejurka.com> writes: > > On Thu, 10 Jan 2008, mljv@planwerk6.de wrote: > >> 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? > > > > Data and stats changes do not trigger a replan. > > Note that this is no longer true as of 8.3: a stats update from ANALYZE > (either manual or autovacuum) will trigger invalidation of cached plans. great, i have too look out for 8.3, but i am quite happy with 8.1 because of automatic security updates coming with debian out of the box. I have to look for another way to replan. Maybe i just have to close and reopen my connections from time to time. kind regards Janning