PreparedStatement performance issues
Posted by:
Ledion B
Date: May 06, 2005 08:07PM
Hi all,
I've been working on trying to get the PreparedStatement's performance out of mysql, but with not much luck.
My testing environment
A table with 100,000 rows (primary key (id)) id -> 1 - 100,000
Issue 10,000 SELECTS for a random set of ids
Measure the time it takes to complete
Compare the time it takes for using PreparedStatement vs simple Statement. I am NOT seeing any performance improvement whatsoever.
Server version: Mysql 4.1.10-standard
Connector/J: mysql-connector-java-3.1.7.bin.jar
OS: Linux
Java: 1.4.1
Connection String
"jdbc:mysql:///test?user=XXXX&password=XXX" +
"&emulateUnsupportedPstmts=false" +
"&useServerPrepStmts=true" +
"&cachePrepStmts=true";
The PreparedStatement code
testPrepared() {
String query = "SELECT p.field1 FROM perf_test2 as p WHERE p.field1 = ?";
PreparedStatement stmt = con.prepareStatement(query);
ResultSet rs;
long start = System.currentTimeMillis(), stop;
int key;
for(int i=0; i<10000; i++)
{
key = (int)(Math.random()*100000);
stmt.setInt(1, key);
rs = stmt.executeQuery();
}
stop = System.currentTimeMillis();
System.out.print((stop - start));
stmt.close();
}
The regular statement code
test(){
String query = "SELECT p.field1 FROM perf_test2 as p WHERE p.field1 = ";
ResultSet rs;
long start = System.currentTimeMillis(), stop;
int key;
for(int i=0; i<10000; i++)
{
key = (int)(Math.random()*100000);
rs = stmt.executeQuery(query + key);
}
stop = System.currentTimeMillis();
System.out.print((stop - start));
stmt.close();
}
Anyone see any mistakes on the above code ???? Can anyone post some code that does show an improvement.
Subject
Written By
Posted
PreparedStatement performance issues
May 06, 2005 08:07PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.