一次一次的插入 十万条数据
总耗时316543毫秒
try {
Class.forName("org.postgresql.Driver");
connection = DriverManager.getConnection(url, user, passWord);
ps = connection.prepareStatement(insertSql);
long oddTime = System.currentTimeMillis();
System.out.println(oddTime);
for (int i = 1; i <= 100000; i++) {
ps.setInt(1, i);
ps.executeUpdate();
}
long newTime = System.currentTimeMillis();
System.out.println(newTime-oddTime);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (ps != null) {
ps.close();
}
if (connection != null) {
connection.close();
}
}
加上事务,当所有执行完后再提交
这次速度快一点 258293毫秒
try {
Class.forName("org.postgresql.Driver");
connection = DriverManager.getConnection(url, user, passWord);
ps = connection.prepareStatement(insertSql);
long oddTime = System.currentTimeMillis();
System.out.println(oddTime);
connection.setAutoCommit(false);
for (int i = 1; i <= 100000; i++) {
ps.setInt(1, i);
ps.executeUpdate();
}
long midTime = System.currentTimeMillis();
System.out.println("执行for时间:" + (midTime - oddTime));
connection.commit();
long newTime = System.currentTimeMillis();
System.out.println("插入的时间是:"+(newTime-midTime));
System.out.println(newTime - oddTime);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (ps != null) {
ps.close();
}
if (connection != null) {
connection.close();
}
}
事务+加上批量
十万条数据1100毫秒
try {
Class.forName("org.postgresql.Driver");
connection = DriverManager.getConnection(url, user, passWord);
ps = connection.prepareStatement(insertSql);
long oddTime = System.currentTimeMillis();
System.out.println(oddTime);
connection.setAutoCommit(false);
for (int i = 0; i < 100000; i++) {
ps.setInt(1, i);
ps.addBatch();
}
long midTime = System.currentTimeMillis();
System.out.println("执行for时间:" + (midTime - oddTime));
ps.executeBatch();
connection.commit();
long newTime = System.currentTimeMillis();
System.out.println("插入的时间是:" + (newTime - midTime));
System.out.println("总耗时:" + (newTime - oddTime));
} catch (Exception e) {
e.printStackTrace();
} finally {
if (ps != null) {
ps.close();
}
if (connection != null) {
connection.close();
}
}
分批次批量插入
try {
Class.forName("org.postgresql.Driver");
connection = DriverManager.getConnection(url, user, passWord);
ps = connection.prepareStatement(insertSql);
long oddTime = System.currentTimeMillis();
System.out.println(oddTime);
connection.setAutoCommit(false);
for (int i = 1; i <= 100000; i++) {
ps.setInt(1, i);
ps.addBatch();
if (i%500==0){
ps.executeBatch();
connection.commit();
System.out.println("插入"+i);
}
}
//如果最后无法取模尽插入,就将那部分提交
ps.executeBatch();
connection.commit();
long newTime = System.currentTimeMillis();
System.out.println("总耗时:" + (newTime - oddTime));
} catch (Exception e) {
e.printStackTrace();
} finally {
if (ps != null) {
ps.close();
}
if (connection != null) {
connection.close();