Re: Prepared Statements

On Thu, 10 Jan 2008, mljv(at)planwerk6(dot)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