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

Something like that, however, the permissions are resolved on the
statement preparation stage, so sharing prepared statements between the
connections is not possible. And caching of the prepared statements for
a single connection can happen on the client side.

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

There is one more issue with the caching of the prepared statements. If
you have a prepared statement which involves some tables or views, any
DDL operation on that table or view (or procedure) will raise "object in
use" exception.

So, if caching happens in some pool, the pool should provide methods to
close all cached statements in order to release the database objects.

One more issue with the cached prepared statements is that they use the
execution plan according to statistics collected on preparation time.
Things might change significantly in the runtime, so re-preparing the
statements is "a very good thing" (tm).

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

Many years ago, when the half of the world was covered with ice and the
mammoths were walking on the streets, I did performance tests with
connection pool on/off and prepared statement pool on/off. The
connection pooling had very little effect on the performance, but the
pooling of the prepared statements improved the performance up to 2x.

On the other hand, in most Java applications people use some O/R mapping
layer which provide prepared statement pooling internally.

Roman