Sunday, January 15, 2017

6 JDBC Performance Tips for Java Developers

JDBC Performance tips are collection of some tried and tested way of coding and applying process which improves performance of JDBC code. Performance of core Java application or J2EE web application is very important, especially if its using database in back end which tend to slow down performance drastically. do you experience your java j2ee web application to be very slow (taking few seconds to process simple requests which involves database access, paging, sorting etc) than below tips may improve performance of your Java application. these tips are simple in nature and can be applied to other programming language application which uses database as back-end. If you are interested learning how to use JDBC in right way I suggest you to take a look at Practical database programming with Java book by Ying Bai. It's one of the rare book in 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 for Java Developers

how to improve performance Java database application 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 called this database performance tips but I prefer to keep them as Java because I mostly used this when I access 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 performance in seconds than in most cases culprit is database access code. since connecting to database requires connections to be prepared, network round trip and processing on database side, its best to avoid database call 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 trip 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 call in DAO layer, even better if you log in and out time which gives you 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 database and your query is taking longer than expected than first thing you should check is whether you have index on columns which you are using for search (in where clause of query). this is most common error programmers make and believe me there is huge difference than querying a database which 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 first place. Another point which is worth noting is that too many indexes slows 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.

JDBC performance tips 3 : Use PreparedStatement
Use PreparedStatement or Stored Procedure for executing query Prepared Statements are much faster than normal Statement object as database can pre-compile them and also cache there query plan. so always use 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 performance benefit of preparestatment by using second form. see here for more advantages of PreparedStatement in Java like prevention from SQL Injection.

JDBC Performance Tips Java

Java database performance tips 4 : Use Database Connection Pool
Use Connection Pool for holding Database Connections. Creating Database connections are slow process in Java and take long time. So if you are creating connection on each request than obviously your response time will be lot more. Instead if you use Connection pools with adequate number of connections based upon your traffic or number of concurrent request to make to database you can minimize this time. Even with Connection pooling few of first request may take little longer to execute till your connection gets created and cached in pool.

JDBC performance tips 5 : Use JDBC Batch Update
using JDBC batch update can improve performance of Java database application 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 executeBatch() method to execute batch queries

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 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 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

These Java database application performance tips are very simple in nature and most of 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 expert may not get anything new but for beginners this is something worth remembering and applying. You can also use this Java performance tips as code review checklist of what not to do while writing Java applications which uses database in back-end.

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

Further Learning
Complete Java Masterclass
Java Fundamentals: The Java Language
Java In-Depth: Become a Complete Java Engineer!


Ara said...

A few comments:

Tip #4 (use connection pool): In a J2EE server, you should be using DataSource, not ConnectionPool directly. The server will take care of pooling.

Tip #3 (Use PreparedStatement): Parse time is usually insignificant compared to execution time unless the query is executed thousands of times (in which case you should be writing your query to return a cursor, aka RecordSet; see Tip #1). Using PreparedStatement is better for security and is not a bad practice generally. However, the trade-off is that the query plan generated by the database may not be optimal, because the database has to guess at the value of the bind parameters. Often, this does not matter, but when the WHERE clause includes columns that are not evenly distributed over the range of values, query times can be dramatically different between a query that uses bind parameters (PreparedStatement) and one that uses literals.

mestachs said...

my favorite tool :
wraps the datasource and enable it... in production.
sort by "total time"... and find the query you can avoid by caching, and locate the statement that needs an index.

for performance best practice check this entry on java ranch :

Javin @ sort array in java said...

@Ara, Thanks for your comment. I see point of J2EE Server manages connection pool. Thanks for pointing it out.

@mestachs , thanks for pointing to the tool it looks promising, going to try it.

@Nur , glad to hear that thanks for your comment.

Karlo Smid said...

Along with tip #2. Prerequisite is that database has to be filled with data! A lot of data! How much is a lot? Tables with millions of records.

Shailesh Mistry said...

if you have multiple queries to execute like, uploading batch data to database use prepared statement , and use addbatch() and clearParameter method() of preparestetment class to execute the queries in one shot.

Anonymous said...

Amazing blog. Thanks for sharing your knowledge with everyone. Everything is very useful

Post a Comment