java - Spring data JPA batch insert is very slow -


i trying read excel file 700k+ records , batch insert in mysql database table.

please note, excel parsing fast , can entity objects in arraylist within 50 seconds or so.

i using spring boot , spring data jpa.

below partial application.properties file:

hibernate.jdbc.batch_size=1000 spring.jpa.hibernate.use-new-id-generator-mappings=true

and partial entity class:

@entity @table(name = "wht_apps", schema = "test") public class whtapps {      @id     @tablegenerator(name = "whtappsgen", table = "id_gen", pkcolumnname = "gen_key", valuecolumnname = "gen_val")     @generatedvalue(strategy = generationtype.table, generator = "whtappsgen")     private long id;      @column(name = "vendor_code")     private int vendorcode;     .     .     .     .

below dao:

@repository @transactional public class japanwhtdaoimpl implements japanwhtdao {      @autowired     japanwhtappsrepository appsrepo;      @override     public void storeapps(list<whtapps> whtappslist) {         appsrepo.save(whtappslist);      }

and below repository class:

@transactional public interface japanwhtappsrepository extends jparepository<whtapps, long> {  }

can please enlighten me doing incorrect here?

edit:

process not finish , throws error eventually:-

2017-08-15 15:15:24.516  warn 14710 --- [tp1413491716-17] o.h.engine.jdbc.spi.sqlexceptionhelper   : sql error: 0, sqlstate: 08s01 2017-08-15 15:15:24.516 error 14710 --- [tp1413491716-17] o.h.engine.jdbc.spi.sqlexceptionhelper   : communications link failure  last packet received server 107,472 milliseconds ago.  last packet sent server 107,472 milliseconds ago. 2017-08-15 15:15:24.518  info 14710 --- [tp1413491716-17] o.h.e.j.b.internal.abstractbatchimpl     : hhh000010: on release of batch still contained jdbc statements 2017-08-15 15:15:24.525  warn 14710 --- [tp1413491716-17] c.m.v.c3p0.impl.defaultconnectiontester  : sql state '08007' of exception tested statusonexception() implies database invalid, , pool should refill fresh connections.  com.mysql.jdbc.exceptions.jdbc4.mysqlnontransientconnectionexception: communications link failure during rollback(). transaction resolution unknown.     @ sun.reflect.nativeconstructoraccessorimpl.newinstance0(native method) ~[na:1.8.0_131]     @ sun.reflect.nativeconstructoraccessorimpl.newinstance(nativeconstructoraccessorimpl.java:62) ~[na:1.8.0_131]     @ sun.reflect.delegatingconstructoraccessorimpl.newinstance(delegatingconstructoraccessorimpl.java:45) ~[na:1.8.0_131]     @ java.lang.reflect.constructor.newinstance(constructor.java:423) ~[na:1.8.0_131]     @ com.mysql.jdbc.util.handlenewinstance(util.java:425) ~[mysql-connector-java-5.1.43.jar:5.1.43]     .     .     .     .     2017-08-15 15:15:24.526  warn 14710 --- [tp1413491716-17] c.m.v2.c3p0.impl.newpooledconnection     : [c3p0] pooledconnection has signalled connection error still in use! 2017-08-15 15:15:24.527  warn 14710 --- [tp1413491716-17] c.m.v2.c3p0.impl.newpooledconnection     : [c3p0] error has occurred [ com.mysql.jdbc.exceptions.jdbc4.mysqlnontransientconnectionexception: communications link failure during rollback(). transaction resolution unknown. ] not reported listeners!  com.mysql.jdbc.exceptions.jdbc4.mysqlnontransientconnectionexception: communications link failure during rollback(). transaction resolution unknown.     @ sun.reflect.nativeconstructoraccessorimpl.newinstance0(native method) ~[na:1.8.0_131]

thanks

i point 1 more thing. problem not hibernate db.

when insert 700k objects in 1 transaction stored in db's rollback segment waiting transaction commit.

if possible split logic have commits in middle.

create 1k sized sublists main list, save sublists , commit after each sublist saving.


Comments

Post a Comment

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

python Tkinter Capturing keyboard events save as one single string -

is possible store keyboard events 1 string? code below stores 1 char , prints it. card reader or bar code reader, contains collection of character/ string not 1 character @ time. goal save char pressed text variable. from tkinter import * root = tk() def key(event): text= event.char text+= event.char print ("pressed", text) def callback(event): frame.focus_set() print ("clicked at", event.x, event.y) frame = frame(root, width=100, height=100) frame.bind("<key>", key) frame.bind("<button-1>", callback) frame.pack() root.mainloop() currently, creating text variable , everytime key() function gets called, , text stores last character typed. you can define text module level variable , , use module level text inside key function - text = '' def key(event): global text text+= event.char print("pressed", text)
Read more

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -

here tables: create table customer ( cid int primary key, name varchar(32) ) create table ord ( oid int primary key, cid int foreign key references customer, address varchar(20) ) here's linq-to-sql statement: var aaa = c in db.customer select new { c, o = c.ord.tolist() }; and here's inexplicable query generated linq-to-sql: select [t0].[cid] [cid], [t0].[name] [name], [t1].[oid] [oid], [t1].[cid] [cid2], [t1].[address] [address], (select count(*) [dbo].[ord] [t2] [t2].[cid] = [t0].[cid]) [value] [dbo].[customer] [t0] left outer join [dbo].[ord] [t1] on [t1].[cid] = [t0].[cid] order [t0].[cid], [t1].[oid] i want understand how rid of count(*) part. it's entirely uncalled for! linq needs count(*) in order determine number of entries returned o = c.ord.tolist() .
Read more