Eclipse Community Forums: EclipseLink » Avoiding N+One Selects and stale BatchValueHolders

Help

Home

Home » Eclipse Projects » EclipseLink » Avoiding N+One Selects and stale BatchValueHolders

Show: Today's Messages :: Show Polls :: Message Navigator

Avoiding N+One Selects and stale BatchValueHolders [message #666808]

Fri, 22 April 2011 13:33

Michael Mising name is currently offline

Michael Mising name
Messages: 9
Registered: April 2011

Junior Member

I'm trying to cut down the number of n+1 selects incurred by my application, in as many places as possible I've tried to add the batch read hint to queries. In a large number of places in the app I don't always know exactly what relationships I'll be traversing (My view displays fields based on user preferences). At that point I'd like to run one query to populate all of those relationships for my objects.

My dream is to call something like ReadAllRelationshipsQuery(Collection,RelationshipName) and populate all of these items so that later calls to:

Collection.get(0).getMyStuff will already be populated and not cause a db query. How can I accomplish this? I'm willing to write any code I need to but I can't find a way that works with eclipselink?

-- Why don't I just batch read all of the possible fields and let them load lazily? What I've found is that the batch value holders that implement batch reads don't behave well with the eclipselink cache. If a batch read value holder isn't "evaluated" and ends up in the eclipse link cache it can become stale and return incorrect data (This behavior was logged as a bug by someone else but rejected...)

How do I avoid N+1 selects for objects/collections I already have a reference to?

Report message to a moderator

Re: Avoiding N+One Selects and stale BatchValueHolders [message #667207 is a reply to message #666808]

Wed, 27 April 2011 08:36

Chris Delahunt
Messages: 862
Registered: July 2009

Senior Member

Hello,

Sounds like batching is what you want. Can you find the link for the issue raised about stale valueholders, or describe it in more detail? Can you explain what you mean by N+1 selects for collections you already have a reference to - if you already have the reference, why would hit cause a query?

You can also try fetch joins over relationships if batching cannot be used.

Best Regards,
Chris

Report message to a moderator

Re: Avoiding N+One Selects and stale BatchValueHolders [message #667234 is a reply to message #666808]

Wed, 27 April 2011 10:49

James Sutherland
Messages: 1834
Registered: July 2009

Senior Member

You can use a LoadGroup on a query to force instantiation.

query hints,
"eclipselink.load-group"
and,
"eclipselink.load-group.attribute"

James : Wiki : Book : Blog

Report message to a moderator

Re: Avoiding N+One Selects and stale BatchValueHolders [message #667251 is a reply to message #667207]

Wed, 27 April 2011 12:13

Michael Mising name
Messages: 9
Registered: April 2011

Junior Member

Agh, I just wrote up a great answer to your question, hit save and lost it! Here goes round two.

The bug on stale valueholder is here: https://bugs.eclipse.org/bugs/show_bug.cgi?id=326197

I understand his reasoning, the 2nd comment by john.vandale does make sense. The problem I see is when a BatchValueHolder ends up unevaluated in the cache. Then a cache hit to that object could return an invalid stale object, not good! That is a scary enough situation that I think it might be worth reopening the bug.

The N+1 Select problem shows up when you grab related objects from a collection of other objects. For example:

You have one query SELECT * FROM USERS WHERE isActive = 1; this query returns N User objects.

Then I iterate over those objects and grab a related field.

for(User user : users) {
user.getNotes();
}

Each call to user.getNotes issues a query like SELECT * FROM NOTES WHERE USERID = user.id;

The end result is 1 query to retrieve the collection and N queries to retrieve all the notes, N+1 queries. It gets even worse when you try to reference some relationship of the notes, heaven help you then!

A Batch Read hint solves this by issuing a different query the first time you call getNotes, instead of SELECT * FROM NOTES WHERE USERID = user.id; eclipselink issues a query like:

SELECT * FROM NOTES WHERE USERID IN(SELECT USERID FROM USERS WHERE isActive = 1)

Eclipselink then uses the results from this query to populate all of the user objects.

The specifics are a bit different I think but the end result is the same.

Thanks for your help!

Michael

Report message to a moderator

Re: Avoiding N+One Selects and stale BatchValueHolders [message #667261 is a reply to message #667234]

Wed, 27 April 2011 13:16

Michael Mising name
Messages: 9
Registered: April 2011

Junior Member

This looks awesome, the examples I'm seeing suggest that I'd be able to run a query like:

LoadGroup loadGroup = new LoadGroup();

loadGroup.addAttribute("notes");

Session.load(users,loadGroup);

I'm a little nervous about the contents of AbstractSession.load, The iterator and iterate methods look like they might trigger an N+1 select. Is there something smart that I'm not seeing in here (I'm looking at the source for 2.2)

Right now our project is using eclipselink 1.1.4 but we are in process of upgrading.

Michael

Report message to a moderator

Re: Avoiding N+One Selects and stale BatchValueHolders [message #667406 is a reply to message #667261]

Thu, 28 April 2011 10:53

Chris Delahunt
Messages: 862
Registered: July 2009

Senior Member

I'm glad James was able to point you in the right direction.

As for bug 326197, it is not a bug. The reason it is not a bug is that if the valueholder is 'stale', it means that the application is ignoring the relationship when making changes that affect the relationship - otherwise it would have been triggered. This means that it is not specific to batching - the relationship may be stale if it was populated before the changes are made.

The only way to keep the cache consistent and reflect what is in the database holds true with or without batching reading. The application must maintain bi-directional relationships when changes are made, or to refresh the affected objects afterward.

Best Regards,
Chris

Report message to a moderator

Re: Avoiding N+One Selects and stale BatchValueHolders [message #667431 is a reply to message #667406]

Thu, 28 April 2011 13:17

Michael Mising name
Messages: 9
Registered: April 2011

Junior Member

The data that is used for the where clause of the BatchValueHolder won't necessarily be related to the relationship that returns incorrect results. For instance if I batch read notes on a query like this:

SELECT * FROM USERS WHERE NUMBEROFFRIENDS = 5;

If I do not immediately call getNotes on those users I have a potential problem. Here is the scenario:

A 2nd request comes in an says add one friend to User 12

A 3rd request comes in and says show me all of User 12's notes (A direct call for User 12 pulls the object out of the identity map)

User12.getNotes() then issues the query:

SELECT NOTES.* FROM NOTES, USERS WHERE NUMBEROFFRIENDS = 5;

This returns 0 notes for User12 because this user now has 6 friends.

I see just about any BatchValueHolder in the global IdentityMap as dangerous?

To avoid this we've stopped using BatchRead hints when we can't guarantee they will be immediately evaluated but that defeats the point of indirection.

Is there a way to change a value holder from a batch value holder back to a standard value holder before abandoning the object? What is the best way to avoid this?

Thanks for your help!

Michael

EDIT: The closer I look at bug 326197 the more I think it isn't perfectly related.

[Updated on: Thu, 28 April 2011 13:21]

Report message to a moderator

Re: Avoiding N+One Selects and stale BatchValueHolders [message #667889 is a reply to message #667261]

Mon, 02 May 2011 11:41

James Sutherland
Messages: 1834
Registered: July 2009

Senior Member

You can also set the LoadGroup on the Query.

If calling the JpaEntityManager/Session load() API, you should ensure the object passed to it use a batch for join fetch to avoid the n+1 problem.

James : Wiki : Book : Blog

Report message to a moderator

Previous Topic:	Working with whole schema
Next Topic:	JPQL query date/datetime problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 22:55:44 EDT 2013