How to Improve the Java JDBC Performance

  • Use prepared statements. Use parametrized SQL.
  • Tune the SQL to minimize the data returned (e.g. not ‘SELECT *’).
  • Minimize transaction conflicts (e.g. locked rows).
  • Use connection pooling.
  • Try to combine queries and batch updates.
  • Use stored procedures.
  • Cache data to avoid repeated queries.
  • Close resources (Connections, Statements, ResultSets) when finished with.
  • Select the fastest JDBC driver.
  • If you are not using stored procedures or triggers, turn off autocommit. All transaction levels operate faster with autocommit turned off, and doing this means you must code commits.
  • Use connection pooling, either explicitly with your own implementation, or implicitly via a product that supports connection pooling.
  • Use batch updates (sending multiple rows to the database in one call).
  • Use the type-correct get() method, rather than getObject().
  • Avoid n-way database joins: every join has a multiplicative effect on the amount of work the database has to do. The performance degradation may not be noticeable until large datasets are involved.
  • Avoid bringing back thousands of rows of data: this can use a disproportionate amount of resources.
  • Run an application server and any database servers on separate server machines.
  • One of the most time-consuming procedures of a database application is establishing a connection to the database. Use connection pooling to minimize this overhead
  • Use the same connection to execute multiple statements
  • Avoid distributed transactions (transactions that span mutliple connections).
  • Use scrollable ResultSet (JDBC 2.0).
  • Oracle 9i includes queryable snapshots of the main database which can offload the query to run against the clients local snapshot.
  • Performance should be considered at the start of a project
  • Given a simple SQL statement and a stored procedure call that accomplishes the same task, the simple SQL statement will always execute faster because the stored procedure executes the same SQL statement but also has the overhead of the procedure call itself. On the other hand complex tasks requiring several SQL statements can be faster using stored procedures as fewer network trips and data transfers will be needed.
  • Performance can be better if changes to the database are batched: turn off autocommit; add multiple SQL statements using the Statement.addBatch() method; execute Statement.executeBatch().
  • The ConnectionPoolDataSource (from JDBC3.0) and PooledConnection interfaces provide built-in support for connection pools.
  • Use Connection.setReadOnly(true) to optimize read-only database interactions.
  • Use Connection.nativeSQL() to see how the SQL query will execute in the database to help ensure that the SQL is optimized.
  • Use Connection.nativeSQL() to see how the SQL query will execute in the database to help ensure that the SQL is optimized.
  • Commit the data after the transaction completes rather than after each method call
  • Use the fastest driver available to the database: normally type 4 (preferably) or type 3.
  • Use the Page-by-Page Iterator pattern to repeatedly pass small amounts of data rather than huge chunks.
  • Reusing connections allows a prepared statement to be reused.
  • Database connection pools can take one of two strategies: a limited size pool, where attempts to make connections beyond the pool size must wait for a connection to become idle; or a flexible sized pool with a preferred size which removes idle connections as soon as the preferred size is exceeded (i.e. temporarily able to exceed the preferred size). The fixed size pool is generally considered to be the better choice.
  • Avoid the following common mistakes: Failure to close JDBC result sets, statements, and connections; Failure to remove unused stateful session beans; Failure to invalidate HttpSession.
  • CachedRowSet provides cached result sets that do not require continuous connection to the database, allowing connections to be reused more efficiently.
  • Reuse database connections using a connection pool.
  • Obtain and release pooled conections within each method that requires the resource if the connection is very short (termed “Quick Catch-and-Release Strategy” in the article). However do not release the connection only to use it again almost immediately, instead hold the connection until it will not be immediately needed.
  • Response time increasing too much when database is over populated probably indicates lack of or inappropriate indexing on the database.
  • Use SQL clause with EXPLAIN or similar (e.g. “Explain select * from table where tablefield = somevalue”) to ensure that the database is doing an indexed search rather than a linear searches of large datasets.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: