jdbc - Eclipselink and Postgresql batch writting -

i have been working on bulksms solution 1 of customers, , have decided use jpa (eclipselink) orm , underlying database postgresql 9.5.1.

my problem issue whenever send request 65,000 records persisted takes around 27 seconds complete operation. decided implement sequence pooling, sequence preallocation =1000, , batch writing, managed remove 15 seconds operation.

after investigating database logs noticed same queries being called before , after applying optimization.

here optimized persistance.xml:

<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xsi:schemalocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"> <persistence-unit name="com.kw.ktt.sms.server" transaction-type="jta">     <jta-data-source>smsdb</jta-data-source>      <non-jta-data-source>sequence</non-jta-data-source>      <class>com.kw.ktt.sms.server.core.testclass</class>     <class>com.kw.ktt.sms.server.jpa.customer</class>     <class>com.kw.ktt.sms.server.jpa.smsaccount</class>     <class>com.kw.ktt.sms.server.jpa.smstransaction</class>     <class>com.kw.ktt.sms.server.jpa.contactgroup</class>     <class>com.kw.ktt.sms.server.jpa.phonenumber</class>     <properties>         <property name="eclipselink.application-location" value="/users/mousaalsulaimi/desktop"/>         <property name="eclipselink.ddl-generation.output-mode" value="database"/>         <property name="eclipselink.logging.connection" value="true"/>         <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>         <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/>         <property name="eclipselink.jdbc.batch-writing" value="jdbc" />          <property name="eclipselink.jdbc.batch-writing.size" value="1000"/>          <property name="eclipselink.jdbc.sequence-connection-pool"  value="true" />         <property name="eclipselink.connection-pool.sequence.nonjtadatasource" value="sequence"/>         <property name="eclipselink.connection-pool.sequence.intial" value="1000" />      </properties> </persistence-unit> 

as mentioned above i'm using jta connection pool persistence (called smsdb) , non jta connection sequencing (called sequence) each different db user in order track connection db log.

logs unoptimized connection are here - sample of 10 records only.

the logs optimized connection are here - sample of 10 records only.

can explain me doing wrong , why both persistence setting produce same queries though there actual improvement of 15 seconds.

one more thing , have set sequence preallocation 1000 in entitiy's source code , judging database logs sequencing working expected , picking correct increment value . i'm concerned batch writing , fear not set correctly in persistence.xml

update

i have enables logging in eclipse link advised chris , here eclipselink logs produced when using optimized persistence.xml

2016-02-27t23:59:28.307+0300|fine: select customerid, civilidnumber, createdate, createdby, email, fullname, isactive, isadmin, male, password, personal, phonenumber, status, username, account_smsaccountid customer (customerid = ?)     bind => [1 parameter bound] 2016-02-27t23:59:28.310+0300|fine: select smsaccountid, oooredooo_balance, viva_balance, zain_balance smsaccount (smsaccountid = ?)     bind => [1 parameter bound] 2016-02-27t23:59:28.312+0300|fine: select nextval('seq_gen_sequence') 2016-02-27t23:59:28.327+0300|fine: select nextval('number_seq') 2016-02-27t23:59:28.331+0300|info: id 1 2016-02-27t23:59:28.332+0300|fine: insert smsaccount (smsaccountid, oooredooo_balance, viva_balance, zain_balance) values (?, ?, ?, ?)     bind => [4 parameters bound] 2016-02-27t23:59:28.335+0300|fine: insert customer (customerid, civilidnumber, createdate, createdby, email, fullname, isactive, isadmin, male, password, personal, phonenumber, status, username, account_smsaccountid) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)     bind => [15 parameters bound] 2016-02-27t23:59:28.337+0300|fine: insert contactgroup (groupid, createby, createdate, groupdescription, groupname) values (?, ?, ?, ?, ?)     bind => [5 parameters bound] 2016-02-27t23:59:28.339+0300|fine: insert phonenumber (numberid, operator, phonenumber) values (?, ?, ?) 2016-02-27t23:59:28.339+0300|fine: bind => [3 parameters bound] 2016-02-27t23:59:28.339+0300|fine: bind => [3 parameters bound] 2016-02-27t23:59:28.339+0300|fine: bind => [3 parameters bound] 2016-02-27t23:59:28.339+0300|fine: bind => [3 parameters bound] 2016-02-27t23:59:28.339+0300|fine: bind => [3 parameters bound] 2016-02-27t23:59:28.339+0300|fine: bind => [3 parameters bound] 2016-02-27t23:59:28.339+0300|fine: bind => [3 parameters bound] 2016-02-27t23:59:28.340+0300|fine: bind => [3 parameters bound] 2016-02-27t23:59:28.340+0300|fine: bind => [3 parameters bound] 2016-02-27t23:59:28.340+0300|fine: bind => [3 parameters bound] 2016-02-27t23:59:28.342+0300|fine: update contactgroup set customerid = ? (groupid = ?)     bind => [2 parameters bound] 2016-02-27t23:59:28.343+0300|fine: update phonenumber set groupid = ? (numberid = ?) 2016-02-27t23:59:28.344+0300|fine: bind => [2 parameters bound] 2016-02-27t23:59:28.344+0300|fine: bind => [2 parameters bound] 2016-02-27t23:59:28.344+0300|fine: bind => [2 parameters bound] 2016-02-27t23:59:28.344+0300|fine: bind => [2 parameters bound] 2016-02-27t23:59:28.344+0300|fine: bind => [2 parameters bound] 2016-02-27t23:59:28.344+0300|fine: bind => [2 parameters bound] 2016-02-27t23:59:28.344+0300|fine: bind => [2 parameters bound] 2016-02-27t23:59:28.344+0300|fine: bind => [2 parameters bound] 2016-02-27t23:59:28.344+0300|fine: bind => [2 parameters bound] 2016-02-27t23:59:28.344+0300|fine: bind => [2 parameters bound] 

and below eclipse link logs produced when using original persistence.xml

2016-02-28t08:56:25.440+0300|fine: select customerid, civilidnumber, createdate, createdby, email, fullname, isactive, isadmin, male, password, personal, phonenumber, status, username, account_smsaccountid customer (customerid = ?)     bind => [1 parameter bound] 2016-02-28t08:56:25.443+0300|fine: select smsaccountid, oooredooo_balance, viva_balance, zain_balance smsaccount (smsaccountid = ?)     bind => [1 parameter bound] 2016-02-28t08:56:25.445+0300|fine: select nextval('seq_gen_sequence') 2016-02-28t08:56:25.447+0300|fine: select nextval('number_seq') 2016-02-28t08:56:25.449+0300|info: id 1 2016-02-28t08:56:25.450+0300|fine: insert smsaccount (smsaccountid, oooredooo_balance, viva_balance, zain_balance) values (?, ?, ?, ?)     bind => [4 parameters bound] 2016-02-28t08:56:25.451+0300|fine: insert customer (customerid, civilidnumber, createdate, createdby, email, fullname, isactive, isadmin, male, password, personal, phonenumber, status, username, account_smsaccountid) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)     bind => [15 parameters bound] 2016-02-28t08:56:25.452+0300|fine: insert contactgroup (groupid, createby, createdate, groupdescription, groupname) values (?, ?, ?, ?, ?)     bind => [5 parameters bound] 2016-02-28t08:56:25.452+0300|fine: insert phonenumber (numberid, operator, phonenumber) values (?, ?, ?)     bind => [3 parameters bound] 2016-02-28t08:56:25.453+0300|fine: insert phonenumber (numberid, operator, phonenumber) values (?, ?, ?)     bind => [3 parameters bound] 2016-02-28t08:56:25.453+0300|fine: insert phonenumber (numberid, operator, phonenumber) values (?, ?, ?)     bind => [3 parameters bound] 2016-02-28t08:56:25.454+0300|fine: insert phonenumber (numberid, operator, phonenumber) values (?, ?, ?)     bind => [3 parameters bound] 2016-02-28t08:56:25.454+0300|fine: insert phonenumber (numberid, operator, phonenumber) values (?, ?, ?)     bind => [3 parameters bound] 2016-02-28t08:56:25.454+0300|fine: insert phonenumber (numberid, operator, phonenumber) values (?, ?, ?)     bind => [3 parameters bound] 2016-02-28t08:56:25.455+0300|fine: insert phonenumber (numberid, operator, phonenumber) values (?, ?, ?)     bind => [3 parameters bound] 2016-02-28t08:56:25.455+0300|fine: insert phonenumber (numberid, operator, phonenumber) values (?, ?, ?)     bind => [3 parameters bound] 2016-02-28t08:56:25.455+0300|fine: insert phonenumber (numberid, operator, phonenumber) values (?, ?, ?)     bind => [3 parameters bound] 2016-02-28t08:56:25.456+0300|fine: insert phonenumber (numberid, operator, phonenumber) values (?, ?, ?)     bind => [3 parameters bound] 2016-02-28t08:56:25.456+0300|fine: update contactgroup set customerid = ? (groupid = ?)     bind => [2 parameters bound] 2016-02-28t08:56:25.457+0300|fine: update phonenumber set groupid = ? (numberid = ?)     bind => [2 parameters bound] 2016-02-28t08:56:25.457+0300|fine: update phonenumber set groupid = ? (numberid = ?)     bind => [2 parameters bound] 2016-02-28t08:56:25.458+0300|fine: update phonenumber set groupid = ? (numberid = ?)     bind => [2 parameters bound] 2016-02-28t08:56:25.458+0300|fine: update phonenumber set groupid = ? (numberid = ?)     bind => [2 parameters bound] 2016-02-28t08:56:25.459+0300|fine: update phonenumber set groupid = ? (numberid = ?)     bind => [2 parameters bound] 2016-02-28t08:56:25.459+0300|fine: update phonenumber set groupid = ? (numberid = ?)     bind => [2 parameters bound] 2016-02-28t08:56:25.460+0300|fine: update phonenumber set groupid = ? (numberid = ?)     bind => [2 parameters bound] 2016-02-28t08:56:25.460+0300|fine: update phonenumber set groupid = ? (numberid = ?)     bind => [2 parameters bound] 2016-02-28t08:56:25.460+0300|fine: update phonenumber set groupid = ? (numberid = ?)     bind => [2 parameters bound] 2016-02-28t08:56:25.461+0300|fine: update phonenumber set groupid = ? (numberid = ?)     bind => [2 parameters bound] 

obviously there big difference between queries produced when using optimized persistence.xml , original persistence.xml .

thank

turn on eclipselink's sql logging , should see difference in how statements prepared , processed in jdbc, should show why there 15 second difference.

i'm not familiar eclipselink.connection-pool.sequence.intial property - should using allocationsize configuration within sequence generator allow obtaining 1000 sequences @ time.

if not set, batch writing cut down number of insert statements, still see large number of statements obtain sequence numbers, on different connections - sequencing using own connection pool.