JDBC Performance Tuning| Performance Tuning Of JDBC

JDBC Performance Tuning

What Is JDBC?

JDBC is an API for accessing databases in a uniform way.
JDBC provides:
  • Platform independent access to databases
  • Location transparency
  • Transparency to proprietary database issues

JDBC Architecture:

Following are the Parameters useful for tuning the JDBC:

1)   Connection Pool Size:

  • The connection pool size in WebLogic Server can be controlled through the following parameters.
  • Initial Capacity –Maximum Capacity –Capacity Increment
  • Server performance can be improved by tuning these parameters.
  • For applications involving heavy database traffic, consider having Initial and Maximum capacity to be the same.

2)   Shrink Frequency:

  • WebLogic Server periodically shrinks the connection pool to its initial capacity based on the usage.
  • The Shrink Frequency parameter is used to specify the number of seconds to wait before shrinking a connection pool.
  • When set to 0, shrinking is disabled. This can be helpful in a production environment.

3)   Testing Connections:

  • WebLogic Server can test a connection from the connection pool before giving it to a client.
This is specified by enabling the Test Connection On Reserve parameter.
  • Connections can also be tested periodically for validity.
This is specified by the  Test Frequency parameter.
  • Both these parameters can degrade performance.

4)   Row Prefetch:

  • Row prefetching improves performance by fetching multiple rows from the server to the client in one server access.
  • The optimal prefetch size depends on the particulars of the query.
  • In general, increasing this number will increase performance, until a particular value is reached.
     Note: This is applicable only for external clients, not for clients in the same JVM as WebLogic Server.

5)   Statement Cache:

  • The three types of statements in JDBC are:
–Statements (not cached)
–Prepared statements
–Callable statements
  • WebLogic Server provides a Statement Cache where JDBC statements can be cached for reuse.
  • Tuning the cache parameters can improve performance.

6)   PinnedToThread:

  • WebLogic Server pins a database connection from the connection pool to an execution thread the first time an application uses the thread to reserve a connection.
  • This can improve performance, as there is no contention by the threads for reserving a connection from the connection pool.

7)   Batch Updates:

  • A typical application might involve multiple data manipulation statements (insert, update, delete).
  • Combining them as appropriate can help improve performance.
  • Statement.addBatch() and Statement.executeBatch() can help in batching updates.

8)   Combining Transactions:

  • Transaction operations are expensive and can significantly degrade performance.
  • Combining multiple operations into a single transaction can help improve performance.
  • By default, JDBC connections have auto-commit enabled.
–Every JDBC statement is executed as a separate transaction.
–You can use Connection.setAutoCommit(false) to turn this off.
–You can use Connection.start(), Connection.commit(), and Connection.rollback() to demarcate transactions.