There's an interesting discussion between Mark and Norman Dunbar on
the firebird-support mailing list:

Firebird and sharding ?
http://tech.groups.yahoo.com/group/firebird-support/message/117553
http://tech.groups.yahoo.com/group/firebird-support/message/117574
http://tech.groups.yahoo.com/group/firebird-support/message/117588
http://tech.groups.yahoo.com/group/firebird-support/message/117620

Quote from Norman's message 117588:

| Not really. Oracle natively supplies connection pools and cursor
| caching for you, if you know how to use them. And this brings me
| back to my initial point above, the developer *should* know how
| to use them.

Quote from Mark's message 117620:

| Most Java applications work like this:
|
| 1) Open connection from datasource (usually: obtain logical connection
| from connection pool behind that datasource)
| 2) Prepare statement
| 3) Set parameters
| 4) execute
| 5) (optional) traverse resultset
| 6) close statement
| 7) repeat steps 3-5 or 2-6
| 8) Close connection (usually: close logical, physical is returned to
| pool)
|
| The lifetime of steps 1-8 is usually very short (eg one request/
| response cycle in a web application).
|
| In this situation a developer cannot easily keep track of the
| connection, and he shouldn't as he only 'owns' the connection for the
| duration of steps 1-8!
| For all intents and purposes to the application developer it will be
| as if he retrieves a new connection every time (and depending on the
| configuration and implementation class of the datasource it could be)!
| So for the application developer there is no way to track statements
| over multiple invocations of steps 1 to 8.
| That is where statement pooling comes in. If statement pooling is
| available/enabled, step 2 will check the statement pool of the physical
| connection and retrieve the prepared statement if available (and
| otherwise prepare a new one), while step 6 will return the statement to
| the statement pool.

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.

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

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

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

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

Does any of it approach reality? What doesn't? Thanks.

Michael