Oracle 和 JDBC 性能：INSERT ALL 与 preparedStatement.addBatch
我有一个带有 Oracle 数据库后端的 java 应用程序，我需要在其中插入多行。我看过关于 将多行插入 Oracle 的讨论，但我也对混合使用 JDBC 时性能受到的影响感兴趣。
我看到了几种可能性：
选项 1： 使用单行插入PreparedStatement并执行多次：
String insert = "Insert into foo(bar, baz) values (?, ?)";
PreparedStatement stmt = conn.prepareStatement(insert);
for(MyObject obj : someList) {
stmt.setString(1, obj.getBar());
stmt.setString(2, obj.getBaz());
stmt.execute();
}
选项2： 构建 Oracle INSERT ALL 语句：
String insert = "INSERT ALL " +
"INTO foo(bar, baz), (?, ?) " +
"INTO foo(bar, baz), (?, ?) " +
"SELECT * FROM DUAL";
PreparedStatement stmt = conn.prepareStatement(insert);
int i=1;
for(MyObject obj : someList) {
stmt.setString(i++, obj.getBar());
stmt.setString(i++, obj.getBaz());
}
stmt.execute();
选项 3： 使用PreparedStatement的addBatch功能：
String insert = "Insert into foo(bar, baz) values (?, ?)";
PreparedStatement stmt = conn.prepareStatement(insert);
for(MyObject obj : someList) {
stmt.setString(1, obj.getBar());
stmt.setString(2, obj.getBaz());
stmt.addBatch();
}
stmt.execute();
我想另一种可能性是创建一个CSV文件并使用SQL Loader，但我不确定如果添加创建CSV文件的开销是否真的会更快......
所以哪个选项执行速度最快？
I have a java app with an Oracle database backend that I need to insert multiple rows into. I've seen the discussion about inserting multiple rows into Oracle, but I'm also interested in how the performance is affected when JDBC in thrown in the mix.
I see a few possibilities:
Option 1:
Use a singe-row insert PreparedStatement and execute it multiple times:
String insert = "Insert into foo(bar, baz) values (?, ?)";
PreparedStatement stmt = conn.prepareStatement(insert);
for(MyObject obj : someList) {
stmt.setString(1, obj.getBar());
stmt.setString(2, obj.getBaz());
stmt.execute();
}
Option 2:
Build an Oracle INSERT ALL statement:
String insert = "INSERT ALL " +
"INTO foo(bar, baz), (?, ?) " +
"INTO foo(bar, baz), (?, ?) " +
"SELECT * FROM DUAL";
PreparedStatement stmt = conn.prepareStatement(insert);
int i=1;
for(MyObject obj : someList) {
stmt.setString(i++, obj.getBar());
stmt.setString(i++, obj.getBaz());
}
stmt.execute();
Option 3:
Use the addBatch functionality of PreparedStatement:
String insert = "Insert into foo(bar, baz) values (?, ?)";
PreparedStatement stmt = conn.prepareStatement(insert);
for(MyObject obj : someList) {
stmt.setString(1, obj.getBar());
stmt.setString(2, obj.getBaz());
stmt.addBatch();
}
stmt.execute();
I guess another possibility would be to create a CSV file and use the SQL Loader, but I'm not sure that would really be faster if you add in the overhead of creating the CSV file...
So which option would perform the fastest?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
对于 1,000,000 行以下的任何内容，请使用
PreparedStatement的addBatch()功能。添加到代码中的每个附加组件都会增加依赖性和故障点。
如果您沿着这条路线走下去（外部表、sql 加载器等），请确保它确实值得。
将数据序列化到 csv 文件，将其移动到数据库可读的位置将很容易花费一秒钟左右的时间。
在那段时间里，如果我忍住并开始使用 JDBC 插入，我可以插入 20,000 行。
Use the
addBatch()functionality ofPreparedStatementfor anything below 1,000,000 rows.Each additional component you add to your code increases the dependencies and points of failure.
If you go down that route (external tables, sql loader etc) make sure it is really worth it.
Serializing data to a csv file, moving it into a location readable by database will easily take a second or so.
During that time, I could have inserted 20,000 rows if I just sucked it up and started inserting with JDBC.
即使没有直接路径加载，SQL Loader 似乎也是更好的方法，但它很难维护。
批量插入比单个插入语句快2-4倍。
全部插入就像批量插入一样，这两者都会比 PL/SQL 实现更快。
您还可能想阅读 此 AskTom 主题。
SQL Loader seems to be better way even without direct path loading, but it's hard to maintain.
Batch insert 2-4 times faster than single insert statements.
Insert all just like batch insert, and both of this would be faster then PL/SQL implementation.
Also you may want to read this AskTom topic.
使用批处理对于程序员来说是透明的。以下是此处的引用：
Using batch can be transparent to a programmer. Here is a cite from here: