Skip to main content

java.sql.Statement and java.sql.PreparedStatement performance test

6 replies [Last post]
manoaman
Offline
Joined: 2006-11-04
Points: 0

Hi,

I am trying to do the benchmark test to compare performance efficiency between java.sql.Statement and java.sql.PreparedStatement. I have 10,000 records in a MySQL table and executing 1,000 SELECT queries on series of keys for 10 times, however, the result of my test does not look correct. Especially, I don't understand the time suddenly decreases for Statement testing.(Using cache???) I was assuming PreparedStatement use to be about 3 times faster than using Statement class. Could anyone indicate an inappropriate part on my code or show me the better way to do the performance test? I know I should try doing over 1,000 queries.

Thank you,

[Environment]
JDK:1.4.2_12
MySQL:4.1.15-log

[Database]
+--------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+-------------------+----------------+
| id | bigint(20) | | PRI | NULL | auto_increment |
| value | varchar(30) | | | | |
| value2 | int(11) | YES | | NULL | |
| time | timestamp | YES | | CURRENT_TIMESTAMP | |
+--------+-------------+------+-----+-------------------+----------------+

[Sample Code]

(DatabasePerformance)

package test;

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

public class DatabasePerformance {

/**
* Select (Statement)
* @param id A key to select a record.
*/
public int select(int id) {
// Get connection and create statement.
Connection con = getConnection();
Statement stmt = null;
ResultSet rs = null;
int value = 0;
try {
stmt = con.createStatement();
String sql = "SELECT id FROM table WHERE value2="+id;
rs = stmt.executeQuery(sql);
if(rs.next()) value = rs.getInt(1);
} catch (Exception e) {
e.printStackTrace();
} finally {
// Close the connection.
try {
if(rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return value;
}

/**
* Select (PreparedStatement)
* @param id A key to select a record.
*/
public int pselect(int id) {
// Get connection and create statement.
Connection con = getConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;
int value = 0;
try {
String sql = "SELECT id FROM table WHERE value2=?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1,id);
rs = pstmt.executeQuery();
if(rs.next()) value = rs.getInt(1);
} catch (Exception e) {
e.printStackTrace();
} finally {
// Close the connection.
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return value;
}

/**
* Obtains and returns a valid connection to the database.
*
* @return A valid connection to the database.
*
* @throws ServerException
* If a connection could not be obtained.
*/
private synchronized Connection getConnection() {

Connection connection = null;

try {
Class.forName("org.gjt.mm.mysql.Driver"); // MySQL use.
// connection = DriverManager.getConnection("","",""); // Set properly on test.
} catch (Exception e) {
e.printStackTrace();
}

return connection;
}

}

---------------------------------------------------------------------------------

(DatabasePerformanceTest)

package test;

import junit.framework.TestCase;

/**
* Test cases for DatabasePerformance class.
* Performance test on Statement and PreparedStatement.
*/
public class DatabasePerformanceTest extends TestCase {

/** Number of SQL queries. */
private long numberOfSQL = 1000;

/** Number of test to be conducted. */
private long numberOfTest = 10;

/** DatabasePerformance class */
DatabasePerformance dp;

/* (non-Javadoc)
* @see junit.framework.TestCase#setUp()
*/
protected void setUp() throws Exception {
dp = new DatabasePerformance();
super.setUp();
}

/**
* Select query to the database using Statement class.
*
* SQL: SELECT * FROM table WHERE value2=?
*/
public void testSelect()
{
long startTime = 0;
long endTime = 0;
long totalTime = 0;
float averageTime = 0.0F;
int id = 0;
print("=========================Start testSelect()=========================");
for(int j=0;j

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
jerp
Offline
Joined: 2003-09-07
Points: 0

Hi manoaman!

I've seen a view problems in your code:
1. As already quoted, you should prepare a statement only once.
For the first call a prepared statement usualy takes longer time, than a normal statement, but from the second call on it's by far faster (also on mysql)
2. Don't open a connection every time but use always the same or use a connection pool if you want to perform simultanious queries.
This has 2 reasons: a) A prepared (JDBC 2?) statement is only valid for one connection (maybe this has changed in newer JDBC-versions). So you have to prepare it for each connection. b) The main reason is, that opening a connection is usually the most time consuming part of a query.

sdo
Offline
Joined: 2005-05-23
Points: 0

It's true that in a standalone program like this, the better thing is to prepare and reuse the statement; something like:

PreparedStatement ps = con.prepareStatement(...);
for (some loop) {
ps.set*(); // for each of the bind variables
ps.executeQuery();
ps.clearParameters();
}

However, that's not always the most convenient way to program an application, and many JDBC drivers (the good ones :-)) do "implicit" statement caching -- they keep a statement cache in the connection. When you close the prepared statement, the driver puts the statement back in the cache (and automatically clears the parameters); when you call prepareStatement, the statement is retrieved from the cache.

The Connector/J 3.1.13 MySQL JDBC drivers can do this. You have to set the following properties: cachePreparedStatements=true;prepStmtCacheSize=512;useServerPreparedStmts=false;useLocalSessionState=true

The statement cache size can be varied for your application; it's the size of the implicit cache for the connection.

spdenne
Offline
Joined: 2006-02-15
Points: 0

Prepared statements should be prepared once, and used many times with different parameter values. Not prepared many times, and only used once each.

manoaman
Offline
Joined: 2006-11-04
Points: 0

Hi spdenne,

Actually, that's what I came to me and I tried the follwing test to see if I see any changes. It seems I'm still getting the same kind of results as the previous test. Any thoughts? I beginning to be suspicious on that my.cnf has been customized for MySQL. Could this be a cause?

Thank you,

[Source code]

/**
* Simple select preformance test.
*/
public void select() {
// Get connection and create statement.
Connection con = getConnection();
Statement stmt = null;
PreparedStatement pstmt = null;
int numberOfSQL = 1000;
int numberOfTests = 10;
long startTime = 0;
long endTime = 0;
long totalTime = 0;
float averageTime = 0.0F;
try {
// Statement Test.
System.out.println("Test Statement...");
for(int j=0;j startTime = System.currentTimeMillis();
for(int i=0;i String sql1 = "SELECT id FROM table WHERE value2="+i;
stmt = con.createStatement();
stmt.executeQuery(sql1);
}
endTime = System.currentTimeMillis();
System.out.println("Time: " + (endTime-startTime) + " miliseconds");
totalTime += (endTime-startTime);
}
averageTime = (float)(totalTime/numberOfTests);
System.out.println("Total Time: " + totalTime + " miliseconds");
System.out.println("Average Time: " + averageTime + " miliseconds");

// PreparedStatement Test.
totalTime = 0;
System.out.println("Test PreparedStatement...");
for(int j=0;j startTime = System.currentTimeMillis();
String sql2 = "SELECT id FROM table WHERE value2=?";
pstmt = con.prepareStatement(sql2);
for(int i=0;i pstmt.setInt(1,i);
pstmt.executeQuery();
}
endTime = System.currentTimeMillis();
System.out.println("Time: " + (endTime-startTime) + " miliseconds");
totalTime += (endTime-startTime);
}
averageTime = (float)(totalTime/numberOfTests);
System.out.println("Total Time: " + totalTime + " miliseconds");
System.out.println("Average Time: " + averageTime + " miliseconds");

} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(stmt!=null) stmt.close();
if(pstmt!=null) pstmt.close();
if(con!=null) con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

[Test results]

Test Statement...
Time: 5596 miliseconds
Time: 230 miliseconds
Time: 144 miliseconds
Time: 100 miliseconds
Time: 139 miliseconds
Time: 100 miliseconds
Time: 95 miliseconds
Time: 161 miliseconds
Time: 92 miliseconds
Time: 98 miliseconds
Total Time: 6755 miliseconds
Average Time: 675.0 miliseconds

Test PreparedStatement...
Time: 5398 miliseconds
Time: 5220 miliseconds
Time: 5186 miliseconds
Time: 5201 miliseconds
Time: 5175 miliseconds
Time: 5201 miliseconds
Time: 5189 miliseconds
Time: 5171 miliseconds
Time: 5190 miliseconds
Time: 5181 miliseconds
Total Time: 52112 miliseconds
Average Time: 5211.0 miliseconds

spdenne
Offline
Joined: 2006-02-15
Points: 0

I don't know.

I don't use MySQL, but based on my knowledge of PostgreSQL...
one thing that it might be doing is making use of the known value parameter to chose a query plan that is better than the choice it has to make when it doesn't know the parameter's value.

olsonje
Offline
Joined: 2005-08-10
Points: 0

From a quick 3 minute googling, it looks like mysql has a bunch of issues with prepared statements including something to do with caching of them.

I don't know if it would directly affect your problem, but hey, it could so here are two links that seem fairly interesting and might be worth seeing if the suggestion changes your results.

http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/

Note: No statement caching? BLARGH!

http://ubuntuforums.org/showthread.php?t=88253

Note: Try the flag, see how it works.