Database connection pool | Dynamic caching
Prepared statement cache
Overview
According to the WebSphere Application Server Information Center, a 10% to 20% performance improvement can be observed with the use of this parameter. To determine the correct setting, the Tivoli Performance Viewer is often used to observe the behavior and minimize cache discards. However, for WebSphere Commerce, because of the high number of SQL statements executed internally, it is not possible to cache all prepared SQL statements in the application server.
The only reliable way to determine the optimal setting for the prepared statement cache is through actual performance measurement in a controlled environment with repeatable workloads. The recommended initial value for the prepared statement cache is 150, and it should be increased in intervals of 50, until no more performance increase is observed.
The prepared statement cache's default is 50 statements/connections. There is a 10-20% performance increase when tuning this parameter. Test performance in a repeatable manner in a controlled environment.
Set the prepared statement cache size
JDBC providers > skyway - WebSphere Commerce JDBC Provider > Data sources > WebSphere Commerce DB2 DataSource skyway > WebSphere Application Server data source properties