Insert Jooq Batch Record
I am currently trying to insert many records into a batch (~ 2000), and Jooq batchInsert does not do what I want.
I am converting the POJO to UpdatableRecords and then doing batchInsert, which does the insert for each record. So Jooq makes ~ 2000 queries for every package insert and kills database performance.
Executes this code (jooq 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);
}
}
I could just do it like this (because Jooq does the same internally):
records.forEach(UpdatableRecord::insert);
instead:
jooq.batchInsert(records).execute();
How can I tell Jooq to create new records in batch? Should I convert the records to bind requests and then call batchInsert? Any ideas?;)
source to share
jOOQ DSLContext.batchInsert()
creates a single batch JDBC statement for a set of sequential records with identical generated SQL strings (Javadoc does not formally define this, unfortunately).
This can be a problem when your posts look like this:
+------+--------+--------+ | COL1 | COL2 | COL3 | +------+--------+--------+ | 1* | {null} | {null} | | 2* | B* | {null} | | 3* | {null} | C* | | 4* | D* | D* | +------+--------+--------+
.. because in this 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 (?, ?, ?);
The reason for this default behavior is the fact that it is the only way to guarantee ... DEFAULT
behavior. As in SQL DEFAULT
. I have explained this behavior here .
With that in mind, and since every sequential SQL line is different, the inserts are unfortunately not packaged as one batch as you intended.
Solution 1: Make sure all modified flags true
One way to force all statements INSERT
to do the same is to set all modified flags of each individual record to true
:
for (Record r : records)
r.changed(true);
Now all SQL lines will be the same.
Solution 2: use the Loader
API
Instead of batch processing, you can import data (and specify batch size there). For details, see the tutorial section on importing records:
https://www.jooq.org/doc/latest/manual/sql-execution/importing/importing-records
Solution 3: use batch operator instead
Usage batchInsert()
is convenience that works when used TableRecords
. But of course you can generate the statement INSERT
manually and batch individual bind variables using the jOOQ API:
https://www.jooq.org/doc/latest/manual/sql-execution/batch-execution
Performance note
There are a couple of open issues related to DSLContext.batchInsert()
and similar API. A client-side algorithm that generates SQL rows for every single record is inefficient and can be changed in the future based on flags alone changed()
. Some actual problems:
source to share