Databases Reference
In-Depth Information
of statements for a pool to be 100 or fewer. In this example, when statement
101 is executed, the database server will generate an error.
•
Configure the maximum number of statements for a statement pool to be
equal to or greater than the number of different SQL statements in your
application. For example, suppose the maximum number of statements for
the pool is 50 and the number of static SQL statements in your application is
55. When the application executes statement 51, the statement pool must
close an existing pooled statement to add statement 51 because the pool can-
not exceed 50 statements. In this scenario, the pool manager may have to
switch statements in and out of the pool. This isn't an efficient way to config-
ure statement pooling because the overhead of closing and opening state-
ments causes unnecessary network round trips.
Note
Not all drivers/providers on the market support statement pooling. To
use this feature, make sure you deploy a driver/provider with your data-
base application that does.
We discussed the performance benefits of connection pooling and statement
pooling, and we talked about how multiple applications can use the same con-
nection pool. As we previously explained, all connections in a connection pool
are maintained in the database's memory. If you implement statement pooling
with connection pooling, each pooled connection has its own statement pool
associated with it. Each of these statement pools may contain the prepared state-
ments that the application uses. All these pooled prepared statements are also
maintained in the database's memory.
That isn't the whole picture. A typical application server environment has
numerous connection pools and statement pools that use memory on the data-
base server. Also, other application servers will likely be accessing that same data-
base server, as shown in Figure 8-9. What this means is that your database server
can potentially be a big bottleneck. You need to think about the big picture when
you design your applications to use connection pooling and statement pooling.
