Mark Rotteveel schrieb am 07.04.2012 um 15:07 (+0200):

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

> > (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).

So is it then a bit like RPC, calling prepared remote code through a
handle? Basically, marshalling the parameters; identifying the remote
code by some efficient means, e.g. an ID for a lookup table, so the
server just has to execute the code (after permission checks etc).

> 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.

Okay.

> 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 ;)

I think that's what I meant; my wording was clumsy, though. Norman and
you agree on efficiency being best when unnecessary calls to PREPARE are
avoided. Norman, who admitted to hating Java and not knowing much about
it, appears to see ensuring such avoidance as the job of the developer;
while you see it as the job of the statement pool, which makes sense;
and I think Norman would agree if he were more familiar with Java (and
less inclined to hate it). Preparing statements over and over again
would certainly shift unnecessary work to the database server, and it
would mean more work and waiting in the Java application itself.

> 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.

That seems to mean that it would be good for the statement pool not to
blindly cache everything but rather give the programmer some means to
control which statements end up prepared in the cache and which ones
don't.

> 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.

Yes. Thanks for sharing your understanding.
--
Michael Ludwig