How I improved JPA insert performance by 1300%

JPA has always been considered to be slower than plain JDBC or lighter frameworks like MyBatis. Bulk inserts have been considered the cultpit of JPA.

I recently had to implement a small report archiving for a client (financial organization) . The goal of the project was to archive status reports sent to customers, so that they could be searched for and retrieved.

The input to the archiving system is an XML file containing metadata regarding all reports and one big PDF file that includes all reports. The metadata is parsed and inserted into a database, the database can then be searched for a specific report or a set of reports. For each report there is an index into the large PDF file (using start page and number of pages) that can be used to extract a specific PDF report from the large PDF file.

The project included a batch process to import the data into the archive system as well as a web service front end that supports looking up reports and extracting the report from the large PDF file.

The batch process will need to support archiving about 400k reports in one run. Each report will create a ReportArchive entity in the DB which also contains a one-to-many relationship with an additional metadata entity.

While JPA is not the most efficient technology to use for bulk inserts, it is the standard API used in all Java project by the customer, so it was the default choice.

I used STAX to parse the XML file and IText to extract pages from the large PDF file, with eclipselink as the JPA provider, but what I wanted to talk about was the simple optimizations that helped me improve the JPA insert performance by a magnitude of over 13 (!!).

Initial Run

The initial run has a simple JPA persistence implementation with a commit on each insert of the ReportArchive entity. The main entity the ReportArchive entity had a Cascade.ALL on its child entity.

I had a sample batch file with 5786 reports and it took 7:43 minutes to insert those reports.

Optimization # 1

I decided to perform a commit every 500 inserts in order to improve performance.

I also configured eclipselinke to use JDBC batching using the following configuration settings in persistence.xml:

<property name="eclipselink.jdbc.batch-writing" value="JDBC"/>

<property name="eclipselink.jdbc.batch-writing.size" value="1000"/>

The result: performance improved to 5:09 minutes for inserting the same 5786 reports.

I also tried to change the commit batch size and played with various sizes from 200 to 1000, but at least in my limited testing 500 seemed to be the optimal size.

Optimization #2

The ReportArchive entity had a unique key in report id, but it also had a report sequence which was a database auto increment field (DB2 was the database). I decided to remove report sequence and use the report id as the key since it’s unique in any case.

The result: performance improved to 3:29 minutes for the same 5786 reports.

I also tried to further optimize performance by calling clear() on the entityManager between batch commits, however that did not produce any performance improvements.

Optimization #3

I added caching of sql statements and turned eclipselink’s JPA logging off by adding the following configuration settings to persistenc.xml :

<property name="eclipselink.jdbc.cache-statements" value="true"/>

<property name="eclipselink.logging.level" value="off"/>

The result: performance increased dramatically to 35 seconds (!!!) for the same 5786 reports.

Summary

In order to dramatically improve JPA insert performance if you are using EclipseLink :

1. Remove database auto increment columns as your primary keys if possible
2. Perform commits in batches - 500 was the magic number for my application.
3. Add the following eclipseLink configuration settings:

<property name="eclipselink.jdbc.batch-writing" value="JDBC"/>

<property name="eclipselink.jdbc.batch-writing.size" value="1000"/>

<property name="eclipselink.jdbc.cache-statements" value="true"/>

<property name="eclipselink.logging.level" value="off"/>

My Experience with ExtGWT

I recently had to develop a GWT application as freelance work for a client.
The application was actually a rewrite of an old application developed with jsp and extJS. To keep the same look and feel I chose to use the extGWT library having read some very good recommendations on it. I developed the application using the MVP pattern which I found pretty straight forward to implement once you get the hang of it.
The extGWT library itself is very nice with many widgets to choose from, however I did find the following drawbacks:
1) css styling is difficult. extGWT comes with several themes, which look very nice compared to bare bones gwt widgets. However their css styling is not well documented. I found myself using firebug to identify which class to change.
2) The  extGWT  library widgets have their own inheritance hierarchy, which means that an extGWT button for example, it totally different than a GWT button, it does not implement HasClickHandlers and does not inherit from ButtonBase or FocusWidget. So, you need to use different event handlers and if you want to expose your widgets through their interface to the Presenter, as in the MVP pattern, you find some problems. For example the addSelectionHandler() method of the extGWT Button is a method of Button and not its interface, so you need to expose Button to your presenter and not its interface.
3) Lack of a community site - the community version of extGWT is hosted in the Sencha web site. Kind of makes you wonder how much Sencha controls the community edition.
This was my first experience developing an application with extGWT and so I ran into several problems which I'd like to share with you. I guess some of these will not be new to experienced extGWT developers, but would still be useful at least to newbies.
So here we go:
1) Expanding a TreePanel - sometimes when you define a TreePanel you will want to have it expanded, rather than collapsed. However, calling the expandAll() method of TreePanel did not seem to work. It seems that you need to call this method only after attaching the LayoutContainer holding the TreePanel to the RootPanel.
2) FormLayout - extGWT has a Panel called FormPanel which uses a layout called FormLayout. This allows alignment of fields in a form. You also don't need to provide labels for the fields but rather set them as part of the field initialization by calling setFieldLabel() on the field. However that does not work unless your panel's layout is FormLayout.
 3) Using FieldSets - if you want to groups form fields together you should use FieldSets. One important thing to remember about fieldsSets is that if you want to control the length of the field or its labels you should do it by invoking the appropriate methods on the FormLayout applied to the FieldSet:
setDefaultWidth - to set the width of the fields in the FieldSet.
setLabelWidth - to set the width of field labels in the FieldSet.
This, as opposed to calling the appropriate method on the FormPanel in case you are not using FieldSets
4) Complex Forms - a FormPanel layouts the fields in a vertical order by default. However, for the application I had to group the fields into several groups and lay them out in a tabular form. See the following screenshot:


To layout this form I used four FieldSets, each with a FormLayout and put them in a FormPanel with a TableLayout.
 5) IE compatibility - Both GWT and extGWT are not compatible with IE9 officially. In order to get your application to work with IE9, use IE8 compatibility mode by adding the following line to you html file:
<meta http-equiv="X-UA-Compatible" content="IE=8" />
6) DatePicker in IE - There seems to be an annoying problem with the DatePicker disappearing in IE before you can make a selection. This problem does not occur consistently, but seems to appear when you use a DateField in something other than a FormLayout. The work around for this is revert to IE7 compatibility although it may present other problems. (see next item).
So again, add this line to your html file:
<meta http-equiv="X-UA-Compatible" content="IE=7" />
7) Disappearing RadioGroup in IE7 - When using IE7 compatibility the DatePicker problem is resolved, but another one soon surfaces: the RadioGroup is disappearing.  The workaround here is to add the individual radio buttons to the container rather than adding the RadioGroup, the radio buttons will still need to be in a group to ensure that only one radio button is selected at a time.
 Hope you find this useful.

My Presentation about designing JEE applications structure

This is a presentation that I gave recently on how to design the JEE applications structure. This is a topic that is often overlooked and misused.
The lecture discussed JEE modules and JEE class loaders briefly. We then discuss how to structure a JEE application consisting of Web modules EJB modules utility Java projects and 3rd party jars. Where should we put the 3rd party jars ? How should we package the Java Modules ? Module visibility between the modules.
The last topic in the talk is about managing the module dependencies in JEE applications. Often dependencies are not designed well or designed based on organizational structure constraints rather then architectural constraints. We discuss the various options we have to deal with dependencies and some tips on how to design dependencies well.
Here is the link to my presentation in slide share:
http://www.slideshare.net/odedns/designing-jee-application-structure