• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

(N+1) selects � Hibernate bug?

 
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I tried all possible combinations to prevent (N+1), nothing works.

Our mapping looks like:
--------------------------------
<class �User� lazy=�false�>
<many-to-one name="security" cascade="lock" column="SECURITYID" class="Security" not-null="false" lazy="no-proxy" access="field">
</many-to-one>
<set name="history" lazy="true" cascade="save-update,lock" inverse="true">
<key column="USERID" not-null="true"/>
<one-to-many class="History"/>
</set>
<one-to-one name="profile" class="Profile" cascade="save-update,lock" lazy="no-proxy" access="field"/>
</class>

<class �History� lazy=�false�>
<many-to-one name="user" cascade="lock" column="USERID" class="User" not-null="true" lazy="no-proxy" access="field">
</many-to-one>
</class>
------------------------------------------------

retrieveUser(userid)  retrieves everything in one JOIN, which is fine.
retrieveHistory(historyid) � generating 3-SQLs (one JOIN-fine, 2-extra selects one
on User.Profile & one on User.Security.

How to prevent SELECTs on Profile & Security.
This is really affecting performance. I also tried with other LAZY options on User.Security&Profile, varying fetch_depth, but nothing works.
Our client is very upset, don�t want to use Hibernate any more!
I would appreciate your help.

Thanks.
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here is the suggested way.

1. When mapping, keep everything to the default of lazy loading.
2. In your Use Cases when you are querying set the appropriate Fetching strategies.

So for this thread, lets remove all the "lazy=""" from your mapping.

And please post your Query code, and I can show you have to fix the N+1. This is not a Hibernate bug at all. You need to understand that for a use case the best fetching strategy is not the same as the best fetching strategy for a different use case. That is why it should always be Use Case driven in the Use Case code.

Read my post in this thread here
https://coderanch.com/t/217892/ORM/java/Nested-Children-Access

Hibernate does have a learning curve that is important to go through to fully see the power and speed of Hibernate.

So I highly recommend reading the Hibernate documentation or better yet read "Java Persistence with Hibernate" by Manning.

Good Luck and let see some of that code.

Mark
 
Rvrk reddy
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Mark: I know hibernate basics, we have complicated reflection code to generate polymorphic queries. In summary in our code we can only do "criteria.list", this criteria/associations populated by reflection.
If I hard code in my EJB (by using LEFT_JOIN), I get only one SQL, but our requirement is diffrent.
Can you please suggest me right mappings that can generate only ONE SQL in posted usecase, if this works I will try to simulate by reflection.
Thanks a lot.
 
Rvrk reddy
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please share your expertise..
Thanks, Ram.
 
Mark Spritzler
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Rvrk reddy:
Mark: I know hibernate basics, we have complicated reflection code to generate polymorphic queries. In summary in our code we can only do "criteria.list", this criteria/associations populated by reflection.
If I hard code in my EJB (by using LEFT_JOIN), I get only one SQL, but our requirement is diffrent.
Can you please suggest me right mappings that can generate only ONE SQL in posted usecase, if this works I will try to simulate by reflection.
Thanks a lot.



I don't understand what you mean here. "If I hard code in my EJB"? We need code examples for you to demonstrate. Also if you have to have the fetching strategy to stay the same throughout your application, which I doubt, you can set your collection fetching strategies to either "subselect" or eager.

Mark
 
Rvrk reddy
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Mark Spritzler:


I don't understand what you mean here. "If I hard code in my EJB"? We need code examples for you to demonstrate. Also if you have to have the fetching strategy to stay the same throughout your application, which I doubt, you can set your collection fetching strategies to either "subselect" or eager.

Mark




Mark:
I think I just want to know how to stop loading <many-one> relation backwards. Relations are: User<one-many>Profile and Profile<many-one>User.
I don't want to load User details on retrieveProfile().
Now it's creating unneccesary JOINS on User-table. In <many-one> it looks like <lazy="true"> is not permitted (throwing exception saying "proxy no-proxy false" are allowed). We are really stuck with this problem.
Thanks, Ram.
 
Mark Spritzler
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator



Try the above mapping, By default Hibernate loads things lazily. For Many To One, they put a Proxy object for History and Security. Which means if you query for a User, it will not run a query against History or Security. The moment your code gets the History or Security object, it will run a query for each one.

Now if you don't want a proxy for History or Security and you want it all loaded when you query for the User, which makes just one query for all of it, then use the following mapping.



I was figuring you were talking about the One-To-Many, because usually that is what people are talking about with the N+1. But it can relate to a many to one, just that the N is really a 1 also, so it would be a 1+1 selects issue.

Mark
 
Rvrk reddy
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Mark Spritzler:



Try the above mapping, By default Hibernate loads things lazily. For Many To One, they put a Proxy object for History and Security. Which means if you query for a User, it will not run a query against History or Security. The moment your code gets the History or Security object, it will run a query for each one.

Now if you don't want a proxy for History or Security and you want it all loaded when you query for the User, which makes just one query for all of it, then use the following mapping.



I was figuring you were talking about the One-To-Many, because usually that is what people are talking about with the N+1. But it can relate to a many to one, just that the N is really a 1 also, so it would be a 1+1 selects issue.

Mark




Thanks Mark. I will try this!
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic