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