| Strange behavior of property eclipselink.jdbc.batch-writing [message #1844169] |
Fri, 03 September 2021 23:15  |
 r sr
Messages: 1
Registered: September 2021 |
Junior Member |
|
|
Hello,
today I was trying to improve bulk insert performance and enabled batch-writing by adding the following two properties to my persistenc.xml:
<property name="eclipselink.jdbc.batch-writing" value="JDBC"/>
<property name="eclipselink.jdbc.batch-writing.size" value="1000"/>
Surprisingly, this configuration had no effect.
I remember, that I did something similar 4 years ago and the performance gain was huge (Oracle DB, Payara 4 with EclipseLink 2.5)
My environemt today is Payara Micro 5.2020.4 with EclipseLink 2.7.6, PostgreSQL 12.7 with jdbc driver version 42.2.19
After adding the jdbc driver property "reWriteBatchedInsert" = true, the perfomance was much better, execution time decreased from 61223 ms to 11756 ms.
Still strange is, that eclipselink produced single INSERT statements. After enabling statement logging in the PostgreSQL server, I could recognize, that the postgres jdbc driver had sent the statements in bulks.
Since I was still not completely satisfied because EclipseLink generated single INSERT statements instead of batch statements, I simply configured "ORACLE-JDBC" for batch-writing and the performance improved again! The execution time decreased to 7531 ms. The EclipseLink logs now don't show single INSERT statements, but the INSERT statement is logged ONCE and after that there are only parameter ==> bind log entries.
Summary: INSERT 100000 rows with EclipseLink + PostgreSQL (!)
- without optimization: 61223 ms
- batch-writing=JDBC plus reWriteBatchedInserts = true : 11756 ms
- batch-writing=ORACLE-JDBC, without reWriteBatchInserts : 7531 ms
Can someone explain what is going on here?
Shall I use the ORACLE-JDBC setting together with a PostgeSQL database?
I am looking forward to interesting answers!
Best Regards,
RSR
|
|
|
| Re: Strange behavior of property eclipselink.jdbc.batch-writing [message #1848680 is a reply to message #1844169] |
Mon, 13 December 2021 18:40  |
Chris Delahunt
Messages: 43
Registered: December 2021 |
Member |
|
|
Checking out how the ORACLE-JDBC setting is used and it sets the usesJDBCBatchWriting to true just as it would for JDBC batch writing, but also sets the usesNativeBatchWriting DatabasePlatform boolean to true. The usesNativeBatchWriting flag though only serves to unwrap connections, so that the OraclePlatform classes can deal with Oracle connections and Oracle statements, which it then uses in prepareBatchStatement to call setExecuteBatch on the OraclePreparedStatement.
Postgres and other DatabasePlatform have no such special handling, so the only differences I can see from the two settings is that the unwrapped connection is used to build the statement used for batching. If this makes any difference, as EclipseLink seems to make the same JDBC calls in both cases, your container's wrapped connection handling of those calls would seem to be the cause. I'd suggest that since reWriteBatchedInsert does not affect EclipseLink behaviour, only the driver handling of JDBC method calls, that you try it with both the JDBC and Oracle-JDBC batch writing options. IF there are differences, you'd want to see the statement generated and check the Payara docs/support.
Best Regards,
Chris
|
|
|
Powered by
FUDForum. Page generated in 0.01544 seconds
] 
Search
Help
Register
Login
Home
