Hello,
2015-09-11 0:57 GMT+02:00 Aner Perez <[email protected]>:
> Let me preface this by saying that I am on my second day of evaluating
> jOOQ so user error is a distinct possibility.
>
> Environment:
> Postgresql 9.4.4
> Postgresql jdbc driver 9.4-1202-jdbc42
> jOOQ 3.6.2
> Java 1.8.31
> Spring 4.1.7
>
> I am implementing a bulk upload of records by parsing a CSV file and
> loading them into a table. I have an existing script I execute using the
> psql command that can load 80K sample records in about a second using the
> "\copy" command provided by psql.
>
No jOOQ (or JDBC) based solution will beat \copy - or any other
database-provided loading mechanism. If speed is really important and there
is hardly any business logic, then you should stick to vendor-specific
loader commands...
> I have written code using jOOQ to load these same records in a Spring
> service function. The code parses the CSV and loads the data into Record
> objects that were generated by the jOOQ code generator from a database
> designed in Vertabelo (Very nice integration by the way!!!).
>
Thanks, glad to hear that this integration is working for you
> My service method looks like this:
>
> @Autowired
> private DefaultDSLContext jooq;
>
> @Transactional
> public void loadActuals(Collection<JRawActualsRecord> records) {
> jooq.deleteFrom(RAW_ACTUALS).execute();
> jooq.batchInsert(records).execute();
> }
>
> The delete runs in about 0.5 seconds but the insert of 80k records takes
> almost 15 minutes!
>
That's bad indeed!
First off, my immediate recommendation is for you to use jOOQ's loader API
instead of batchInsert(). There's native support for loading CSV data:
http://www.jooq.org/doc/latest/manual/sql-execution/importing/importing-csv
You can play around with:
1. bulk sizes (how many rows per insert)
2. batch sizes (how many inserts per statement)
3. commit sizes (how many statements per transaction)
Unlike the loader API, batchInsert() generates individual SQL statements
for each record and batches only those statements that are equal. If 80k
records produce equal SQL statements, that's a lot of useless SQL
rendering. We'll look into optimising this:
https://github.com/jOOQ/jOOQ/issues/4533
However, I'd still recommend using the loader API.
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
