Thanks for your time! Comments inline
On Friday, September 11, 2015 at 12:42:57 PM UTC-4, Lukas Eder wrote: > > > That's bad indeed! > > Yes, 1000 times slower is always a cause for concern :-) > 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 > Awesome, I was not aware of that functionality! Unfortunately, I just tried this and it did not make any perceptible difference, still around 15 minutes to upload! I did have debug logging turned on and with loadInto().loadCSV() I see debug log messages for each of the 80k inserts that looked like this: 15:19:57.691 [http-apr-8084-exec-73] DEBUG org.jooq.tools.StopWatch - Query executed : Total: 16.3ms 15:19:57.691 [http-apr-8084-exec-73] DEBUG org.jooq.tools.StopWatch - Finishing : Total: 16.312ms, +0.011ms 15:19:57.691 [http-apr-8084-exec-73] DEBUG org.jooq.tools.LoggerListener - Executing query : insert into "raw_actuals" ("year", "month", "sales_manager_number", "sales_manager_desc", "associate", "associate_number", "associate_desc", "customer_channel", "team", "lr3", "lr4", "lr5", "regional_product_material_group", "trial_indicator", "regional_product_sub_brand", "regional_product_brand", "net_direct_sales_amt", "net_direct_units", "national_net_combined_sales_amt", "national_net_combined_revenue_units", "strategic_net_combined_sales_amt", "strategic_net_combined_revenue_units") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 15:19:57.691 [http-apr-8084-exec-73] DEBUG org.jooq.tools.LoggerListener - -> with bind values : insert into "raw_actuals" ("year", "month", "sales_manager_number", "sales_manager_desc", "associate", "associate_number", "associate_desc", "customer_channel", "team", "lr3", "lr4", "lr5", "regional_product_material_group", "trial_indicator", "regional_product_sub_brand", "regional_product_brand", "net_direct_sales_amt", "net_direct_units", "national_net_combined_sales_amt", "national_net_combined_revenue_units", "strategic_net_combined_sales_amt", "strategic_net_combined_revenue_units") values (/snip DATA REMOVED /snip) 15:19:57.691 [http-apr-8084-exec-73] DEBUG o.s.j.d. LazyConnectionDataSourceProxy - Using existing database connection for operation 'prepareStatement' 15:19:57.701 [http-apr-8084-exec-73] DEBUG org.jooq.tools.LoggerListener - Affected row(s) : 1 I thought maybe the debug messages were killing performance but taking them out only shaved around 20 seconds from the 15 minute total. The message "Using existing database connection for operation 'prepareStatement'" makes it sound like each insert is running a separate prepareStatement. Hopefully that is just confusing wording in the message? 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) > Unfortunately, I'm currently using container managed transactions so I would not be able to use these. 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. > If I can get the loader api to perform reasonably well I will definitely use it since it does exactly what I need. > I hope this helps, > Lukas > Once again, thanks. Your suggestions were very useful but I still have to figure out what is going on here. I have also tried loading using a ByteArrayInputStream to rule out other I/O issues loading the file but that only shaved off about 10 seconds. My next attempt will be to increase the memory on the jvm to rule out garbage collection issues and if that does not help I guess I'll have to use as a profiler. -- 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.
