Performance using Bind Variables in Java Application

Posted by Steve Racanovic | Posted in , | Posted on 1:03 PM

1

The advantage of using bind variables within your Java application, it that provides greater application performance and uses less of the shared pool from the database.

This is illustrated in the following 2 examples.

Example 1: Using Bind variables.


package bindvariables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import bindvariables.JDBCUtil;

public class Bind {

public static void main(String[] args) throws SQLException {

ResultSet rs = null;
Connection conn = null;
PreparedStatement pstmt = null;

try {
// Get Connection
conn = JDBCUtil.getConnection();
// Create statement
pstmt = conn.prepareStatement("SELECT ? FROM dual");
System.out.println("Start: " + new Date());
for (int i = 1; i < 100000; i++) {
// Use bind variable for subsitution
pstmt.setString(1, String.valueOf(i));
// Execute the query
rs = pstmt.executeQuery();
//if (i % 1000 == 1)
// System.out.println("Print: " + i);
}
System.out.println("End: " + new Date());

} catch (SQLException se) {
System.out.println("SQL Exception:");
se.printStackTrace();
}
finally {
// Close the result set, statement and the connection
JDBCUtil.close(rs,pstmt,conn);
}
}
}


Running this application returns the following:

Start: Mon Mar 31 11:36:04 EST 2008
End: Mon Mar 31 11:36:51 EST 2008

It shows the application being executed within less than a minute.

Now the second example does not use bind variables and force the use of Hard Parse on the database.

Example 2: Not using bind variables.


package bindvariables;

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

public class NonBind {

public static void main(String[] args) throws SQLException {

ResultSet rs = null;
Connection conn = null;
Statement stmt = null;

try {
// Get Connection
stmt = (JDBCUtil.getConnection()).createStatement();
System.out.println("Start: " + new Date());
for (int i = 0; i < 100000; i++) {
// Execute Statement
rs = stmt.executeQuery("SELECT " + i + " FROM dual");
//if (i % 1000 == 1)
// System.out.println("Print: " + i);
}
System.out.println("End: " + new Date());
} catch (SQLException se) {
System.out.println("SQL Exception:");
se.printStackTrace();
}
finally {
// Close the result set, pstatement and the connection
JDBCUtil.close(rs,stmt,conn);
}
}
}


Running this application returns the following:

Start: Mon Mar 31 11:31:45 EST 2008
End: Mon Mar 31 11:35:34 EST 2008

It shows the application being executed over 4 minutes.

As you can see, ensure you use bind variables for performance when coding your application.

My JDBCUtil class method look like:

...

public static void close(ResultSet resultSet, Statement statement,
Connection connection) {
try {
if (resultSet != null)
close(resultSet);
if (statement != null)
close(statement);
if (connection != null)
close(connection);
} catch (Exception e) {
e.printStackTrace();
}
}

public static void close(ResultSet resultSet, PreparedStatement pstatement,
Connection connection) {
try {
if (resultSet != null)
close(resultSet);
if (pstatement != null)
close(pstatement);
if (connection != null)
close(connection);
} catch (Exception e) {
e.printStackTrace();
}
}

public static Connection getConnection() throws SQLException {
String username = "scott";
String password = "tiger";
String thinConn =
"jdbc:oracle:thin:@sracanov-au2.au.oracle.com:1522:orcl";
DriverManager.registerDriver(new OracleDriver());
Connection conn =
DriverManager.getConnection(thinConn, username, password);
conn.setAutoCommit(false);
return conn;
}
}


...

Comments (1)

Good examples!