JDBC Performance Optimization Tips

Java database connectivity (JDBC) is the JavaSoft specification of a standard application programming interface (API) that allows Java programs to access database management systems. The JDBC API consists of a set of interfaces and classes written in the Java programming language. Using these standard interfaces and classes, programmers can write applications that connect to databases, send queries written in structured query language (SQL), and process the results. JDBC is oriented towards relational databases.

Though it is not standard anymore to use JDBC directly into your application because we got many more robust APIs to do this job for us e.g. hibernate and iBatis. But, if you are still struck in there due to specific requirements or simply you are learning it, then below suggestions will help you in writing more fast and efficient code.

JDBC-Icon

Sections in this post:
Use Object Pooling
Consider MetaData Performance
Choose Commit Mode carefully
Save Some Bytes On Network Traffic

Let’s directly jump into discussion.

Use Object Pooling Almost Always

Object pooling can happen at two sides:

1) Connection Pooling: Database connections are often expensive to create because of the overhead of establishing a network connection and initializing a database connection session in the back end database. In turn, connection session initialization often requires time consuming processing to perform user authentication, establish transactional contexts and establish other aspects of the session that are required for subsequent database usage.

Additionally, the database’s ongoing management of all of its connection sessions can impose a major limiting factor on the scalability of your application. Valuable database resources such as locks, memory, cursors, transaction logs, statement handles and temporary tables all tend to increase based on the number of concurrent connection sessions.

Enabling Connection pooling allows the pool manager to keep connections in a “pool” after they are closed. The next time a connection is needed, if the connection options requested match one in the pool then that connection is returned instead of incurring the overhead of establishing another actual socket connection to the server.

By the way, you don’t need to implement your own logic for connection pool manager. You can use some facilities provided on your server. Example: http://people.apache.org/~fhanik/jdbc-pool/jdbc-pool.html

2) Statement Pooling:  Starting from JDBC 3.0, JDBC standards define a statement-caching interface. Setting the MaxPooledStatements connection option enables statement pooling. Enabling statement pooling allows the driver to re-use Prepared Statement objects. When Prepared Statements are closed they are returned to the pool instead of being freed and the next Prepared Statement with the same SQL statement is retrieved from the pool rather than being instantiated and prepared against the server.

Statement caching can do the following:

  1. Prevent the overhead of repeated cursor creation
  2. Prevent repeated statement parsing and creation
  3. Reuse data structures in the client

Please ensure your driver support this feature, and is enabled by default or not. A sample code can be something like this if you are doing it programatically.

Properties p = new Properties();
p.setProperty("user", "root");
p.setProperty("password", "password");
p.setProperty("MaxPooledStatements", "200");

connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/JDBCDemo", p);

Read more about statement pooling here: http://docs.oracle.com/cd/B28359_01/java.111/b31224/stmtcach.htm

Consider MetaData Performance Also

If you are dealing with metadata into your code then it is another important area to look after. Here the first tip is to use as many parameters (or filters) to fetch metadata as much you can specify. For example, don’t call getTables like this:

DatabaseMetaData dbmd = connection.getMetaData();
ResultSet rs = dbmd.getTables(null,null,null,null);

Specifying at least the schema will avoid returning information on all tables for every schema when the request is sent to the server:

DatabaseMetaData dbmd = connection.getMetaData();
ResultSet rs = dbmd.getTables(null,"testDB",null,null);

Secondly, remember that most JDBC drivers populate the ResultSetMetaData object at fetch time when the needed data is returned in select queries. Use this information instead of getting data from DatabaseMetaData which is addionational request and avoidable in most cases.

selectStmt = connection.createStatement();
ResultSet rs = selectStmt.executeQuery("SELECT ID,FIRST_NAME,LAST_NAME,STAT_CD FROM EMPLOYEE WHERE ID <= 10");

ResultSetMetaData rsmd = rs.getMetaData();
rsmd.getColumnCount();
rsmd.getColumnName(0);
rsmd.getColumnType(0);
rsmd.getColumnTypeName(0);
rsmd.getColumnDisplaySize(0);
rsmd.getPrecision(0);
rsmd.getScale(0);
TIP : Instead of using getColumns to get data about a table, consider issuing a dummy query and using the returned ResultSetMetaData which avoids querying the system tables!

Choose Commit Mode carefully

When writing a JDBC application, make sure you consider how often you are committing transactions. Every commit causes the driver to send packet requests over the socket. Additionally, the database performs the actual commit which usually entails disk I/O on the server. Consider removing auto-commit mode for your application and using manual commit instead to better control commit logic.

Code to use is:

Connection.setAutoCommit(false);

Save Some Bytes On Network Traffic

To reduce network traffic, following suggestions can be looked and adapted in appropriate for your application.

  1. Use addBatch() instead of using Prepared Statement to insert data when working with high volume clients. This sends multiple insert requests in a single network packet and save some bytes for you.
  2. Do not use “select * from table”. Instead specify column names which are actually needed. I will suggest that make it a practice because many times we are doing it without realizing it’s negative impacts. Just imagine if you do this in a table where you are storing BLOBs also. You fetch such heavy objects from database and do not use it. What a waste.
  3. Ensure that your database is set to the maximum packet size and that the driver matches that packet size. For fetching larger result sets, this reduces the number of total packets sent/received between the driver and server.

That’s all for this post. Post a comment if you have some doubts.

Happy Learning !!

Was this post helpful?

Join 7000+ Fellow Programmers

Subscribe to get new post notifications, industry updates, best practices, and much more. Directly into your inbox, for free.

8 thoughts on “JDBC Performance Optimization Tips”

  1. Hi,

    You’ve described in the first point about the connection pooling and I checked the link you provided and there’s an example using connection pooling and I’ll paste half of it below.

    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.Statement;

    import org.apache.tomcat.jdbc.pool.DataSource;
    import org.apache.tomcat.jdbc.pool.PoolProperties;

    public class SimplePOJOExample {

    public static void main(String[] args) throws Exception {
    PoolProperties p = new PoolProperties();
    p.setUrl(“jdbc:mysql://localhost:3306/mysql”);
    p.setDriverClassName(“com.mysql.jdbc.Driver”);
    p.setUsername(“root”);
    p.setPassword(“password”);
    p.setJmxEnabled(true);
    p.setTestWhileIdle(false);
    p.setTestOnBorrow(true);

    and you’ve mentioned about ‘Statement pooling’ in second point and you provided an example like this.

    Properties p = new Properties();
    p.setProperty(“user”, “root”);
    p.setProperty(“password”, “password”);
    p.setProperty(“MaxPooledStatements”, “200”);

    connection = DriverManager.getConnection(“jdbc:mysql://localhost:3306/JDBCDemo”, p);

    My question is if we use ‘Connection Pooling’ which you’ve explained in first point, will the ‘Statement Pooling’ also happen? or is there a way to do ‘Statement Pooling’ using PoolProperties class (org.apache.tomcat.jdbc.pool.PoolProperties) or do we have to do these Connection Pooling using PoolProperties class & Statement Pooling using Properties class both in the same application? Can you please explain?

    Thank you!

    Reply
  2. Hi Sir,
    How to achieve connection Pooling in Core Java plain JDBC programming. If I want to proceed 5-6 millions of records. How can I achieve /process it in plain JDBC with connection Pooling. Here I need to use ORACLE.

    Can you please help me on that.

    Reply
    • I will suggest you to directly use already available connection pooling libraries e.g. . If you wan to do it your own, the download them and see how they achieve so fast performance and implement those things in our own way.

      Reply
  3. Hi sir,Iam having one small doubt sir.iam using ConnectionPooling (Tomcat) for db connection,and all attributes were set like max_poolsize,max_wait etc my Question is, if all the connections in the pool are busy,when new request arrives then tomcat will wait for any connection to get free upto the time set in max_wait attribute or directly it will throw error or how tomcat will handle sir.Correct me if iam wrong sir.

    Reply

Leave a Comment

HowToDoInJava

A blog about Java and its related technologies, the best practices, algorithms, interview questions, scripting languages, and Python.