Oracle 和 JDBC 性能：INSERT ALL 与 PreparedStatement.addBatch
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4901997/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle and JDBC performance: INSERT ALL vs preparedStatement.addBatch
提问by Jeremy
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.
我有一个带有 Oracle 数据库后端的 Java 应用程序，我需要在其中插入多行。我看过有关将多行插入 Oracle的讨论，但我也对混合使用 JDBC 时性能如何影响感兴趣。
I see a few possibilities:
我看到了几种可能性：
Option 1: Use a singe-row insert PreparedStatement and execute it multiple times:
选项 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();
}
Option 2: Build an Oracle INSERT ALL statement:
选项 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();
Option 3: Use the addBatch functionality of PreparedStatement:
选项 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();
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...
我想另一种可能性是创建一个 CSV 文件并使用 SQL 加载器，但我不确定如果添加创建 CSV 文件的开销，那真的会更快......
So which option would perform the fastest?
那么哪个选项的执行速度最快？
采纳答案by Ronnis
Use the addBatch()functionality of PreparedStatementfor anything below 1,000,000 rows.
对低于1,000,000 行的任何内容使用 的addBatch()功能。PreparedStatement
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.
如果您沿着那条路线（外部表、sql 加载程序等）走下去，请确保它真的值得。
Serializing data to a csv file, moving it into a location readable by database will easily take a second or so.
将数据序列化为 csv 文件，将其移动到数据库可读的位置将很容易花费一秒钟左右的时间。
During that time, I could have inserted 20,000 rows if I just sucked it up and started inserting with JDBC.
在那段时间里，如果我把它吸进去并开始用 JDBC 插入，我可以插入 20,000 行。
回答by Lev Khomich
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.
即使没有直接路径加载，SQL Loader 似乎也是更好的方法，但它很难维护。批量插入比单个插入语句快 2-4 倍。插入所有就像批量插入一样，这两者都会比 PL/SQL 实现更快。
Also you may want to read thisAskTom topic.
您也可能想阅读此AskTom 主题。
回答by Sebastian ?askawiec
Using batch can be transparent to a programmer. Here is a cite from here:
使用批处理对程序员来说是透明的。这是从引用在这里：
Setting the Connection Batch Value
You can specify a default batch value for any Oracle prepared statement in your Oracle connection. > To do this, use the setDefaultExecuteBatch() method of the OracleConnection object. For example, the following code sets the default batch value to 20 for all prepared statement objects associated with the conn connection object:
((OracleConnection)conn).setDefaultExecuteBatch(20);
Even though this sets the default batch value for all the prepared statements of the connection, you can override it by calling setDefaultBatch() on individual Oracle prepared statements.
The connection batch value will apply to statement objects created after this batch value was set.
设置连接批次值
您可以为 Oracle 连接中的任何 Oracle 准备好的语句指定默认批处理值。> 为此，请使用 OracleConnection 对象的 setDefaultExecuteBatch() 方法。例如，以下代码将与 conn 连接对象关联的所有准备好的语句对象的默认批处理值设置为 20：
((OracleConnection)conn).setDefaultExecuteBatch(20);
即使这为连接的所有准备好的语句设置了默认批处理值，您也可以通过在单个 Oracle 准备好的语句上调用 setDefaultBatch() 来覆盖它。
连接批处理值将应用于设置此批处理值后创建的语句对象。
