Splitting large INSERTs

[jdoe]

Hello!

Appearing of lots of VALUES in my resulting INSERT query leads to exceeding the max_allowed_packet size.

I'm wondering if jOOQ offers any "splitting" functionality for laaaaarge INSERTs?

[Lukas Eder]

Not out of the box, and it probably doesn't make sense to do so out of the box.

Once you're in that situation, you might want to prefer using the Loader API, as documented here:

http://www.jooq.org/doc/latest/manual/sql-execution/importing/importing-csv

The loader API has the advantage that you can fine-tune:

- Bulk size (i.e. the number or rows per statement)

- Batch size (i.e. the number of statements per batch execution)

- Commit size (i.e. the number of batch executions per transaction)

The above metrics are highly database dependent. Usually, you shouldn't have too small / too big numbers for any of the above.

There's also been a similar discussion on this user group, recently:

https://groups.google.com/d/msg/jooq-user/PalloixXTr4/4ZCfA_gVGgAJ

In the upcoming jOOQ 3.7, we'll also support loading non-serialized data - i.e. other formats than CSV and JSON, such as arrays, or records

I hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[jdoe]

Thank you for your response!

For now the Loader API doesn't fit my needs so I had to resort to making a small utility class that manages a List of InsertQuery<...> internally and adds a new element to the list if (count++ % maxRecords == 0).

jOOQ shines in handling these queries via create.batch(queries).execute()!

jOOQ 3.7 looks promising and I think all its users appreciate your efforts to make it flawless before releasing! Don 't be tempted to fit it into some deadline limits ;)

воскресенье, 27 сентября 2015 г., 13:41:19 UTC+3 пользователь Lukas Eder написал:

[Lukas Eder]

2015-09-28 9:50 GMT+02:00 jdoe <ua.s...@gmail.com>:

Thank you for your response!

For now the Loader API doesn't fit my needs

What's missing? Something that we might still add in jOOQ 3.7, perhaps?

 

so I had to resort to making a small utility class that manages a List of InsertQuery<...> internally and adds a new element to the list if (count++ % maxRecords == 0).

jOOQ shines in handling these queries via create.batch(queries).execute()!

That works smoothly indeed, but beware that this is probably not the fastest solution.

 

jOOQ 3.7 looks promising and I think all its users appreciate your efforts to make it flawless before releasing! Don 't be tempted to fit it into some deadline limits ;)

It's flawless no matter the deadline, don't worry :)