Seems statement caching does make a difference, about 30% difference. I was always under the assumption that it didn't and finally tested it out for myself under Oracle 9.2. Here's the test:
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.HashMap;
/**
* Created by IntelliJ IDEA.
* User: wburke
* Date: Sep 16, 2003
* Time: 9:07:52 PM
* To change this template use Options | File Templates.
*/
public class pscache
{
/*
CREATE TABLE C_customer
(
c_id integer not null,
c_first char(16),
c_last char(16),
c_street1 char(20),
c_city char(20),
c_state char(2),
c_phone char(16)
);
*/public static final String insertQuery = "insert into C_customer values (?, ?, ?, ?, ?, ?, ?)"; public static final String findQuery = "select c_first, c_last, c_street1, c_city, c_state, c_phone from C_customer where c_id = ?"; public static final String deleteQuery = "delete from C_customer where c_id = ?";
public static PreparedStatement createInsert(Connection con) throws
Exception
{
return con.prepareStatement("insert into C_customer values (?, ?,
?, ?, ?, ?, ?)");
} public static PreparedStatement createFindBy(Connection con) throws
Exception
{
return con.prepareStatement("select c_first, c_last, c_street1,
c_city, c_state, c_phone from C_customer where c_id = ?");
} public static PreparedStatement createDelete(Connection con) throws
Exception
{
return con.prepareStatement("delete from C_customer where c_id = ?");
} public static void testNonCachedPS(Connection con, int i) throws
Exception
{
PreparedStatement insert = createInsert(con);
PreparedStatement find = createFindBy(con);
PreparedStatement delete = createDelete(con); int idx = 0;
insert.setInt(++idx, i);
insert.setString(++idx, "Bill");
insert.setString(++idx, "Burke");
insert.setString(++idx, "10 Blanchard Ave");
insert.setString(++idx, "Billerica");
insert.setString(++idx, "MA");
insert.setString(++idx, "666-666-6666");
insert.executeUpdate();
insert.close(); find.setInt(1, i);
ResultSet rs = find.executeQuery();
rs.next();
String first = rs.getString("c_first");
String last = rs.getString("c_last");
String street = rs.getString("c_street1");
String city = rs.getString("c_city");
String state = rs.getString("c_state");
String phone = rs.getString("c_phone");
rs.close();
find.close(); delete.setInt(1, i);
delete.executeUpdate();
delete.close();
}public static HashMap psCache = new HashMap();
public static void testCachedPS(Connection con, int i) throws Exception
{
PreparedStatement insert =
(PreparedStatement)psCache.get(insertQuery);
PreparedStatement find = (PreparedStatement)psCache.get(findQuery);
PreparedStatement delete =
(PreparedStatement)psCache.get(deleteQuery); int idx = 0;
insert.setInt(++idx, i);
insert.setString(++idx, "Bill");
insert.setString(++idx, "Burke");
insert.setString(++idx, "10 Blanchard Ave");
insert.setString(++idx, "Billerica");
insert.setString(++idx, "MA");
insert.setString(++idx, "666-666-6666");
insert.executeUpdate(); find.setInt(1, i);
ResultSet rs = find.executeQuery();
rs.next();
String first = rs.getString("c_first");
String last = rs.getString("c_last");
String street = rs.getString("c_street1");
String city = rs.getString("c_city");
String state = rs.getString("c_state");
String phone = rs.getString("c_phone");
rs.close(); delete.setInt(1, i);
delete.executeUpdate();
} public static void main(String[] args) throws Exception
{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ziondb",
"ecperf", "ecperf");long start, end;
start = System.currentTimeMillis();
for (int i = 0; i < 3000; ++i)
{
testNonCachedPS(con, i);
}
end = System.currentTimeMillis() - start;
System.out.println("non cached took: " + end);
con.close(); con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ziondb",
"ecperf", "ecperf");
PreparedStatement insert = createInsert(con);
PreparedStatement find = createFindBy(con);
PreparedStatement delete = createDelete(con);
psCache.put(insertQuery, insert);
psCache.put(findQuery, find);
psCache.put(deleteQuery, delete);
start = System.currentTimeMillis();
for (int i = 0; i < 3000; ++i)
{
testCachedPS(con, i);
}
end = System.currentTimeMillis() - start;
System.out.println("cached took: " + end);
insert.close();
find.close();
delete.close();
con.close();} }
-- ================ Bill Burke Chief Architect JBoss Group LLC. ================
------------------------------------------------------- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf _______________________________________________ JBoss-Development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
