6 JDBC Performance Tips for Java Developers

JDBC Performance tips are a collection of some tried and tested ways of coding and applying process which improves the performance of JDBC code. The performance of core Java application or J2EE web application is very important, especially if its user database in the back end which tends to slow down performance drastically. do you experience your java j2ee web application being very slow (taking few seconds to process simple requests which involve database access, paging, sorting, etc) then the below tips may improve the performance of your Java application? these tips are simple in nature and can be applied to other programming language application which uses the database as back-end. 

6 Best JDBC Performance Tips for Java Developers

Here are four JDBC performance tips, not really super cool or something you never heard and I rather say fundamentals but in practice, many programmers just missed these, you may also be called these database performance tips but I prefer to keep them as Java because I mostly used this when I access the database from Java application.

JDBC Performance Tips 1: Use Cache

Find out how many database calls you are making and minimize those believe it or not if you see the performance in seconds than in most cases the culprit is database access code.

Since connecting to the database requires connections to be prepared, network round trip, and processing on the database side, it's best to avoid database calls if you can work with cached value. 

Even if your application has quite dynamic data having a short time cache can save many database round trips which can boost your java application performance by almost 20-50% based on how many calls got reduced. 

In order to find out database calls just put logging for each DB calls in the DAO layer, even better if you log in and out time which gives you an idea which call is taking how much time.

Java database performance tips 2: Use Database Index

Check whether your database has indexed on columns if you are reading from the database and your query is taking longer than expected than the first thing you should check is whether you have an index on columns that you are using for search (in where clause of the query).

This is the most common error programmers make and believe me there is a huge difference than querying a database that is indexed and the one which is not. 

This tip can boost your performance by more than 100% but as I said its mistake now having proper indexes in your tables so don't do that in the first place. 

Another point which is worth noting is that too many indexes slow insert and update operation so be careful with indexes and always go on suitable and practical numbers like having indexes on fields which most often used for searching like id, category, class, etc. 

And, If you are interested in learning how to use JDBC in the right way I suggest you take a look at these JDBC courses for Java programmers. It's one of the resources on JDBC which not only explains how to use different JDBC classes but also the right way of using it. You can also find lots of JDBC performance tips there.

JDBC performance tips 3: Use PreparedStatement

Use PreparedStatement or Stored Procedure for executing query Prepared Statements are much faster than normal Statement objects as a database can pre-compile them and also cache their query plan. so always use a parametric form of Prepared Statement 

like "select * from table where id=?" , don't use "select * from table where id='" + id "'" which is still a prepared Statement but not parametrized.

You won't get the performance benefit of prepare statements by using the second form. see here for more advantages of Prepared Statement in Java like prevention from SQL Injection.

Java database performance tips 4: Use Database Connection Pool

Use Connection Pool for holding Database Connections. Creating Database connections is slow to process in Java and take a long time. So if you are creating a connection on each request than obviously your response time will be a lot more. 

Instead, if you use Connection pools with an adequate number of connections based upon your traffic or number of concurrent requests to make to the database you can minimize this time. 

Even with Connection pooling few of the first requests may take a little longer to execute till your connection gets created and cached in the pool.

JDBC performance tips 5: Use JDBC Batch Update

Using the JDBC batch update can improve the performance of Java database applications significantly. you should always execute your insert and update queries on Java using batch. 

You can execute batch queries in Java by using either Statement or PreparedStatement. 

Prepared Statement is preferred because of other advantages. Use the executeBatch() method to execute batch queries. 

When you are using MySQL database there is also a setting which significantly improve the performance of your Java application, its called rewriteBatchedStatements and you can see how difference it make. 

JDBC performance tips 6: Disable auto-commit

This is one of those JDBC performance tips which provides substantial benefit by small change. One of the better ways to improve the performance of Java database application is running queries with setAutoCommit(false). 

By default new JDBC connection has there auto-commit mode ON, which means every individual SQL Statement will be executed in its own transaction. 

While without auto-commit you can group SQL statement into a logical transaction, which can either be committed or rolled back by calling commit() or rollback(). 

Also, its significant performance gain when you commit() explicitly. try running same query number of times with and without auto-commit and you can see how much different it make

And, here is a nice summary and diagrams of JDBC Best Practices Java Developers can follow to improve performance of their application:

These Java database application performance tips are very simple in nature and most advanced Java developers already employ these while writing production code, but same time I have seen many Java programmers which doesn't put so much attention until they found there java application is very slow. 

So geeks and experts may not get anything new but for beginners, this is something worth remembering and applying. 

You can also use this Java performance tips as a code review checklist of what not to do while writing Java applications which uses a database in the back-end.

That's all on how to improve the performance of Java programs with database. let me know if you have some other java or database tips which is helpful to boost the performance of java database applications.