Performance OpenJPA query (3000+ records) is slow

Question

I'm using Websphere Application Server 7 with buildin OpenJPA 1.2.3 and an Oracle database. I have the following entity:

    @NamedNativeQuery(name=Contract.GIVE_ALL_CONTRACTS, 
        query="SELECT number, name \n" +
          "FROM contracts \n" +
          "WHERE startdate <= ?1 \n" +
          "AND enddate > ?1",
          resultSetMapping = Contract.GIVE_ALL_CONTRACTS_MAPPING)
    @SqlResultSetMapping(name = Contract.GIVE_ALL_CONTRACTS_MAPPING, 
        entities = { @EntityResult(entityClass = Contract.class, fields = {
          @FieldResult(name = "number", column = "number"),
          @FieldResult(name = "name", column = "name")
        })
    })
    @Entity
    public class Contract {
      public static final String GIVE_ALL_CONTRACTS = "Contract.giveAllContracts";
      public static final String GIVE_ALL_CONTRACTS_MAPPING = "Contract.giveAllContractsMapping";

      @Id
      private Integer number;
      private String name;

      public Integer getNumber() {
        return number;
      }
      public String getName() {
        return name;
      }
    }

And the following code to retrieve the contracts:

Query query = entityManager.createNamedQuery(Contract.GIVE_ALL_CONTRACTS);
query.setParameter(1, referenceDate);

List contracts = query.getResultList();
entityManager.clear();

return contracts;

The retrieved contracts are passed to a webservice.

Executing this query in Oracle developer takes around 0,35 seconds for 3608 records. The call to query.getResultList() takes around 4 seconds.

With a logger in the constuctor of the entity, it logs that there are about 10-20 entities created with the same timestamp. Then 0,015 seconds it does something else. I guess OpenJPA stuff.

Is there a way to speed up OpenJPA? Or is the only solution caching?

Answer 1

Object creation may have its fair share in the performance hit. While running your code in the server, you're not only querying the database but also you allocate memory and create a new Contract object for each row. An expanding heap or garbage collection cycle may count for idle periods that you observed.

I'd suggest you skim through OpenJPA documentation on how to process large results sets.