Bind variables Performance Test in Java

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

Bind variable method Non bind variable method
Start: Fri Jan 09 14:09:41 SGT 2009
End: Fri Jan 09 14:09:42 SGT 2009
Start: Fri Jan 09 14:15:08 SGT 2009
End: Fri Jan 09 14:15:08 SGT 2009
1 Second 0 Second

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

Bind variable method Non bind variable method
Start: Fri Jan 09 14:18:31 SGT 2009
End: Fri Jan 09 14:18:35 SGT 2009
Start: Fri Jan 09 14:19:53 SGT 2009
End: Fri Jan 09 14:19:59 SGT 2009
4 Seconds 6 Seconds

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

Bind variable method Non bind variable method
Start: Fri Jan 09 14:22:40 SGT 2009
End: Fri Jan 09 14:23:17 SGT 2009
Start: Fri Jan 09 14:23:49 SGT 2009
End: Fri Jan 09 14:24:46 SGT 2009
37 Seconds 57 Seconds

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

Bind variable method Non bind variable method
Start: Fri Jan 09 14:30:51 SGT 2009
End: Fri Jan 09 14:37:08 SGT 2009
Start: Fri Jan 09 14:41:59 SGT 2009
End: Fri Jan 09 14:51:58 SGT 2009
6 minutes 17 Seconds 9 minutes 59 Seconds

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

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
6 Comment threads
2 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
7 Comment authors
Tim TaylorsimonMoeinmjbGerardo Valdez Recent comment authors
newest oldest most voted
Tim Taylor
Guest
Tim Taylor

ANother issue to be aware of. The prepared statement that uses the bind variables are compiled on the database and cached. In the case of non-bind variables a new query is cached by the database potentially for every value of the variable can take. If a bind varaible is used the query is only cached once, with the place holder. I have seen a production database effectively run out of query cache space because of a number of queries written using string concatination rather than bind variables. With a large number of variables (say 5-10) the number of different permitations… Read more »

simon
Guest
simon

if it were oracle, bind var sqls will be compiled and compiled result will be saved for later use. When sql is very complicated the performance difference is significant because compiling time is much.
for your sql. it’s very simple, so compile time( if there would be a compile process for postgreSQL, and it’s handled similar to oracle ) would be very short. So the difference is not significant.
for a better persuasion, I suggest try different and complicated SQLs to test.

Moein
Guest
Moein

hi

I have a question about variable binding in java.would you please help me

I have a simple bi-directional variable binding to a jtextfield (Example)
when ever I change the swing jTextField.text property via GUI , the bounded variable (String boundedString) is changed online.

BUT

but if I change the variable value , no changes in swing component happens !!

I’ve bind them in read-write mode.

I don’t want to call bindinggroup.bind() method on each variable changes

any ideas please?

mjb
Guest
mjb

The even more compelling point is avoiding sql injection. Don’t quote things yourself (that’s easy to get wrong, and not portable) – let the jdbc driver do it.

Gerardo Valdez
Guest
Gerardo Valdez

Well, what about your SGA?
Please make the same example with 100 diferents sql instruction and then tell me.
Your example really is not a good about “BIND VARIABLES PERFORMANCE TEST”

Roger Padilla
Guest
Roger Padilla

That is about Statement and PrepareStatement, not specifically about bind or not-bind variables. PrepareStatement is a pre-compiled sql statement, that is the reason why Statement is faster in only one execution. In genereal, PrepareStatement is better (secure, legible and faster).