Criteria api and pagination

Dear hibernate community,

I have a criteria api query which runs slow (few seconds to fetch results) although I am using pagination to make the query faster.

This is my query:

CriteriaBuilder builder = em.getCriteriaBuilder();

CriteriaQuery<Ereturn> criteria = builder.createQuery( Ereturn.class );

Root<Ereturn> er = criteria.from(Ereturn.class);
Fetch<Ereturn, User> shipperFetch = er.fetch("shipper");
Fetch<Ereturn, User> carrierFetch = er.fetch("carrier");
Fetch<Ereturn, User> consigneeFetch = er.fetch("consignee");
Fetch<Ereturn, User> destinationFetch = er.fetch("destination");
Fetch<Ereturn, ProductItem> productItemFetch = er.fetch("productItems", JoinType.LEFT);
Fetch<ProductItem, Receptacle> receptacleFetch = productItemFetch.fetch("receptacle", JoinType.LEFT);
Fetch<ProductItem, ProductDefinition> productDefinitionFetch = productItemFetch.fetch("product", JoinType.LEFT);

criteria.select( er );

if (disabled == null) {
	disabled = false;
}

List<Predicate> predicates = new ArrayList<>();

predicates.add(builder.equal(er.get( "disabled" ), disabled));

if (filter.getGlobalId() != null) {
	predicates.add(builder.equal(er.get( "globalId" ), filter.getGlobalId()));
}
		
if (filter.getShipperId() != null) {
	predicates.add(builder.equal(er.get( "shipper" ), filter.getShipperId()));
}

criteria.where(
	builder.and(predicates.toArray(new Predicate[predicates.size()]))
);

List<Ereturn> ers = em.createQuery( criteria )
	.setFirstResult(first)
	.setMaxResults(window)
	.getResultList();

And this is the query generated:

   select
        ereturn0_.id as id1_1_0_,
        user1_.id as id1_9_1_,
        user2_.id as id1_9_2_,
        user3_.id as id1_9_3_,
        user4_.id as id1_9_4_,
        productite5_.id as id1_7_5_,
        receptacle6_.id as id1_8_6_,
        productdef7_.id as id1_6_7_,
        ereturn0_.barcode as barcode2_1_0_,
        ereturn0_.carrier as carrier27_1_0_,
        ereturn0_.consignee as consign28_1_0_,
        ereturn0_.consigneeFirstName as consigne3_1_0_,
        ereturn0_.consigneeLastName as consigne4_1_0_,
        ereturn0_.creationtime as creation5_1_0_,
        ereturn0_.destination as destina29_1_0_,
        ereturn0_.disabled as disabled6_1_0_,
        ereturn0_.dispatchedDate as dispatch7_1_0_,
        ereturn0_.failedReturnPOBoxPrivateBag as failedRe8_1_0_,
        ereturn0_.globalCondition as globalCo9_1_0_,
        ereturn0_.globalId as globalI10_1_0_,
        ereturn0_.groupName as groupNa11_1_0_,
        ereturn0_.invoice as invoice12_1_0_,
        ereturn0_.notes as notes13_1_0_,
        ereturn0_.pickupDateTime as pickupD14_1_0_,
        ereturn0_.pickupDateTimeOffset as pickupD15_1_0_,
        ereturn0_.pieces as pieces16_1_0_,
        ereturn0_.processedByShipper as process17_1_0_,
        ereturn0_.reasonToReturn as reasonT18_1_0_,
        ereturn0_.returnAction as returnA19_1_0_,
        ereturn0_.returnMethod as returnM20_1_0_,
        ereturn0_.returned as returne21_1_0_,
        ereturn0_.rma as rma22_1_0_,
        ereturn0_.scanDateTime as scanDat23_1_0_,
        ereturn0_.shipper as shipper30_1_0_,
        ereturn0_.status as status24_1_0_,
        ereturn0_.trackingNumber as trackin25_1_0_,
        ereturn0_.weight as weight26_1_0_,
        user1_.accName as accName2_9_1_,
        user1_.accNum as accNum3_9_1_,
        user1_.address as address4_9_1_,
        user1_.address2 as address5_9_1_,
        user1_.addressId as addressI6_9_1_,
        user1_.addressId2 as addressI7_9_1_,
        user1_.bankName as bankName8_9_1_,
        user1_.branchName as branchNa9_9_1_,
        user1_.bsb as bsb10_9_1_,
        user1_.carrierCustomerInternalId as carrier11_9_1_,
        user1_.city as city12_9_1_,
        user1_.city2 as city13_9_1_,
        user1_.contactFirstName as contact14_9_1_,
        user1_.contactLastName as contact15_9_1_,
        user1_.contactNumber as contact16_9_1_,
        user1_.contactNumber2 as contact17_9_1_,
        user1_.country as country18_9_1_,
        user1_.country2 as country19_9_1_,
        user1_.disabled as disable20_9_1_,
        user1_.email as email21_9_1_,
        user1_.firstName as firstNa22_9_1_,
        user1_.importEnabled as importE23_9_1_,
        user1_.labelApiUrl as labelAp24_9_1_,
        user1_.lastName as lastNam25_9_1_,
        user1_.manager as manager39_9_1_,
        user1_.notes as notes26_9_1_,
        user1_.password as passwor27_9_1_,
        user1_.pickupApiUrl as pickupA28_9_1_,
        user1_.pobox as pobox29_9_1_,
        user1_.pobox2 as pobox30_9_1_,
        user1_.postalcode as postalc31_9_1_,
        user1_.postalcode2 as postalc32_9_1_,
        user1_.role as role33_9_1_,
        user1_.secondContactNumber as secondC34_9_1_,
        user1_.state as state35_9_1_,
        user1_.state2 as state36_9_1_,
        user1_.type as type37_9_1_,
        user1_.web as web38_9_1_,
        user2_.accName as accName2_9_2_,
        user2_.accNum as accNum3_9_2_,
        user2_.address as address4_9_2_,
        user2_.address2 as address5_9_2_,
        user2_.addressId as addressI6_9_2_,
        user2_.addressId2 as addressI7_9_2_,
        user2_.bankName as bankName8_9_2_,
        user2_.branchName as branchNa9_9_2_,
        user2_.bsb as bsb10_9_2_,
        user2_.carrierCustomerInternalId as carrier11_9_2_,
        user2_.city as city12_9_2_,
        user2_.city2 as city13_9_2_,
        user2_.contactFirstName as contact14_9_2_,
        user2_.contactLastName as contact15_9_2_,
        user2_.contactNumber as contact16_9_2_,
        user2_.contactNumber2 as contact17_9_2_,
        user2_.country as country18_9_2_,
        user2_.country2 as country19_9_2_,
        user2_.disabled as disable20_9_2_,
        user2_.email as email21_9_2_,
        user2_.firstName as firstNa22_9_2_,
        user2_.importEnabled as importE23_9_2_,
        user2_.labelApiUrl as labelAp24_9_2_,
        user2_.lastName as lastNam25_9_2_,
        user2_.manager as manager39_9_2_,
        user2_.notes as notes26_9_2_,
        user2_.password as passwor27_9_2_,
        user2_.pickupApiUrl as pickupA28_9_2_,
        user2_.pobox as pobox29_9_2_,
        user2_.pobox2 as pobox30_9_2_,
        user2_.postalcode as postalc31_9_2_,
        user2_.postalcode2 as postalc32_9_2_,
        user2_.role as role33_9_2_,
        user2_.secondContactNumber as secondC34_9_2_,
        user2_.state as state35_9_2_,
        user2_.state2 as state36_9_2_,
        user2_.type as type37_9_2_,
        user2_.web as web38_9_2_,
        user3_.accName as accName2_9_3_,
        user3_.accNum as accNum3_9_3_,
        user3_.address as address4_9_3_,
        user3_.address2 as address5_9_3_,
        user3_.addressId as addressI6_9_3_,
        user3_.addressId2 as addressI7_9_3_,
        user3_.bankName as bankName8_9_3_,
        user3_.branchName as branchNa9_9_3_,
        user3_.bsb as bsb10_9_3_,
        user3_.carrierCustomerInternalId as carrier11_9_3_,
        user3_.city as city12_9_3_,
        user3_.city2 as city13_9_3_,
        user3_.contactFirstName as contact14_9_3_,
        user3_.contactLastName as contact15_9_3_,
        user3_.contactNumber as contact16_9_3_,
        user3_.contactNumber2 as contact17_9_3_,
        user3_.country as country18_9_3_,
        user3_.country2 as country19_9_3_,
        user3_.disabled as disable20_9_3_,
        user3_.email as email21_9_3_,
        user3_.firstName as firstNa22_9_3_,
        user3_.importEnabled as importE23_9_3_,
        user3_.labelApiUrl as labelAp24_9_3_,
        user3_.lastName as lastNam25_9_3_,
        user3_.manager as manager39_9_3_,
        user3_.notes as notes26_9_3_,
        user3_.password as passwor27_9_3_,
        user3_.pickupApiUrl as pickupA28_9_3_,
        user3_.pobox as pobox29_9_3_,
        user3_.pobox2 as pobox30_9_3_,
        user3_.postalcode as postalc31_9_3_,
        user3_.postalcode2 as postalc32_9_3_,
        user3_.role as role33_9_3_,
        user3_.secondContactNumber as secondC34_9_3_,
        user3_.state as state35_9_3_,
        user3_.state2 as state36_9_3_,
        user3_.type as type37_9_3_,
        user3_.web as web38_9_3_,
        user4_.accName as accName2_9_4_,
        user4_.accNum as accNum3_9_4_,
        user4_.address as address4_9_4_,
        user4_.address2 as address5_9_4_,
        user4_.addressId as addressI6_9_4_,
        user4_.addressId2 as addressI7_9_4_,
        user4_.bankName as bankName8_9_4_,
        user4_.branchName as branchNa9_9_4_,
        user4_.bsb as bsb10_9_4_,
        user4_.carrierCustomerInternalId as carrier11_9_4_,
        user4_.city as city12_9_4_,
        user4_.city2 as city13_9_4_,
        user4_.contactFirstName as contact14_9_4_,
        user4_.contactLastName as contact15_9_4_,
        user4_.contactNumber as contact16_9_4_,
        user4_.contactNumber2 as contact17_9_4_,
        user4_.country as country18_9_4_,
        user4_.country2 as country19_9_4_,
        user4_.disabled as disable20_9_4_,
        user4_.email as email21_9_4_,
        user4_.firstName as firstNa22_9_4_,
        user4_.importEnabled as importE23_9_4_,
        user4_.labelApiUrl as labelAp24_9_4_,
        user4_.lastName as lastNam25_9_4_,
        user4_.manager as manager39_9_4_,
        user4_.notes as notes26_9_4_,
        user4_.password as passwor27_9_4_,
        user4_.pickupApiUrl as pickupA28_9_4_,
        user4_.pobox as pobox29_9_4_,
        user4_.pobox2 as pobox30_9_4_,
        user4_.postalcode as postalc31_9_4_,
        user4_.postalcode2 as postalc32_9_4_,
        user4_.role as role33_9_4_,
        user4_.secondContactNumber as secondC34_9_4_,
        user4_.state as state35_9_4_,
        user4_.state2 as state36_9_4_,
        user4_.type as type37_9_4_,
        user4_.web as web38_9_4_,
        productite5_.dangerousGood as dangerou2_7_5_,
        productite5_.disabled as disabled3_7_5_,
        productite5_.ereturn as ereturn19_7_5_,
        productite5_.expirationDate as expirati4_7_5_,
        productite5_.feedback as feedback5_7_5_,
        productite5_.forward as forward6_7_5_,
        productite5_.location as location7_7_5_,
        productite5_.lotNumber as lotNumbe8_7_5_,
        productite5_.lotReceivedDate as lotRecei9_7_5_,
        productite5_.lotStatus as lotStat10_7_5_,
        productite5_.notes as notes11_7_5_,
        productite5_.price as price12_7_5_,
        productite5_.processed as process13_7_5_,
        productite5_.product as product20_7_5_,
        productite5_.quantity as quantit14_7_5_,
        productite5_.quarantine as quarant15_7_5_,
        productite5_.receptacle as recepta21_7_5_,
        productite5_.reserve as reserve16_7_5_,
        productite5_.returnAction as returnA17_7_5_,
        productite5_.status as status18_7_5_,
        productite5_.ereturn as ereturn19_7_0__,
        productite5_.id as id1_7_0__,
        receptacle6_.closed as closed2_8_6_,
        receptacle6_.creationtime as creation3_8_6_,
        receptacle6_.disabled as disabled4_8_6_,
        receptacle6_.hawb as hawb5_8_6_,
        receptacle6_.height as height6_8_6_,
        receptacle6_.length as length7_8_6_,
        receptacle6_.masterCrossDock as masterC12_8_6_,
        receptacle6_.type as type8_8_6_,
        receptacle6_.volWeight as volWeigh9_8_6_,
        receptacle6_.weight as weight10_8_6_,
        receptacle6_.width as width11_8_6_,
        productdef7_.description as descript2_6_7_,
        productdef7_.disabled as disabled3_6_7_,
        productdef7_.height as height4_6_7_,
        productdef7_.hsCode as hsCode5_6_7_,
        productdef7_.length as length6_6_7_,
        productdef7_.productNumber as productN7_6_7_,
        productdef7_.serialNumber as serialNu8_6_7_,
        productdef7_.shipper as shipper12_6_7_,
        productdef7_.sku as sku9_6_7_,
        productdef7_.weight as weight10_6_7_,
        productdef7_.width as width11_6_7_ 
    from
        ereturn ereturn0_ 
    inner join
        user user1_ 
            on ereturn0_.shipper=user1_.id 
    inner join
        user user2_ 
            on ereturn0_.carrier=user2_.id 
    inner join
        user user3_ 
            on ereturn0_.consignee=user3_.id 
    inner join
        user user4_ 
            on ereturn0_.destination=user4_.id 
    left outer join
        product_item productite5_ 
            on ereturn0_.id=productite5_.ereturn 
    left outer join
        receptacle receptacle6_ 
            on productite5_.receptacle=receptacle6_.id 
    left outer join
        product_definition productdef7_ 
            on productite5_.product=productdef7_.id 
    where
        ereturn0_.disabled=? 
        and ereturn0_.shipper=148375 
        and ereturn0_.carrier=148380 
        and ereturn0_.carrier=148380 
        and ereturn0_.shipper=148375

When I run this query in my mysql console it returns thousands of rows instead of 16 (windows size)

QUESTION: Why my criteria api is ignoring the pagination?

thank you very much

The where clause contains criteria on “carrier” but I don’t see any predicate on this one in the Java code, are you sure the code corresponds to the query?

It’s very simple. When you join fetch, pagination will be applied in memory, not in SQL.

If you want a more efficient solution to this problem, you need to use window functions
.