Tuesday, February 20, 2007

Why Prepared Statements are important and how to use them "properly"

Prepared Statement Cache is available as a standard feature of app-server. This feature has to be used judiciously. It work faster only when the particular statements are executed multiple times in the same transaction.

Cache Prepared and Callable Statements
When you use a prepared statement or callable statement in an application , there is considerable processing overhead for the communication between the application server and the database server and on the database server itself. To minimize the processing costs, WebLogic Server can cache prepared and callable statements used in your applications. When an application calls any of the statements stored in the cache, WebLogic Server reuses the statement stored in the cache. Reusing prepared and callable statements reduces CPU usage on the database server, improving performance for the current statement and leaving CPU cycles for other tasks.
Using the statement cache can dramatically increase performance, but you must consider its limitations before you decide to use it.
The WebSphere® Application Server data source optimizes processing of prepared statements. It manages a pool of database connections, as well as an associated cache of prepared statement objects. Prepared statements are cached separately for each connection that executes them.

Why Prepared Statements are important and how to use them "properly"

The top Java EE best practices

IBM WebSphere Developer Technical Journal: The top Java EE best practices