4.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 JDBC performance by using statement interfaces
1. Choose the right Statement interface
2. Do batch update
3. Do batch retrieval using Statement
4. Close Statement when finished
4.1 Choose right Statement interface
There are three types of Statement interfaces in JDBC to represent the SQL query and 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, but PreparedStatement and CallableStatement can be used for static SQL statements as well. CallableStatement is mainly meant for executing stored procedures.
PreparedStatement gives better performance when compared to Statement because it is pre-parsed and pre-compiled by the database once for the first time and then onwards 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 at database itself that in turn improves JDBC performance. But we loose java portability and we have to depend up on database specific stored procedures.
4.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(“sql query1”);
statement.addBatch(“sql query2”);
statement.addBatch(“sql query3”);
statement.executeBatch();
Example Program:
// to execute a batch of sql commands with single statement.
import java.sql.*;
public class BatchUpdates
{
public static void main(String args[]) throws Exception
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");
Statement stmt=con.createStatement();
con.setAutoCommit(false);
stmt.addBatch("insert into Employee values(498,'SNRao',2000.50)");
stmt.addBatch("delete from Employee where empid=100”);
stmt.addBatch("update Employee set empsal=7777 where empid=200”);
stmt.executeBatch(); // executes all addBatch() statements
System.out.println("Records inserted");
stmt.close();
con.close();
}
}
All three types of statements have these methods to do batch update.
4.3 Do batch retrieval 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
Statement.setFetchSize(30);
Here it retrieves 30 rows at a time for all result sets of this statement.
4.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 JDBC performance as in the following.
Statement.close();
5.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. Do batch retrieval using ResultSet
2. Set up proper direction for processing the rows
3. Use proper get methods
4. Close ResultSet when finished
5.1 Do batch retrieval using ResultSet
This is discussed earlier; anyhow repeated again.ResultSet interface also provides batch retrieval facility like Statement as mentioned above. 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.
5.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);
5.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 cast it 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.
5.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();
6.0 Optimization with SQL Query
This is one of the areas where programmers generally make a mistake If you give a query like:
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("select * from employee where name=RR");
The returned result set contains all the columns data. you may not need all the column data and want only salary for RR.
The better query is "select salary from employee where name=RR"
It returns the required data and reduces unnecessary data retrieval.
=============Other Posting on Performance Tuning==============
1. Core Java Tuning – 1
2. Core Java Tuning – 2
3. Servlets & JSP Tuning
Hi sir,
What is the default connection pool size for tomcat and weblogic?
what is difference between c3p0 and dbcp and proxool ? please tell me i am getting confused.
Thanks & Regards
saj
How to implement connection pooling in Core Java? Could you please explain it or atleast share the link will refer it.
To use connection pooling, you require the practice of a server like Weblogic, WehSphere or Tomact.
Thanks,I did in web applications. Is there any API in JDBC. How to implement in desktop applications?
How to load java class into oracle database? I mean how to call java methods from oracle?
Consult a Oracle lecturer.