JDBC Performance Tuning
1.0 Introduction
In general Getting database connection is very expensive. If your application requires database connection that is repeatedly opened and closed, then this can become a significant performance issue. In this article let us learn some best way by which we can increase the performance.
2.0 Control transaction- Auto-Commit
In JDBC, transaction is a set of one or more statements that execute as a single unit.
java.sql.Connection interface provides some methods to control transaction they are
public interface Connection {
boolean getAutoCommit();
void setAutoCommit(boolean autocommit);
void commit();
void rollback();
}
JDBC’s default mechanism for transactions:
By default in a JDBC transaction the AutoCommit mode is set to true. This default mechanism gives good facility if we are executing a single statement. But on executing multiple statements, it gives poor performance because the transaction is committed after each statement, which in turn reduces performance.
In order to avoid this issue, set AutoCommit mode to false during transaction and enable it once the statements got executed, this process is called as batch transaction.
We we do as above , then we can use rollback() in catch block to rollback the transaction whenever an exception occurs in our program.
The following code illustrates the batch transaction approach.
private static void updateRecordToTable() throws SQLException {
Connection dbConnection = null;
PreparedStatement pStmt1 = null;
PreparedStatement pStmt2 = null;
String updateEmployeSQL = "UPDATE EMPLOYEE SET USERNAME = ? WHERE USER_ID = ?";
String updateTableSQL = "UPDATE ACCOUNT SET SALARY = ? WHERE USER_ID = ?";
try {
dbConnection = getDBConnection();
// set auto commit to false
dbConnection.setAutoCommit(false);
pStmt1 = dbConnection.prepareStatement(updateEmployeSQL);
pStmt1.setString(1, "Azarudin");
pStmt1.setInt(2, 2001);
pStmt1.executeUpdate();
pStmt2 = dbConnection.prepareStatement(updateTableSQL);
pStmt2.setInt(1, 23000);
pStmt2.setInt(2, 2001);
pStmt2.executeUpdate();
// enable commit now
dbConnection.commit();
dbConnection.setAutoCommit(true);
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (pStmt1 != null) {
pStmt1.close();
}
if (pStmt2 != null) {
pStmt2.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
3.0 Optimization with SQL Query
This is one of the areas where programmers generally make a mistake, when you give a query like below:
String query = select * from employee where name='Azar'; Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(query);
Here the returned resultset contains all the columns data. But in some case you may not need all the column present in the table. For example, in the above example if I want only department in which Azar is working for, then the better query is “select department from employee where name=’Azar’”
This query in turn returns the required data alone and reduces unnecessary data retrieval.
4.0 Explicitly closing JDBC objects
Oracle JDBC objects such as Connection, ResultSet,Statement, PreparedStatement andCallableStatement do not have finalizer methods. If you do not explicitly call the close() method for each of these objects as soon as you are done with them, your programs are likely to run out of resources.
Close JDBC objects in finally block. In addition to closing, assign null value afterwards, so that it will be immediately eligible for garbage collection. Doing these things, will keep your programs nice and tidy.
5.0 Optimization with Statement
Statement interface represents SQL query and execution, and they provide number of methods and constants to work with queries. They also provide some methods to fine tune performance. Programmer may overlook these fine tuning methods that result in poor performance. The following are the tips to improve performance by using statement interfaces
1. Choose the right Statement interface
2. Do batch update
3. Configure the fetch size using Statement
4. Close Statement when finished
5.1 Choose right Statement interface
There are three types of Statement interfaces in JDBC which is used to execute that query, they are Statement, PreparedStatement and CallableStatement.
Statement is used for static SQL statement with no input and output parameters, PreparedStatement is used for dynamic SQL statement with input parameters and CallableStatement is used for dynamic SQL satement with both input and output parameters, in real world scenario CallableStatement is mainly meant for executing stored procedures present in the Database.
PreparedStatement gives better performance when compared to Statement because it is pre-parsed and pre-compiled by the database for the first time and then after the first usage, it reuses the parsed and compiled statement. Because of this feature, it significantly improves performance when a statement executes repeatedly, it reduces the overload incurred by parsing and compiling.
CallableStatement gives better performance when compared to PreparedStatement and Statement when there is a requirement for single request to process multiple complex statements. It parses and stores the stored procedures in the database and does all the work in database itself, which in turn improves performance.
5.2 Do batch update
You can send multiple queries to the database at a time using batch update feature of statement objects this reduces the number of JDBC calls and improves performance. Here is an example of how you can do batch update.
statement.addBatch("Query1");
statement.addBatch("Query2");
statement.addBatch("Query3");
statement.executeBatch();Example Program:
// Here is an example to show you how to insert few records in batch process, via JDBC PreparedStatement.
dbConnection.setAutoCommit(false);//commit trasaction manually String insertTableSQL = "INSERT INTO EMPLOYEE" + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES" + "(?,?,?,?)"; PreparedStatement = dbConnection.prepareStatement(insertTableSQL); preparedStatement.setInt(1, 789); preparedStatement.setString(2, "Azarudin"); preparedStatement.setString(3, "system"); preparedStatement.setTimestamp(4, getCurrentTimeStamp()); preparedStatement.addBatch(); preparedStatement.setInt(1, 790); preparedStatement.setString(2, "Nilafar"); preparedStatement.setString(3, "system"); preparedStatement.setTimestamp(4, getCurrentTimeStamp()); preparedStatement.addBatch(); preparedStatement.executeBatch(); dbConnection.commit();
All three types of statements have these methods to do batch update.
5.3 Configure the fetch size using Statement
You can get the default number of rows that is provided by the driver. You can improve performance by increasing number of rows to be fetched at a time from database using setFetchSize() method of the statement object.
Initially find the default size by using
Statement.getFetchSize(); and then set the size as per your requirement.
Connection connection = DriverManager.getConnection("");
Statement statement = connection.createStatement();
// configure the fetch size
statement.setFetchSize(1000);
ResultSet resultSet = statement.executeQuery("someQuery");
Here it retrieves 1000 rows at a time.
5.4 Close Statement when finished
Close Statement object as soon as you finish working with that; it explicitly gives a chance to garbage collector to recollect memory as early as possible which in turn effects performance as in the following.
Statement.close();
6.0 Optimization with ResultSet
ResultSet interface represents data that contains the results of executing a select SQL Query and it provides a number of methods and constants to work with this data. It also provides methods to fine tune retrieval of data to improve performance. The following are the fine tuning tips to improve performance by using ResultSet interface.
1. Configure the fetch size using ResultSet
2. Set up proper direction for processing the rows
3. Use proper get methods
4. Close ResultSet when finished
6.1 Configure the fetch size using ResultSet
ResultSet interface also provides batch retrieval facility like Statement. It overrides the Statement behaviour. Initially find the default size by using ResultSet.getFetchSize(); and then set the size as per requirement
ResultSet.setFetchSize(50);
This feature significantly improves performance when you are dealing with retrieval of large number of rows like search functionality.
6.2 Setup proper direction of processing rows
ResultSet has the capability of setting the direction in which you want to process the results, it has three constants for this purpose. They are,
FETCH_FORWARD, FETCH_REVERSE, FETCH_UNKNOWN
Initially, find the direction by using
ResultSet.getFetchDirection(); and then set the direction accordingly
ResultSet.setFetchDirection(FETCH_REVERSE);
6.3 Use proper getxxx() methods
ResultSet interface provides a lot of getxxx() methods to get and convert database data types to java data types and is flexible in converting non feasible data types. For example,
getString(String columnName) returns java String object. columnName is recommended to be a VARCHAR OR CHAR type of database but it can also be a NUMERIC, DATE etc.
If you give non recommended parameters, it needs to be cast to proper java data type that is expensive. For example, consider that you select a product’s id from huge database which returns millions of records from search functionality, it needs to convert all these records that is very expensive. So always use proper getxxx() methods according to JDBC recommendations. That numeric field data should retrieved as getInt() and varchar is to be retrieved as getString()etc.
6.4 Close ResultSet when finished
Close ResultSet object as soon as you finish working with ResultSet object even though Statement object closes the ResultSet object implicitly when it closes, closing ResultSet explicitly gives chance to garbage collector to recollect memory as early as possible because ResultSet object may occupy lot of memory depending on query.
ResultSet.close();
7.0 Fetch small amount of data iteratively instead of fetching whole data at once
In general Applications require to retrieve huge data from the database using JDBC in operations like searching data.
In this case the application might return the whole result set at once.
This process takes lot of time and has an impact on performance.
The solution for the problem is
1. Cache the search data at the server-side and return the data iteratively to the client. For example, the search returns 1000 records, return data to the client in 10 iterations where each iteration has 100 records.
2. Use Stored procedures to return data iteratively. This does not use server-side caching rather server-side application uses Stored procedures to return small amount of data iteratively.
Out of these the second solution gives better performance because it need not keep the data in the in-memory.
The first procedure is useful when the total amount of data to be returned is not large.
