使用IBATIS进行INSERTS的最快方法 [英] Fastest way for doing INSERTS using IBATIS

问题描述

我需要使用iBatis在单个表（SQL Server 2005）中插入20,000行。最快的方法是什么？我已经在使用批处理模式，但它没有多大帮助：

I need to insert 20,000 rows in a single table (SQL Server 2005) using iBatis. What's the fastest way to do it ? I'm already using batch mode, but it didn't help much:

try {
  sqlMap.startTransaction();
  sqlMap.startBatch();
  // ... execute statements in between
  sqlMap.commitTransaction();
} finally {
  sqlMap.endTransaction();
}

推荐答案

除了批量装载机，其他人都是参考，让我们考虑如何通过SQL做到最好。 （如果您将混合数据发送到不同的表，则批量加载器不能正常工作。）

Barring the bulk loaders others are referring to, let's consider how to best do it through SQL. (And the bulk loaders don't work well if you're sending intermixed data to different tables.)

首先，您不应该使用任何抽象层。重新使用，在这种情况下是iBatis，因为它实际上会为你提供很少的价值，但是抽象层将有一些（不一定很多，但有些）CPU成本。您应该只使用原始数据库连接。

First, you shouldn't be using whatever abstraction layer you're using, in this case iBatis, as it effectively will offer you little value, but that abstraction layer will have some (not necessarily much, but some) CPU cost. You should really simply use a raw database connection.

接下来，您将发送一堆INSERT语句。问题是你是否应该使用一个简单的字符串作为参数（即INSERT INTO TABLE1 VALUES（'x'，'y'，12））vs一个预备语句（INSERT INTO TABLE1 VALUES（？，？，？））。

Next, you'll be sending in a mess of INSERT statements. The question is whether you should use a simple string for the statment, (i.e. INSERT INTO TABLE1 VALUES('x','y', 12)) vs a prepared statement (INSERT INTO TABLE1 VALUES(?, ?, ?)).

这取决于您的数据库和数据库驱动程序。

That will depend on your database and DB drivers.

使用简单字符串的问题基本上是从内部格式（假设您正在插入Java数据）到字符串的转换成本。将数字或日期转换为String实际上是一个相当昂贵的CPU操作。一些数据库和驱动程序将直接使用二进制数据，而不仅仅是字符串数据。因此，在这种情况下，PreparedStatement可以节省一些CPU节省而可能不必转换数据。

The issue with using a simple string, is basically the conversion cost from an internal format (assuming you're inserting Java data) to the string. Converting a number or date to a String is actually a reasonably expensive CPU operation. Some databases and drivers will work with the binary data directly, rather than simply the string data. So, in that case a PreparedStatement could net some CPU savings in potentially not having to convert the data.

缺点是这个因素会因数据库供应商而异甚至是JDBC供应商。例如，Postgres（我相信）只适用于SQL字符串，而不是二进制文件，因此使用PreparedStatement是一种浪费，而不仅仅是自己构建字符串。

The downside is that this factor will vary by DB vendor, and potentially even the JDBC vendor. For example, Postgres (I believe) only works with SQL strings, rather than binary, so using a PreparedStatement is a waste over simply building the string yourself.

下一步，一次你有你的陈述类型，你想使用 addBatch （）方法。 addBatch的作用是将SQL语句分组到批处理中。好处是，您可以发送一个LARGE请求，而不是向DB发送多个请求。这减少了网络流量，并且会在吞吐量方面带来一些显着的提升。

Next, once you have your statement type, you want to use the addBatch() method of the JDBC Statement class. What addBatch does is it groups up the SQL statements in to, well, a batch. The benefit is that instead of sending several requests to the DB, you send a single LARGE request. This cuts down on network traffic, and will give some noticeable gains in throughput.

细节是并非所有的驱动程序/数据库都支持addBatch（至少不是很好），但是您的批次的大小也是有限的。您很可能无法为所有20,000行添加addBatch并期望它能够正常工作，尽管这将是最好的选择。此限制也可能因数据库而异。

The detail is that not all drivers/databases support addBatch (at least not well), but also the size of your batch is limited. You most likely can't addBatch for all 20,000 rows and expect it to work, though that would be the best bet. This limit, also, can vary by database.

对于Oracle，过去我使用的是64K的缓冲区。基本上我写了一个包装器函数，它将采用文字INSERT语句，并以64K批次累积它们。

For Oracle, in the past, I used a buffer of 64K. Basically I wrote a wrapper function that would take a literal INSERT statement, and accumulate them in 64K batches.

因此，如果你想通过JDBC批量插入数据，这些是做到这一点的方法。最大的改进是Batch模式，Statement vs PreparedStatement更可能节省一些CPU，如果你的驱动程序支持二进制协议，也可能是网络流量。

So, if you wanted to bulk insert data through SQL via JDBC, those are the ways to do it. The big improvement is the Batch mode, the Statement vs PreparedStatement is more to potentially conserve some CPU, and maybe network traffic if your driver supports a binary protocol.

测试，冲洗，并重复，直到你足够快乐。

Test, rinse, and repeat until you're happy enough.

这篇关于使用IBATIS进行INSERTS的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！