Bind variables Performance Test in Java

By mkyong | Updated: August 17, 2009

Viewed: 838

Tags: java jdbc

I heard a lot of people talking about “Bind variables” will increase java application performance. Is this really true? i skeptical and make a simple performance test between Bind variables in PreparedStatement class and Non Bind variables in Statement class

How do i test it?

I will create a simple java class and keep sending a SQL query in bind variables method and non bind variables method to PostgreSQL database. The java class will display start and end of the execution result time.

What tools in use?

1) PostgreSQL Database
2) PostgreSQL JDBC Driver
3) Java JDBC PrepareStatement and Statement class

Here is the source code for Bind variables in PreparedStatement class

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

public class BindVariableJDBC {
  public static void main(String[] argv) {
  
      ResultSet rs = null;
      Connection conn = null;
      PreparedStatement pstatement = null;
      
	  try {
		  
	    conn = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/db_test","mkyong", "password");
	    Class.forName("org.postgresql.Driver");
	    
	    pstatement = conn.prepareStatement("SELECT * FROM s_user where userid = ?");
	    
            System.out.println("Start: " + new Date());
        
        for (int i = 1; i < 100000; i++) {
            
            pstatement.setInt(1, i);
            rs = pstatement.executeQuery();
         
        }
        
           System.out.println("End: " + new Date());
	  
	  } catch (Exception e) {
	   
	    e.printStackTrace();
	    return;
	  }
  }
}

Here is the source code for Non Bind variables in Statement class

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;

public class NonBindVariableJDBC {
  public static void main(String[] argv) {
  
      ResultSet rs = null;
      Connection conn = null;
      Statement statement = null;
      
	  try {
		  
	    conn = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/db_test","mkyong", "password");
	    Class.forName("org.postgresql.Driver");
	    
	    statement = conn.createStatement();
	    
            System.out.println("Start: " + new Date());
        
        for (int i = 1; i < 100000; i++) {

            rs = statement.executeQuery("SELECT * FROM s_user where userid = " + i);
         
        }
            System.out.println("End: " + new Date());
        
	  } catch (Exception e) {
	   
	    e.printStackTrace();
	    return;
	  }
  }
}

Performance Test Result

Loop 1000 time

Wow, the 1000 loop time testing , result is surprising me, it’s show that non-bind variable method is slight faster than bind variable method. Am i did something wrong? It’s ok… i just continue my testing on 10000 loop time.

Loop 10000 time

The 10000 loop time testing , result show that bind variable method is faster and 50% performance increased if compare with non bind variable method. Sound interesting , i just continue my testing on 100000 loop time.

Loop 100000 time

The 100000 loop time testing , result show that bind variable method is faster and 35% performance increased if compare with non bind variable method. One last testing on 1000000 loop time.

Loop 1000000 time

The 1000000 loop time testing , result show that bind variable method is faster and 37% performance increased if compare with non bind variable method.

Conclusion

Performance between bind variable and non bind variable is not so obviously in light database access application. However when the application involve heavy database access , it is always advice to use bind variable method to increase the java performance at least 30%.