• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

(N+1) selects � Hibernate bug?

 
Rvrk reddy
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • 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.
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • 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
http://www.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
  • 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
  • Quote
  • Report post to moderator
Please share your expertise..
Thanks, Ram.
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • 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
  • 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
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • 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
  • 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!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic