Oracle入库速度测试(Java版)

测试环境：
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();
	}
}

测试结果：
[i]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[/i]
总结：
平均入库速度每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();
	}
}

测试结果：
[i]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[/i]
总结：
平均入库速度每100万条62.36秒

绑定参数法虽然比较快，但是还有没有挖掘的空间？
每500条执行一次executeBatch是不是最合适的？
Java中有没有数组绑定法？
有待进一步考察……