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.
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!!!).
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! The following log snippet tells the story.
17:48:15.353 [http-apr-8084-exec-15] DEBUG org.jooq.tools.LoggerListener -
> Executing query : delete from "raw_actuals"
> 17:48:15.366 [http-apr-8084-exec-15] DEBUG
> o.s.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from
> DataSource
> 17:48:15.367 [http-apr-8084-exec-15] DEBUG
> o.s.jdbc.datasource.DataSourceUtils - Registering transaction
> synchronization for JDBC Connection
> 17:48:15.369 [http-apr-8084-exec-15] DEBUG
> o.s.j.d.LazyConnectionDataSourceProxy - Connecting to database for
> operation 'prepareStatement'
> 17:48:15.773 [http-apr-8084-exec-15] DEBUG org.jooq.tools.LoggerListener -
> Affected row(s) : 80223
> 17:48:15.773 [http-apr-8084-exec-15] DEBUG org.jooq.tools.StopWatch -
> Query executed : Total: 465.477ms
> 17:48:15.774 [http-apr-8084-exec-15] DEBUG org.jooq.tools.StopWatch -
> Finishing : Total: 466.433ms, +0.956ms
> 17:48:23.317 [http-apr-8084-exec-15] DEBUG org.jooq.tools.LoggerListener -
> Executing batch query : insert into "raw_actuals" ("year", "month",
> "lr3", "lr4", "lr5", "customer_channel", "regional_product_brand",
> "regional_product_sub_brand", "regional_product_material_group",
> "trial_indicator", "net_direct_sales_amt", "net_direct_units", "team",
> "national_net_combined_sales_amt", "national_net_combined_revenue_units",
> "strategic_net_combined_sales_amt", "strategic_net_combined_revenue_units",
> "associate", "associate_number", "associate_desc", "sales_manager_number",
> "sales_manager_desc") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?)
> 17:48:23.317 [http-apr-8084-exec-15] DEBUG
> o.s.j.d.LazyConnectionDataSourceProxy - Using existing database connection
> for operation 'prepareStatement'
> 18:03:08.812 [http-apr-8084-exec-15] DEBUG org.jooq.tools.StopWatch -
> Query executed : Total: 14:45
> 18:03:08.812 [http-apr-8084-exec-15] DEBUG org.jooq.tools.StopWatch -
> Finishing : Total: 14:45, +0.207ms
> 18:03:08.840 [http-apr-8084-exec-15] DEBUG
> o.s.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to
> DataSource
> 18:03:08.906 [http-apr-8084-exec-15] DEBUG
> o.s.j.d.LazyConnectionDataSourceProxy - Using existing database connection
> for operation 'close'
> 18:03:08.906 [http-apr-8084-exec-15] DEBUG
> o.s.orm.jpa.JpaTransactionManager - Initiating transaction commit
> 18:03:08.906 [http-apr-8084-exec-15] DEBUG
> o.s.orm.jpa.JpaTransactionManager - Committing JPA transaction on
> EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@2343b67e]
> 18:03:08.906 [http-apr-8084-exec-15] DEBUG
> o.h.e.t.spi.AbstractTransactionImpl - committing
> 18:03:08.906 [http-apr-8084-exec-15] DEBUG o.h.e.t.i.jdbc.JdbcTransaction
> - committed JDBC Connection
I can't explain what could cause such excessively slow performance. Any
pointers on where I can start looking for answers?
Thanks!
- Aner
--
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.
