[FIXED] CriteriaBuilder slows down during ~15000 executions

Issue

Spring 5.3 with Hibernate 5.6:

I am experiencing some performance issues after rewrite/migration from Hibernate Criteria API to JPA CriteriaBuilder for a findByCriteria method that runs ~15k times during startup of my app. Normally, this would take maybe one minute using Criteria API but when I have the JPA code run, all is good until ~9k iterations (which takes about 10 seconds). But starting with ~10k, the execution rate drops to circa 80 queries per second.

Any idea why that is? My code looks like this:

public List<ENTITY> findByCriteria(final Map<String, Object> criteriaMap, final List<String> fields, final Class<ENTITY> entityClass) {
    Session session = getSessionFactory().getCurrentSession()
    CriteriaBuilder cb = session.getCriteriaBuilder();
    CriteriaQuery cr = cb.createQuery(aliasToBeanClass);
    Root root = cr.from(aliasToBeanClass);
    List<Predicate> predicates = new ArrayList<>();


    // Take info from criteriaMap and create predicates ... some if/else or switch logic
    // Take info from fields and create projections
    
    cr.where(cb.and(predicates.toArray(new Predicate[0])));
    Query query = session.createQuery(cr);
    List<ENTITY> results = query.getResultList();

    return results;
}

The statements that are generated using the predicates look like that:

select this_.rowguid as rowguid1_72_0_, this_.tt_path as tt_path2_72_0_, this_.tt_denotation as tt_denot3_72_0_, this_.tt_description as tt_descr4_72_0_, this_.tt_reference as tt_refer5_72_0_, this_.tt_owner as tt_owner6_72_0_, this_.tt_creation_date as tt_creat7_72_0_, this_.tt_creation_user as tt_creat8_72_0_, this_.tt_modification_date as tt_modif9_72_0_, this_.tt_modification_user as tt_modi10_72_0_ from txt_t_text this_ where this_.tt_path=$1 and this_.tt_denotation=$2 and (this_.tt_owner=$3 or this_.tt_owner=$4)

Solution

JPA uses a cache for managed entities. You may see it referenced as "1st level cache" and is connected to the current persistence context, i.e. the EntityManager. JPA implementations, like Hibernate, should have similar behaviors in their corresponding components, e.g. Hibernate's Session.

Every time you read an instance of an entity from the DB, the EntityManager stores the entity object in the 1st level cache. Operations like em.find(entityClass, primaryKey), em.createQuery(...)...getResultList() etc load entities in the cache. The cache is destroyed when the persistence context is destroyed. Usually you get one persistence context per request, and there is the extended persistence context that may live longer.

At some point this cache will eventually start filling up, causing problems to the running application. It is advised to clear the cache with EntityManager.clear() (or Hibernate's corresponding Session.clear() - thanks @XtremeBaumer) when a workflow reads many entities and doesn't need them anymore. When running a batch workflow, clear the cache every N iterations (experiment for N, could be tens, hundreds or even thousands). Beware that, as per the Javadocs:

void clear()

Clear the persistence context, causing all managed entities to become detached. Changes made to entities that have not been flushed to the database will not be persisted.

Answered By - Nikos Paraskevopoulos
Answer Checked By - Mildred Charles (JavaFixing Admin)