On 7-4-2012 14:20, Michael Ludwig wrote:

> I don't understand how it all ties together. Could you check the
> following assumptions and correct or confirm:
>
> (1) Ultimately, a prepared statement is a handle to a chunk of compiled
> SQL on the database server.

In essence, yes. When a statement is prepared the server decides on the
execution plan and the number and type of parameters it expects for the
statement (and communicates that expectation back to the client).

> (2) The client code - Java or whatever - would delegate to that chunk
> of database server code.

After preparation, the client code only needs to set the parameters and
execute the query (and can do that repeatedly).

> (3) For prepared statements A, B and C living on the database server,
> there may be a pool of such delegators to A, B and C (instanceof
> PreparedStatement) for each connection, or just one pool for all
> connections.

The datasources in org.firebirdsql.pool do provide statement pooling,
but I deprecated those datasources for Jaybird 2.2 because of other
problems with their implementation. They are scheduled for removal in
Jaybird 2.3, and there are no plans to reimplement similar features
(although I might just do it for the challenge if I have the time).

So for Jaybird 2.2 a developer would need to use a third party
datasource that provides statement pooling (eg BoneCP, c3p0, DBCP) to
get the benefit.

> (4) This may depend on whether the database server shares statements
> among connections (Firebird SuperServer?) or gives each connection
> seperate memory, possibly including copies of the stateful (?) code
> for A, B and C (Firebird Classic?). (Really just speculating.)

Statements in Firebird are tied to the connection that created/prepared
the statement, so a pool of statements would be per connection.

> (5) The more work can be shifted to the database server to compile
> code only once, the better overall system efficiency will be.

No work is shifted to the database server: using prepared statements
always involves compiling the statement, whether you pool them or not.
Better yet: statement pooling will reduce the amount of the work on the
server ;)

There is a trade off: keeping statements alive does have a cost in terms
of memory, both on the server and on the client. If an application uses
a great number of different statements (or dynamically generated
queries), the effect may be minimal or maybe even negative because of
eviction before reuse, difference in semantically identical queries (eg
generated aliases being different) and management overhead of the
statement pool.

The positive effect of statement pooling is that it saves time preparing
the statement: compiling the statement + client/server roundtrip are
only required once per connection, assuming no eviction occurs.

--
Mark Rotteveel