Jooq批记录插入 [英] Jooq batch record insert
问题描述
我目前正在尝试批量插入许多记录(〜2000条)，而Jooq的batchInsert并没有满足我的要求.
I'm currently trying to insert in batch many records (~2000) and Jooq's batchInsert is not doing what I want.
我将POJO转换为UpdatableRecords，然后执行batchInsert，该批处理将为每个记录执行插入.因此，Jooq对每个批处理插入执行约2000次查询，这会破坏数据库性能.
I'm transforming POJOs into UpdatableRecords and then I'm performing batchInsert which is executing insert for each record. So Jooq is doing ~2000 queries for each batch insert and it's killing database performance.
它正在执行以下代码(jooq的批处理插入):
It's executing this code (jooq's batch insert):
for (int i = 0; i < records.length; i++) {
Configuration previous = ((AttachableInternal) records[i]).configuration();
try {
records[i].attach(local);
executeAction(i);
}
catch (QueryCollectorSignal e) {
Query query = e.getQuery();
String sql = e.getSQL();
// Aggregate executable queries by identical SQL
if (query.isExecutable()) {
List<Query> list = queries.get(sql);
if (list == null) {
list = new ArrayList<Query>();
queries.put(sql, list);
}
list.add(query);
}
}
finally {
records[i].attach(previous);
}
}
我可以这样做(因为Jooq在内部做同样的事情):
I could just do it like this (because Jooq is doing same thing internally):
records.forEach(UpdatableRecord::insert);
代替:
jooq.batchInsert(records).execute();
如何告诉Jooq以批处理方式创建新记录?我应该将记录转换为绑定查询，然后调用batchInsert吗?有任何想法吗? ;)
How can I tell Jooq to create new records in batch mode? Should I transform records into bind queries and then call batchInsert? Any ideas? ;)
推荐答案
jOOQ's DSLContext.batchInsert() creates one JDBC batch statement per set of consecutive records with identical generated SQL strings (the Javadoc doesn't formally define this, unfortunately).
当您的记录如下所示时，这可能会变成问题:
This can turn into a problem when your records look like this:
+------+--------+--------+
| COL1 | COL2 | COL3 |
+------+--------+--------+
| 1* | {null} | {null} |
| 2* | B* | {null} |
| 3* | {null} | C* |
| 4* | D* | D* |
+------+--------+--------+
..因为在这种情况下，生成的SQL字符串将如下所示:
.. because in that case, the generated SQL strings will look like this:
INSERT INTO t (col1) VALUES (?);
INSERT INTO t (col1, col2) VALUES (?, ?);
INSERT INTO t (col1, col3) VALUES (?, ?);
INSERT INTO t (col1, col2, col3) VALUES (?, ?, ?);
此默认行为的原因是这样的事实，这是保证... DEFAULT行为的唯一方法.与在SQL DEFAULT中一样. 我在这里给出了这种行为的理由.
The reason for this default behaviour is the fact that this is the only way to guarantee ... DEFAULT behaviour. As in SQL DEFAULT. I gave a rationale of this behaviour here.
考虑到这一点，并且由于每个连续的SQL字符串都不同，因此不幸的是，插入并没有按您的预期那样批量处理.
With this in mind, and as each consecutive SQL string is different, the inserts unfortunately aren't batched as a single batch as you intended.
强制所有INSERT语句相同的一种方法是将每个单独记录的所有已更改标志设置为true:
One way to enforce all INSERT statements to be the same is to set all changed flags of each individula record to true:
for (Record r : records)
r.changed(true);
现在，所有SQL字符串都将相同.
Now, all SQL strings will be the same.
代替批处理，您可以导入数据(并在其中指定批处理大小).有关详细信息，请参见手册中有关导入记录的部分:
Instead of batching, you could import the data (and specify batch sizes there). For details, see the manual's section about importing records:
https://www.jooq.org /doc/latest/manual/sql-execution/importing/importing-records
使用batchInsert()的便利之处在于使用TableRecords时可以使用.但是，当然，您可以手动生成INSERT语句，并使用jOOQ的批处理语句API批处理各个绑定变量:
Your usage of batchInsert() is convenience that works when using TableRecords. But of course, you can generate an INSERT statement manually and batch the individual bind variables by using jOOQ's batch statement API:
https://www.jooq.org/doc /latest/manual/sql-execution/batch-execution
关于DSLContext.batchInsert()和类似的API，存在一些未解决的问题.为每个单独的记录生成SQL字符串的客户端算法效率低下，将来可能会更改，直接依赖于changed()标志.一些相关问题:
There are a couple of open issues regarding the DSLContext.batchInsert() and similar API. The client side algorithm that generates SQL strings for each individual record is inefficient and might be changed in the future, relying on changed() flags directly. Some relevant issues:
- https://github.com/jOOQ/jOOQ/issues/4533
- https://github.com/jOOQ/jOOQ/issues/6294
这篇关于Jooq批记录插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
