Unexpected N+1 when querying table with one-to-one mapping
Description
I have two entities set with a one-to-one relationship, Practitioner and User.
The following hql query should result in a single DB query: "from Practitioner p inner join fetch p.User"
In fact, I end up with N+1 queries where the first one pulls all pertinent data from Practitioners and Users tables, and the following ones query Practitioner records one by one. Unit test project will be uploaded momentarily. You can observe this with NHProf. This is a pretty severe perf issue for us as the related entities are in reality quite data heavy.
Environment
None
Attachments
1
Activity
Show:
Michael Teper
March 17, 2012 at 5:43 PM
@Arman – there are no bag relations on User (or, in fact, anywhere in this scenario).
Arman Sadeghian
March 17, 2012 at 12:03 PM
I have seen this bug in my project, to solve it just remove any bag relation in User mapping.
If you design your map file lightly then you will enjoy hibernate facilities. in case it's not possible to remove bag relation in User mapping define another mapping file and load the previous one on demand when you need.
Isaac Bolinger
January 4, 2012 at 2:33 AM
I'm running into N+1 with objects containing one-to-ones when querying with Linq (just a straight 'get all' will work)
I have two entities set with a one-to-one relationship, Practitioner and User.
The following hql query should result in a single DB query:
"from Practitioner p inner join fetch p.User"
In fact, I end up with N+1 queries where the first one pulls all pertinent data from Practitioners and Users tables, and the following ones query Practitioner records one by one. Unit test project will be uploaded momentarily. You can observe this with NHProf. This is a pretty severe perf issue for us as the related entities are in reality quite data heavy.