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.

Options: ReplyQuote


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.