JPQL fetch join fetch query taking too long

Question

I am using JPQL queries for my rest application. spring boot 2.5 and mysql 5.7

I have one table that has 4 onetomany and I need to fetch all the relationships at one go for the findAll query.

In order to achieve that, I am using JPQL query with join fetch. It is working fine however join fetch is taking too long to execute, without all logs off its taking 2+ seconds and with logs it is taking 10 seconds, which is not good as if I remove the fetch and let hibernate fire n+1 queries it fetches all the records in less time. I have to use paginations as well.

Looking for some proper solution with JPQL query itself.

Here are the configurations and settings:

jpa:
    open-in-view: false
    properties:
      hibernate.jdbc.time_zone: UTC
#      hibernate.id.new_generator_mappings: true
      hibernate.connection.provider_disables_autocommit: true #https://vladmihalcea.com/why-you-should-always-use-hibernate-connection-provider_disables_autocommit-for-resource-local-jpa-transactions/
      hibernate.cache.use_second_level_cache: true
      hibernate.cache.region.factory_class: org.hibernate.cache.jcache.internal.JCacheRegionFactory
      hibernate.javax.cache.provider: org.ehcache.jsr107.EhcacheCachingProvider
      hibernate.cache.use_query_cache: true
      hibernate.javax.cache.missing_cache_strategy: create
      hibernate.connection.autocommit: true
      # modify batch size as necessary
      hibernate.jdbc.batch_size: 20
      hibernate.default_batch_fetch_size: 20
      hibernate.order_inserts: true
      hibernate.order_updates: true
      hibernate.batch_versioned_data: true
      hibernate.query.fail_on_pagination_over_collection_fetch: false
      hibernate.query.in_clause_parameter_padding: true
      hibernate.dialect: org.hibernate.dialect.MySQL5InnoDBDialect
      javax.persistent.sharedCache.mode: ENABLE_SELECTIVE

Entity:

package com.pitstop.catalogue.domain;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.pitstop.catalogue.util.EligibleOffer;
import org.hibernate.annotations.BatchSize;
import org.hibernate.annotations.CacheConcurrencyStrategy;

import javax.persistence.*;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import java.io.Serializable;
import java.time.Instant;
import java.util.*;

    /**
     * A Product.
     */
    @Entity
    @Table(name = "product")
    @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    public class Product implements Serializable {
    
        private static final long serialVersionUID = 1L;
    
        public static final String PRODUCT_NAME = "name";
        public static final String PRODUCT_UNIT_PRICE = "unit_price";
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
        @NotNull
        @Size(max = 250)
        @Column(name = PRODUCT_NAME, length = 50, nullable = false)
        private String name;
    
        @Size(max = 1000)
        @Column(name = "description", length = 200)
        private String description;
    
        private Long brandId;
    
        @Size(max = 500)
        @Column(name = "product_icon_url", length = 500)
        private String productIconUrl;
    
        @Column(name = PRODUCT_UNIT_PRICE)
        private Double unitPrice;
    
        @Column(name = "gst_rate")
        private Double gstRate;
    
        @Size(max = 45)
        @Column(name = "part_no", length = 45)
        private String partNo;
    
        @Size(max = 45)
        @Column(name = "serial_no", length = 45)
        private String serialNo;
    
        @Size(max = 45)
        @Column(name = "unit_type", length = 45)
        private String unitType;
    
        @Column(name = "line_item_id")
        private Long lineItemId;
    
        private Long categoryId;
    
        @OneToMany(mappedBy = "product", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
        @JsonIgnoreProperties(value = {"product"}, allowSetters = true)
        @BatchSize(size = 20)
        @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
        private Set<Offer> offers = new HashSet<>();
    
        @OneToMany(mappedBy = "product", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
        @JsonIgnoreProperties(value = {"product"}, allowSetters = true)
        @BatchSize(size = 20)
        @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
        private Set<ProductAttributes> productAttributes = new HashSet<>();
    
        @OneToMany(mappedBy = "product", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
        @JsonIgnoreProperties(value = {"product", "productModel"}, allowSetters = true)
        @BatchSize(size = 20)
        @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
        private Set<ProductModelMapping> productModelMappings = new HashSet<>();
    
        @OneToMany(mappedBy = "product", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
        @JsonIgnoreProperties(value = {"product"}, allowSetters = true)
        @BatchSize(size = 20)
        @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
        private Set<ProductSeller> productSellers = new HashSet<>();
    
        @Size(max = 45)
        @Column(name = "tag", length = 50)
        private String tag;
    
        @Column(name = "created")
        private Instant created;
    
        @Column(name = "created_by", length = 100)
        private String createdBy;
    
        private Integer live;
    
        public Integer getLive() {
            return live;
        }
    
        public void setLive(Integer live) {
            this.live = live;
        }
    
        public Long getId() {
            return id;
        }
    
        public void setId(Long id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getDescription() {
            return description;
        }
    
        public void setDescription(String description) {
            this.description = description;
        }
    
        public String getProductIconUrl() {
            return productIconUrl;
        }
    
        public void setProductIconUrl(String productIconUrl) {
            this.productIconUrl = productIconUrl;
        }
    
        public Double getUnitPrice() {
            return unitPrice;
        }
    
        public void setUnitPrice(Double unitPrice) {
            this.unitPrice = unitPrice;
        }
    
        public Double getGstRate() {
            return gstRate;
        }
    
        public void setGstRate(Double gstRate) {
            this.gstRate = gstRate;
        }
    
        public String getPartNo() {
            return partNo;
        }
    
        public void setPartNo(String partNo) {
            this.partNo = partNo;
        }
    
        public String getSerialNo() {
            return serialNo;
        }
    
        public void setSerialNo(String serialNo) {
            this.serialNo = serialNo;
        }
    
        public String getUnitType() {
            return unitType;
        }
    
        public void setUnitType(String unitType) {
            this.unitType = unitType;
        }
    
        public Set<Offer> getOffers() {
            return offers;
        }
    
        public void setOffers(Set<Offer> offers) {
            this.offers = offers;
        }
    
        public Set<ProductAttributes> getProductAttributes() {
            return productAttributes;
        }
    
        public void setProductAttributes(Set<ProductAttributes> productAttributes) {
            this.productAttributes = productAttributes;
        }
    
        public Set<ProductModelMapping> getProductModelMappings() {
            return productModelMappings;
        }
    
        public void setProductModelMappings(Set<ProductModelMapping> productModelMappings) {
            this.productModelMappings = productModelMappings;
        }
    
        public Set<ProductSeller> getProductSellers() {
            return productSellers;
        }
    
        public void setProductSellers(Set<ProductSeller> productSellers) {
            this.productSellers = productSellers;
        }
    
        public Long getLineItemId() {
            return lineItemId;
        }
    
        public void setLineItemId(Long lineItemId) {
            this.lineItemId = lineItemId;
        }
    
        @Transient
        public double getSellingPrice() {
            // As discussed with Rohit, iterate over all the valid offers (by date)
            // and then apply which has the highest discount value. This is for Pitstop offers only.
            // Sellers offers will be created by sellers.
            if (this.offers.size() > 0) {
                Double sellingPriceFromOffers = EligibleOffer.apply(this.offers, this.unitPrice);
                return sellingPriceFromOffers;
            }
            return this.unitPrice;
        }
    
        @Transient
        public double getProductUnitPrice() {
            return this.unitPrice;
        }
    
        @Transient
        public double getProductGstRate() {
            if (callSellers() != null && callSellers().size() > 0) {
                ProductSeller productSeller = callSellers().get(0);
                return productSeller.getGstRate();
            }
            return gstRate;
        }
    
        @Transient
        public List<ProductSeller> callSellers() {
            //sorting products by seller price in ascending order
            List<ProductSeller> sellers = new ArrayList<>();
            sellers.addAll(productSellers);
            Collections.sort(sellers, (Comparator.comparing(ProductSeller::getUnitPrice)));
            return sellers;
        }
    
        public String getTag() {
            return tag;
        }
    
        public void setTag(String tag) {
            this.tag = tag;
        }
    
        public Instant getCreated() {
            return created;
        }
    
        public void setCreated(Instant created) {
            this.created = created;
        }
    
        public String getCreatedBy() {
            return createdBy;
        }
    
        public void setCreatedBy(String createdBy) {
            this.createdBy = createdBy;
        }
    
        public Long getBrandId() {
            return brandId;
        }
    
        public void setBrandId(Long brandId) {
            this.brandId = brandId;
        }
    
        public Long getCategoryId() {
            return categoryId;
        }
    
        public void setCategoryId(Long categoryId) {
            this.categoryId = categoryId;
        }
    
        @Override
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            Product product = (Product) o;
            return id.equals(product.id) && brandId.equals(product.brandId) && categoryId.equals(product.categoryId);
        }
    
        @Override
        public int hashCode() {
            return Objects.hash(id, brandId, categoryId);
        }
    
        @Override
        public String toString() {
            return "Product{" +
                    "id=" + id +
                    ", brandId=" + brandId +
                    ", categoryId=" + categoryId +
                    '}';
        }
    }

Repository:

 @Query(value = "select p " +
            "from Product p " +
            "left join fetch p.productAttributes " +
            "left join fetch p.productModelMappings " +
            "left join fetch p.productSellers " +
            "left join fetch p.offers where p.live = 1",
            countQuery = "select count(p.id) from Product p where p.live = 1")
    Page<Product> findAll(Pageable pageable);

Here are the queries being fired:

select product0_.id as id1_5_0_, productatt1_.id as id1_6_1_, productmod2_.id as id1_9_2_, productsel3_.id as id1_10_3_, offers4_.id as id1_4_4_, product0_.brand_id as brand_id2_5_0_, product0_.category_id as category3_5_0_, product0_.created as created4_5_0_, product0_.created_by as created_5_5_0_, product0_.description as descript6_5_0_, product0_.gst_rate as gst_rate7_5_0_, product0_.line_item_id as line_ite8_5_0_, product0_.live as live9_5_0_, product0_.name as name10_5_0_, product0_.part_no as part_no11_5_0_, product0_.product_icon_url as product12_5_0_, product0_.serial_no as serial_13_5_0_, product0_.tag as tag14_5_0_, product0_.unit_price as unit_pr15_5_0_, product0_.unit_type as unit_ty16_5_0_, productatt1_.live as live2_6_1_, productatt1_.name as name3_6_1_, productatt1_.product_id as product_5_6_1_, productatt1_.value as value4_6_1_, productatt1_.product_id as product_5_6_0__, productatt1_.id as id1_6_0__, productmod2_.fuel_type as fuel_typ2_9_2_, productmod2_.live as live3_9_2_, productmod2_.name as name4_9_2_, productmod2_.product_id as product_5_9_2_, productmod2_.product_model_id as product_6_9_2_, productmod2_.product_id as product_5_9_1__, productmod2_.id as id1_9_1__, productsel3_.created as created2_10_3_, productsel3_.created_by as created_3_10_3_, productsel3_.description as descript4_10_3_, productsel3_.gst_rate as gst_rate5_10_3_, productsel3_.last_modified as last_mod6_10_3_, productsel3_.live as live7_10_3_, productsel3_.modified_by as modified8_10_3_, productsel3_.name as name9_10_3_, productsel3_.product_id as product13_10_3_, productsel3_.seller_id as seller_10_10_3_, productsel3_.seller_name as seller_11_10_3_, productsel3_.price as price12_10_3_, productsel3_.product_id as product13_10_2__, productsel3_.id as id1_10_2__, offers4_.brand_id as brand_id2_4_4_, offers4_.category_id as category3_4_4_, offers4_.created as created4_4_4_, offers4_.discount as discount5_4_4_, offers4_.end_date as end_date6_4_4_, offers4_.live as live7_4_4_, offers4_.name as name8_4_4_, offers4_.offer_type as offer_ty9_4_4_, offers4_.product_id as product11_4_4_, offers4_.start_date as start_d10_4_4_, offers4_.product_id as product11_4_3__, offers4_.id as id1_4_3__ from product product0_ left outer join product_attributes productatt1_ on product0_.id=productatt1_.product_id left outer join product_model_mapping productmod2_ on product0_.id=productmod2_.product_id left outer join product_seller productsel3_ on product0_.id=productsel3_.product_id left outer join offer offers4_ on product0_.id=offers4_.product_id where product0_.live=1

select count(product0_.id) as col_0_0_ from product product0_ where product0_.live=1

select productmod0_.id as id1_8_0_, productmod0_.live as live2_8_0_, productmod0_.name as name3_8_0_, productmod0_.product_make_id as product_4_8_0_ from product_model productmod0_ where productmod0_.id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

select productmod0_.id as id1_8_0_, productmod0_.live as live2_8_0_, productmod0_.name as name3_8_0_, productmod0_.product_make_id as product_4_8_0_ from product_model productmod0_ where productmod0_.id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Answer 1

You can try Entity Graph to improve performance and avoid n+1 problem at the same time.

1. Create an entity Graph on your entity class

    @NamedEntityGraph(name = Product.GRAPH_ALL_ATTRIBUTES),
            attributeNodes = {
                    @NamedAttributeNode(value = "productAttributes"),
                    @NamedAttributeNode(value = "productModelMappings"),
                    @NamedAttributeNode(value = "productSellers"),
                    @NamedAttributeNode(value = "offers")
    })
    @Entity
    @Table(name = "product")
    @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    public class Product implements Serializable {  
        private static final long serialVersionUID = 1L;
   
        protected static final String GRAPH_ALL_ATTRIBUTES = "product.graph.all";
        public static final String PRODUCT_NAME = "name";
        public static final String PRODUCT_UNIT_PRICE = "unit_price";
   
        ...
    }
   

2. Create an @EntityGraph annotated query method in your repository interface.

   @EntityGraph(Product.GRAPH_ALL_ATTRIBUTES)
   Page<Product> findByLive(Integer live, Pageable pageable);