CODEWITHSUNDEEP
javasqljooq
ManjuGH
ManjuGH

Reputation: 21

Caching Sequence from database to reduce db calls

we are developing batch application using JOOQ. Application requires to call sequence to get next value for persisting large number of records. This will definitely be overhead to the performance. Does JOOQ has any built in way of handling this or any other alternative to reduce the sequence call to cache the sequence generated value?

Upvotes: 1

Views: 510

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221145

I'm assuming you're planning on fetching e.g. 100 sequence numbers into the client in one go, in order to use them in a single, subsequent batch operation. This could be achieved using the following jOOQ statement, e.g. in Oracle:

SELECT seq.nextval
FROM dual
CONNECT BY level <= 100;

Or with jOOQ

ctx.select(SEQ.nextval())
   .connectBy(level().le(100))
   .fetch();

Depending on the RDBMS you're using, you will need to replace the connectBy() syntax by standard WITH syntax for recursion, or generate_series() in PostgreSQL, or otherwise query a table that has the number of rows in it that you need, as a workaround.

Upvotes: 1

Related Questions