Performance Tuning Your JDBC WebLogic Applications

Even after all the discussion on connection pooling and multipooling to improve performance, if you don't follow a set of good programming practices, you will still end up having poorly performing applications. This set of guidelines, if followed, will result in better performance:

• While you decide the number of connections in your connection pool, you should estimate the number of concurrent users for the application. Since WebLogic Server's connection pool can multiplex the connections, the number of concurrent users will help to define the size of the connection pool.

• Caching data is another way to reduce the overhead of database interaction. RowSets, a new interface defined as an extension to the SQL packages in the JDBC 2.0 API, provides support for caching data retrieved as a result of executing and SQL query.

• SQL at the database level can also contribute to poor performance and response times. Tuning and optimizing SQL statements is the preferred way to improve performance.

• Enabling support for transactions provides integrity of data but adds significant overhead to performance. Transactions should be used sparingly and only where needed. Transactions are described in detail on Day 14.

• While you design your database, it is a good idea to first estimate the largest possible size of the data entering your columns before you decide the size of the columns. While the database is being updated, if the size of the data being entered is larger than the size of the column, a new row is created altogether, instead of the same row being updated. This overhead can result in a DB condition called chaining.

• If your tables contain historical data, try separating the historical data into another table. If the data is not needed often (for instance, only to generate reports at some regular interval) but is stored in your regular tables, this data is read every time you fire a query, even queries that do not pertain to this historical data.

• Try to design your database transactions so that they are prioritized and so that the database engine is always processing your transactions. Parallel processing of your transactions in sets of logical groups helps speed up response times and improve performance.