Is there an upper limit to the number of bind calls in a JOOQ batch statement?

Question

We use batch statements when inserting as follows:

BatchBindStep batch = create.batch(create
   .insertInto(PERSON, ID, NAME)
   .values((Integer) null, null));

for (Person p : peopleToInsert) {
  batch.bind(p.getId(), p.getName());
}
batch.execute();

This has worked well in the past when inserting several thousands of objects. However, it raises a few questions:

1. Is there an upper limit to the number of .bind() calls for a batch?
2. If so, what does the limit depend on?
3. It seems to be possible to call .bind() again after having executed .execute(). Will .execute() clear previously bound values?

To clarify the last question: after the following code has executed...

BatchBindStep batch = create.batch(create
   .insertInto(PERSON, ID, NAME)
   .values((Integer) null, null));

batch.bind(1, "A");
batch.bind(2, "B");
batch.extecute();
batch.bind(3, "C");
batch.bind(4, "D");
batch.execute();

which result should I expect?

a)        b)
ID NAME   ID NAME
-------   -------
 1 A       1 A
 2 B       2 B
 3 C       1 A
 4 D       2 B
           3 C
           4 D

Unfortunately, neither the Javadoc nor the documentation discuss this particular usage pattern.

(I am asking this particular question because if I .execute() every 1000 binds or so to avoid said limit, I need to know whether I can reuse the batch objects for several .execute() calls or not.)

Answer 1

This answer is valid as of jOOQ 3.7

1. Is there an upper limit to the number of .bind() calls for a batch?

Not in jOOQ, but your JDBC driver / database server might have such limits.

2. If so, what does the limit depend on?

Several things:

• jOOQ keeps an intermediate buffer for all of the bound variables and binds them to a JDBC batch statement all at once. So, your client memory might also impose an upper limit. But jOOQ doesn't have any limits per se.

• Your JDBC driver might know such limits (see also this article on how jOOQ handles limits in non-batch statements). Known limits are:

  • SQLite: 999 bind variables per statement
  • Ingres 10.1.0: 1024 bind variables per statement
  • Sybase ASE 15.5: 2000 bind variables per statement
  • SQL Server 2008: 2100 bind variables per statement

  I'm not aware of any such limits in Oracle, but there probably are.

• Batch size is not the only thing you should tune when inserting large amounts of data. There are also:

  • Bulk size, i.e. the number of rows inserted per statement
  • Batch size, i.e. the number of statements per batch sent to the server
  • Commit size, i.e. the number of batches committed in a single transaction

  Tuning your insertion boils down to tuning all of the above. jOOQ ships with a dedicated importing API where you can tune all of the above: http://www.jooq.org/doc/latest/manual/sql-execution/importing

• You should also consider bypassing SQL for insertions into a loader table, e.g. using Oracle's SQL*Loader. Once you've inserted all data, you can move it to the "real table" using PL/SQL's FORALL statement, which is PL/SQL's version of JDBC's batch statement. This approach will out perform anything you do with JDBC.

3. It seems to be possible to call .bind() again after having executed .execute(). Will .execute() clear previously bound values?

Currently, execute() will not clear the bind values. You'll need to create a new statement instead. This is unlikely to change, as future jOOQ versions will favour immutability in its API design.