Oracle入库速度测试(Java版)

2xqz4g40en

测试环境：
    Intel Xeon 2.4G四核心 2.5G内存
    Server 2003 Enterprise Edition Service Pack 2
    Oracle9i Enterprise Edition 9.2.0.1.0
    jdk1.5.0_12
    ojdbc14.jar
建立测试表：
CREATE TABLE TEST
(
TEST_ID NUMBER(10, 0),
TEST_NAME VARCHAR2(50),
TEST_TIME TIMESTAMP,
TEST_VALUE NUMBER(10, 3)
);
连接Oracle过程略。
一个测试用的类：
public class Util {
public static Random rand = new Random();
public static String atoz = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
public static String genString(int length)
{
StringBuilder re = new StringBuilder(length);
for (int i = 0; i < length; i++)
{
re.append(atoz.charAt(rand.nextInt(52)));
}
return re.toString();
}
public static double genDouble()
{
double d1 = 2500 * rand.nextDouble();
double d2 = 500000 * rand.nextDouble();
return d1 + d2;
}
}

拼sql入库：
public static void simpleInsert(int total) throws Exception {
Thread.sleep(3000);
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Timestamp current = new Timestamp(System.currentTimeMillis());
String currentStr = dateFormat.format(current);
Connection conn = DriverManager.getConnection(dbURL, user, password);
try {
long begin = System.currentTimeMillis();
Statement s = conn.createStatement();
for (int i = 1; i <= total; i++) {
String sql = String.format("INSERT INTO TEST (TEST_ID, TEST_NAME, TEST_TIME, TEST_VALUE) VALUES (%s, '%s', to_date('%s','yyyy-MM-dd HH24:mi:ss'), %s)", i, Util.genString(5), currentStr, Util.genDouble());
s.executeUpdate(sql);
}
long end = System.currentTimeMillis();
System.out.printf("Count:%d Time:%d\n", total, (end - begin));
} catch (Exception ex) {
ex.printStackTrace();
} finally {
conn.close();
}
}
测试结果：
Count:10240 Time:25203
Count:10240 Time:23891
Count:10240 Time:24516
Count:10240 Time:24891
Count:10240 Time:25063
Count:10240 Time:24359
Count:10240 Time:23547
Count:10240 Time:24312
Count:10240 Time:23938
Count:10240 Time:24687
总结：
平均入库速度每1万条23.87秒
绑定参数法入库：
public static void traditionalInsert(int total) throws Exception {
Thread.sleep(3000);
Timestamp current = new Timestamp(System.currentTimeMillis());
Connection conn = DriverManager.getConnection(dbURL, user, password);
try {
long begin = System.currentTimeMillis();
conn.setAutoCommit(false);
PreparedStatement ps = conn.prepareStatement("INSERT INTO TEST (TEST_ID,TEST_NAME,TEST_TIME,TEST_VALUE) VALUES (?, ?, ?, ?)");
for (int i = 1; i <= total; i++) {
ps.setInt(1, i);
ps.setString(2, Util.genString(5));
ps.setTimestamp(3, current);
ps.setBigDecimal(4, new BigDecimal(Util.genDouble()));
ps.addBatch();
if ((i % 500) == 0) {
ps.executeBatch();
}
}
ps.executeBatch();
conn.commit();
long end = System.currentTimeMillis();
System.out.printf("Count:%d Time:%d\n", total, (end - begin));
} catch (Exception ex) {
ex.printStackTrace();
conn.rollback();
} finally {
conn.close();
}
}
测试结果：
Count:512000 Time:33000
Count:512000 Time:31344
Count:512000 Time:31407
Count:512000 Time:31281
Count:512000 Time:31891
Count:512000 Time:31219
Count:512000 Time:31844
Count:512000 Time:32125
Count:512000 Time:32047
Count:512000 Time:33141
总结：
平均入库速度每100万条62.36秒
绑定参数法虽然比较快，但是还有没有挖掘的空间？
每500条执行一次executeBatch是不是最合适的？
Java中有没有数组绑定法？
有待进一步考察……