大批量数据插入数据库时怎么才能提高速度或性能

[Yanbin_Q]

下面的测试程序

import java.sql.*;

//Table t1(c1 varchar(255),c2 varchar(255),c3 varchar(255),c4 varchar(255));

public class TestDB
{

public static void main(String[] args)
{
try
{
Class.forName("org.gjt.mm.mysql.Driver");
//Class.forName("com.mysql.jdbc.Driver");
}
catch (ClassNotFoundException ex)
{
ex.printStackTrace();
}
String url = "jdbc:mysql://127.0.0.1:3306/test";
try
{
Connection con = DriverManager.getConnection(url, "root", "");
Statement stmt = con.createStatement();

String v = "hellohellohellohellohellohellohellohellohellohellohellohello";
String sql = "insert into t1 values('" + v + "','" + v + "','" + v +
"','" + v + "')";

String sql1 = "insert into t1 values(?,?,?,?)";

PreparedStatement pstmt = con.prepareStatement(sql1);

int count = 100000;

stmt.execute("delete from t1");
long start = System.currentTimeMillis();

for (int i = 0; i < count; i++)
{
stmt.execute(sql);
}

System.out.println("用stmt execute 原始方式逐条添加记录耗时 ： " + (System.currentTimeMillis() - start));


stmt.execute("delete from t1");
start = System.currentTimeMillis();

for (int i = 0; i < count; i++)
{
stmt.addBatch(sql);
}
stmt.executeBatch();

System.out.println("用stmt addBatch executeBatch 方式批量添加记录耗时 ： " + (System.currentTimeMillis() - start));


stmt.execute("delete from t1");
start = System.currentTimeMillis();

for (int i = 0; i < count; i++)
{
pstmt.setString(1, v);
pstmt.setString(2, v);
pstmt.setString(3, v);
pstmt.setString(4, v);
pstmt.addBatch();
}
pstmt.executeBatch();

System.out.println("用pstmt addBatch executeBatch 方式批量添加记录耗时 ： " + (System.currentTimeMillis() - start));

}
catch (SQLException ex1)
{
ex1.printStackTrace();
}
}
}

运行结果是：

用第一个驱动

用stmt execute 原始方式逐条添加记录耗时 ： 20743
用stmt addBatch executeBatch 方式批量添加记录耗时 ： 20046
用pstmt addBatch executeBatch 方式批量添加记录耗时 ： 22091


用第二个驱动

用stmt execute 原始方式逐条添加记录耗时 ： 20807
用stmt addBatch executeBatch 方式批量添加记录耗时 ： 20854
用pstmt addBatch executeBatch 方式批量添加记录耗时 ： 22356


用Batch方式，性能基本没什么影响，反而一条一条的插入到数据库快一点

有没有能显示著提高处理速度的方法呢？

[flycqz]

用存储过程

[Yanbin_Q]

mysql4.0不支持存储过程，如果每条插入的语句内容不一样的，存储过程也不好写

[Arias]

用preparedstatement和sql中的事务处理方式可能快点！

[Yanbin_Q]

加入了事物处理速度肯定更要慢了

[bdsc]

一定要试：
con.setAutoCommit(false);
//massive op
con.commit();

原理--试过再说

[Yanbin_Q]

试过了，加入事物处理的处理结果如下：

用stmt execute 原始方式逐条添加记录耗时 ： 32127
用stmt addBatch executeBatch 方式批量添加记录耗时 ： 22084
用pstmt addBatch executeBatch 方式批量添加记录耗时 ： 22990

[网络咖啡]

用preparedstatement来执行预编译的SQL

[bdsc]

原理--试过再说.

根据jdbc规范：
The Connection attribute auto-commit specifies when to end transactions. Enabling
auto-commit causes the JDBC driver to do a transaction commit after each
individual SQL statement as soon as it is complete. The point at which a statement
is considered to be “complete” depends on the type of SQL statement as well as
what the application does after executing it:

1 For Insert, Update, Delete, and DDL statements, the statement is complete as soon
as it has finished executing.

2 For Select statements, the statement is complete when the associated result set is closed. The result set is closed as soon as one of the following occurs:
- all of the rows have been retrieved
- the associated Statement object is re-executed
- another Statement object is executed on the same connection

3 For CallableStatement objects, the statement is complete when all of the
associated result sets have been closed.

所以，按我的方法应该会快很多。mysql table是分类型的，用支持transact的类型也许明显。（我没有mysql,用的pg试过/快上百倍)

import java.sql.*;

/**
* @author Administrator
*
* 2004-6-11
*/
public class TestDB {
private static String driver = "org.postgresql.Driver";
private static String user = "sys";
private static String pwd = "sys";
private static String url = "jdbc:postgresql://127.0.0.1/test";

public static void main(String[] args) {
Connection con = null;
try {
Class.forName(driver);
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
}

try {
con = DriverManager.getConnection(url, user, pwd);
con.setAutoCommit(false);
Statement stmt = con.createStatement();

String v =
"hellohellohellohellohellohellohellohellohellohellohellohello";
String sql =
"insert into t1 values('"
+ v
+ "','"
+ v
+ "','"
+ v
+ "','"
+ v
+ "')";

String sql1 = "insert into t1 values(?,?,?,?)";
PreparedStatement pstmt = con.prepareStatement(sql1);

int count = 10000;
stmt.execute("delete from t1");
long start = System.currentTimeMillis();
con.commit();
//method 1

for (int i = 0; i < count; i++)
stmt.execute(sql);

System.out.println(
"用stmt execute 原始方式逐条添加记录耗时 ： "
+ (System.currentTimeMillis() - start));
con.commit();
stmt.execute("delete from t1");
start = System.currentTimeMillis();

//method2
for (int i = 0; i < count; i++)
stmt.addBatch(sql);
stmt.executeBatch();
con.commit();
System.out.println(
"用stmt addBatch executeBatch 方式批量添加记录耗时 ： "
+ (System.currentTimeMillis() - start));

stmt.execute("delete from t1");
start = System.currentTimeMillis();

//method 3
for (int i = 0; i < count; i++) {
pstmt.setString(1, v);
pstmt.setString(2, v);
pstmt.setString(3, v);
pstmt.setString(4, v);
pstmt.addBatch();
}
pstmt.executeBatch();
con.commit();
System.out.println(
"用pstmt addBatch executeBatch 方式批量添加记录耗时 ： "
+ (System.currentTimeMillis() - start));

} catch (SQLException ex1) {
ex1.printStackTrace();
}
finally {
try {
con.close();
}catch(SQLException ex2) {}
}
}
}

[Yanbin_Q]

试过，好像也没用

[xue_sharp]

估计是MYSQL的问题，我用ORACLE，BATCH比单条的快n倍

[webrobot]

楼上说得对。
我看过介绍。mysql好像不支持。你用oracle或者sql2000都可以batch绝对快。

[usabcd]

用 Batch方式一次不能太多，每格batch30～50条比较合适。楼主可以试一下。

[lEFTmOON]

关注一下

[hl_longman]

mark

[fxywkj]

mark!

[niyboy]

不用statement来执行sql 语句

用preparedstatement来执行，

[xue_sharp]

楼上的，我的程序BATCH 一次20万条数据，40秒左右

[scvzhang]

估计是MYSQL的问题
最好去看看MYSQL关于BATCH ,PREPAREDSTATEMENT,是否能提高性能。

[tjl713]

谢谢了，这个问题给了我补充！

[majy]

表建primary key了没有