JOIN FETCH performance issue query

Question

I have a sleep query performance issue that I cannot understand. In the code snippet below, I need to select objects with at least one mapping and filter. I use FETCH JOIN to load only filtered mappings. But in this case, I have performance issues with the query. Hibernate reports a warning log:

org.hibernate.hql.ast.QueryTranslatorImpl - firstResult / maxResults set using the fetch collection; application in memory!

When I omit the FETCH JOIN and leave only the JOIN request, it is fast. But as a result, I have all the mappings loaded into the entity, which is unacceptable to me. Is there a way to improve query performance? There are many rows in the mapping table.

HQL query:

select distinct e from Entity 
   join fetch e.mappings as mapping 
where e.deleted = 0 and e.mappings is not empty 
   and e = mapping.e and mapping.approval in (:approvals)

Entities:

@Entity
@Table(name="entity")
class Entity {

   ...

   @OneToMany(mappedBy="entity", cascade=CascadeType.REMOVE, fetch=FetchType.LAZY)
   @OrderBy("created")
   private List<Mapping> mappings = new ArrayList<Mapping>();

   ...
}

@Entity
@Table(name="mapping")
class Mapping {

public static enum MappingApproval {
    WAITING, // mapping is waiting for approval
    APPROVED, // mapping was approved
    DECLINED; // mapping was declined
}

...

    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name="entity_id", nullable=false)
    private Entity entity;

    @Enumerated(EnumType.STRING)
    @Column(name="approval", length=20)
    private MappingApproval approval;

...

}

thank

Answer 1

after increasing the memory for the JVM, the situation is much better. In the end, I end up not using FETCH in requests.

Answer 2

From JPA specifications

The effect of applying setMaxResults or setFirstResult to a merge request related to assemblies is undefined. (JPA "JavaBeans Enterprise 3.0, Final Release", Kapitel 3.6.1 Request Interface)

Hibernate does the right thing, but does part of the request in memory, which is much slower. In my case, the difference is from 3-5 ms to 400-500 ms.

My solution was to swap in the request itself. Works fast with JOIN FETCH.

Answer 3

firstResult/maxResults "fetch", 2 :

• firstResult/maxResults, "" :

  select entity.id from entity (without fetch) where .... (with firstResult/maxResults)
  

• "" , :

  select entity from entity fetch ... where id in <previous ids>
  

Answer 4

, Hibernate SQL- , .

100 . , 100 , 99,9% , Extractor, -, , - .

, JPQL-, JOIN FETCH, :

List<Post> posts = entityManager.createQuery(
    "select p " +
    "from Post p " +
    "left join fetch p.comments " +
    "where p.title like :title " +
    "order by p.id", Post.class)
.setParameter("title", titlePattern)
.setMaxResults(maxResults)
.getResultList();

into an SQL query that restricts the result using DENSE_RANK by parent id:

@NamedNativeQuery(
    name = "PostWithCommentByRank",
    query =
        "SELECT * " +
        "FROM (   " +
        "    SELECT *, dense_rank() OVER (ORDER BY \"p.created_on\", \"p.id\") rank " +
        "    FROM (   " +
        "        SELECT p.id AS \"p.id\", " +
        "               p.created_on AS \"p.created_on\", " +
        "               p.title AS \"p.title\", " +
        "               pc.id as \"pc.id\", " +
        "               pc.created_on AS \"pc.created_on\", " +
        "               pc.review AS \"pc.review\", " +
        "               pc.post_id AS \"pc.post_id\" " +
        "        FROM post p  " +
        "        LEFT JOIN post_comment pc ON p.id = pc.post_id " +
        "        WHERE p.title LIKE :titlePattern " +
        "        ORDER BY p.created_on " +
        "    ) p_pc " +
        ") p_pc_r " +
        "WHERE p_pc_r.rank <= :rank ",
    resultSetMapping = "PostWithCommentByRankMapping"
)
@SqlResultSetMapping(
    name = "PostWithCommentByRankMapping",
    entities = {
        @EntityResult(
            entityClass = Post.class,
            fields = {
                @FieldResult(name = "id", column = "p.id"),
                @FieldResult(name = "createdOn", column = "p.created_on"),
                @FieldResult(name = "title", column = "p.title"),
            }
        ),
        @EntityResult(
            entityClass = PostComment.class,
            fields = {
                @FieldResult(name = "id", column = "pc.id"),
                @FieldResult(name = "createdOn", column = "pc.created_on"),
                @FieldResult(name = "review", column = "pc.review"),
                @FieldResult(name = "post", column = "pc.post_id"),
            }
        )
    }
)

The request can be made as follows:

List<Post> posts = entityManager
.createNamedQuery("PostWithCommentByRank")
.setParameter(
    "titlePattern",
    "High-Performance Java Persistence %"
)
.setParameter(
    "rank",
    5
)
.unwrap(NativeQuery.class)
.setResultTransformer(
    new DistinctPostResultTransformer(entityManager)
)
.getResultList();

To convert a tabular result set back to an entity graph, you need one ResultTransformerthat looks like this:

public class DistinctPostResultTransformer
        extends BasicTransformerAdapter {

    private final EntityManager entityManager;

    public DistinctPostResultTransformer(
            EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Override
    public List transformList(
            List list) {

        Map<Serializable, Identifiable> identifiableMap =
            new LinkedHashMap<>(list.size());

        for (Object entityArray : list) {
            if (Object[].class.isAssignableFrom(entityArray.getClass())) {
                Post post = null;
                PostComment comment = null;

                Object[] tuples = (Object[]) entityArray;

                for (Object tuple : tuples) {
                    if(tuple instanceof Identifiable) {
                        entityManager.detach(tuple);

                        if (tuple instanceof Post) {
                            post = (Post) tuple;
                        }
                        else if (tuple instanceof PostComment) {
                            comment = (PostComment) tuple;
                        }
                        else {
                            throw new UnsupportedOperationException(
                                "Tuple " + tuple.getClass() + " is not supported!"
                            );
                        }
                    }
                }

                if (post != null) {
                    if (!identifiableMap.containsKey(post.getId())) {
                        identifiableMap.put(post.getId(), post);
                        post.setComments(new ArrayList<>());
                    }
                    if (comment != null) {
                        post.addComment(comment);
                    }
                }
            }
        }
        return new ArrayList<>(identifiableMap.values());
    }
}

It!