• Post Reply Bookmark Topic Watch Topic
  • New Topic

How to resolve the N+1 using oneToOne

ray frid
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I assume I am having the N+1 select problem.

I got this entity:

This is the other entity:

Now when I am starting to iterate over this loop:

Set<Device> customerDevices = user.getCustomer().getDevices();
for (Device device : customerDevices) {

I am stuck and I see in the log that Hibernate selects:

Hibernate: */ select warrantyen0_.id as...
Hibernate: /* load myclass.Device */

Hibernate: */ select warrantyen0_.id as ..
Hibernate: /* load myclass.Device */...

Over and over I guess I am having the n+1 select problem.

To try fix this I tried to use fetching this way:

But now the console still show this:

thats the exact selects hibernate performs:

/* load one-to-many cellebrite.activation.data.classes.AbstractCustomer.devices */ select
devices0_.owningCompany_customerRefId as owningC15_0_1_,
devices0_.serial as serial1_,
devices0_.serial as serial9_0_,
devices0_.blackListed as blackLis2_9_0_,
devices0_.Creation_id as Creation12_9_0_,
devices0_.deactivated as deactiva3_9_0_,
devices0_.deviceComment as deviceCo4_9_0_,
devices0_.deviceName as deviceName9_0_,
devices0_.deviceType as deviceType9_0_,
devices0_.distributor_customerRefId as distrib13_9_0_,
devices0_.endCustomer_customerRefId as endCust14_9_0_,
devices0_.owningCompany_customerRefId as owningC15_9_0_,
devices0_.paChallenge as paChalle7_9_0_,
devices0_.parent_serial as parent16_9_0_,
devices0_.pendingDeactivation as pendingD8_9_0_,
devices0_.safetyStock as safetySt9_9_0_,
devices0_.serialSalt as serialSalt9_0_,
devices0_.signedBlackBerry as signedB11_9_0_,
devices0_.tradeInOldDevice as tradeIn17_9_0_
Devices devices0_
/* load cellebrite.activation.data.classes.Device */ select
device0_.serial as serial9_0_,
device0_.blackListed as blackLis2_9_0_,
device0_.Creation_id as Creation12_9_0_,
device0_.deactivated as deactiva3_9_0_,
device0_.deviceComment as deviceCo4_9_0_,
device0_.deviceName as deviceName9_0_,
device0_.deviceType as deviceType9_0_,
device0_.distributor_customerRefId as distrib13_9_0_,
device0_.endCustomer_customerRefId as endCust14_9_0_,
device0_.owningCompany_customerRefId as owningC15_9_0_,
device0_.paChallenge as paChalle7_9_0_,
device0_.parent_serial as parent16_9_0_,
device0_.pendingDeactivation as pendingD8_9_0_,
device0_.safetyStock as safetySt9_9_0_,
device0_.serialSalt as serialSalt9_0_,
device0_.signedBlackBerry as signedB11_9_0_,
device0_.tradeInOldDevice as tradeIn17_9_0_
Devices device0_
/* load cellebrite.activation.data.classes.WarrantyEntry */ select
warrantyen0_.id as id34_2_,
warrantyen0_.createdTime as createdT2_34_2_,
warrantyen0_.deleted as deleted34_2_,
warrantyen0_.expiryDate as expiryDate34_2_,
warrantyen0_.serial as serial34_2_,
warrantyen0_.updateTime as updateTime34_2_,
warrantyen0_.updateUser as updateUser34_2_,
device1_.serial as serial9_0_,
device1_.blackListed as blackLis2_9_0_,
device1_.Creation_id as Creation12_9_0_,
device1_.deactivated as deactiva3_9_0_,
device1_.deviceComment as deviceCo4_9_0_,
device1_.deviceName as deviceName9_0_,
device1_.deviceType as deviceType9_0_,
device1_.distributor_customerRefId as distrib13_9_0_,
device1_.endCustomer_customerRefId as endCust14_9_0_,
device1_.owningCompany_customerRefId as owningC15_9_0_,
device1_.paChallenge as paChalle7_9_0_,
device1_.parent_serial as parent16_9_0_,
device1_.pendingDeactivation as pendingD8_9_0_,
device1_.safetyStock as safetySt9_9_0_,
device1_.serialSalt as serialSalt9_0_,
device1_.signedBlackBerry as signedB11_9_0_,
device1_.tradeInOldDevice as tradeIn17_9_0_,
management2_.id as id22_1_,
management2_1_.deleted as deleted22_1_,
management2_1_.firstName as firstName22_1_,
management2_1_.lastLogin as lastLogin22_1_,
management2_1_.lastName as lastName22_1_,
management2_1_.password as password22_1_,
management2_1_.primaryEmail as primaryE7_22_1_,
management2_1_.userName as userName22_1_,
management2_.authority as authority23_1_,
management2_.isViewer as isViewer23_1_,
management2_3_.distributor as distribu1_25_1_,
management2_4_.umeKeysQuota as umeKeysQ1_27_1_,
when management2_2_.id is not null then 2
when management2_3_.id is not null then 3
when management2_4_.id is not null then 5
when management2_5_.id is not null then 6
when management2_6_.id is not null then 7
when management2_.id is not null then 1
end as clazz_1_,
cids3_.Users_id as Users1_22_4_,
cids3_.element as element4_,
emails4_.Users_id as Users1_22_5_,
emails4_.element as element5_,
roles5_.Users_Management_id as Users1_22_6_,
roles5_.element as element6_
Warranty warrantyen0_
left outer join
Devices device1_
on warrantyen0_.serial=device1_.serial
left outer join
Users_Management management2_
on warrantyen0_.updateUser=management2_.id
left outer join
Users management2_1_
on management2_.id=management2_1_.id
left outer join
Users_Management_Administrators management2_2_
on management2_.id=management2_2_.id
left outer join
Users_Management_Distributors management2_3_
on management2_.id=management2_3_.id
left outer join
Users_Management_Limited management2_4_
on management2_.id=management2_4_.id
left outer join
Users_Management_Managers management2_5_
on management2_.id=management2_5_.id
left outer join
Users_Management_Workers management2_6_
on management2_.id=management2_6_.id
left outer join
Users_CID cids3_
on management2_.id=cids3_.Users_id
left outer join
Users_Emails emails4_
on management2_.id=emails4_.Users_id
left outer join
Users_Management_roles roles5_
on management2_.id=roles5_.Users_Management_id
Any idea why It still didnt fetch right?


ray frid
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No1 got any clue?
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!