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%.
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 of the query can potentially get quite large.
I would recommend always using bind variables, never concatinate the query string when possible
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.
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?
this article is about the database variable binding, not really Swing variable binding.
Suggest you post your question in detail in the following forum
http://www.coderanch.com/forums
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.
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”
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).
When i change PrepareStatement from bind variable format to non-bind variable format, performance drop around 35%. So bind variable does affect performance a lot.
Ya , may be i should use PrepareStatement to compare both of it. Thanks for tips.