oracle入库的速度能到多少_Oracle入库速度测试(Java版)

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中有没有数组绑定法？

有待进一步考察……

分享到：

2008-08-26 19:08

浏览 2623

评论