SELECT n + 1 problem when using an (id)bag with composite-element and 'join' fetch-strategy
Description
Environment
Attachments
Activity
Frederik Gheysels September 18, 2008 at 12:44 AM
Nevermind, now I see your point:
http://www.nhforge.org/wikis/howtonh/get-unique-results-from-joined-queries.aspx
that was not completely clear to me in your previous post.
Frederik Gheysels September 17, 2008 at 5:42 AM
I don't wan't to fight over this, but imho this is not expected behaviour.
Since, as I read in the documentation (16.1.2):
Select fetching (the default) is extremely vulnerable to N+1 selects problems, so we might want to enable join fetching in the mapping document:
<set name="Permissions"
fetch="join">
<key column="userId"/>
<one-to-many class="Permission"/>
</set>
I now have a similar situation, in where I have 2 entities. One entity refers to the other. So , in my mapping , I use a Set:
[code]
<class name="Chapter" table="Chapter" lazy="false">
<id name="Id" column="ChapterId">
<generator class="guid.comb" />
</id>
<set name="Names" access="field.camelcase-underscore" lazy="false" table="ChapterName" cascade="all-delete-orphan" inverse="true">
<key column="ChapterId" />
<one-to-many class="ChapterName" />
</set>
</class>
/code
The ChapterName class is regarded as an entity, since instances are saved in a separate table, which has identity.
Retrieving all Chapters results in an n+1 select problem. (I specify lazy=false on the collection, since each chapter has a very limited number of names (on average, no more then 3).
To ressolve the select n+1, I specify a 'join' fetchmode. Whether I do this in the mapping file, or in the specific Criteria (using SetFetchMode), NHibernate gives me a cartesian product. For every record that exists in the resultset, a Chapter entity is created.
Then, you should explain to me why this is expected behaviour, since,imho this is clearly not what should be expected.
(And yes, my entities override GetHashcode / Equals)
Fabio Maulo September 14, 2008 at 3:06 PM
Expected behavior.
For HQL:
The fetch in the mapping affect HQL if subselect fetching is used (doc. 16.1.2).
In your case, using subselect, mean 2 query in only one roundtrip (if your drive support Multiple Queries).
If you want only one SQL using early loading the HQL is:
from Project p join fetch p.ProjectMembers pm join fetch pm.PersonInformation
and you must use DistinctRootEntityResultTransformer
For Criteria:
Your mapping are joining 3 tables using LEFT OUTER JOIN; you must use DistinctRootEntityResultTransformer in the criteria-query.
As we write in the documentation "Usually, we don't use the mapping document to customize fetching . Instead, we keep the default behavior, and override it for a particular transaction..."
Use lazy="false" everywhere and set all kind of relations to fetch="join" is NOT a good practice, especially with big collections and/or complex applications.
If you need load the whole entity tree in only one roundtrip you can use MultiQuery and/or MultiCriteria feature.
In the future please use our user group for request-support:
http://groups.google.com/group/nhusers
Thanks.
Frederik Gheysels September 10, 2008 at 1:17 PM
i've attached a new testcase.
The test method which uses an IQuery will perform a select n +1 to retrieve the projects, but will retrieve the correct amount of results
The test method which uses the ICriteria will perform one query to retrieve all projects, but will retrieve to many results.
Fabio Maulo September 9, 2008 at 11:33 PM
Please create a test case using NUnit and not a winForm application.
Details
Details
Assignee
Reporter
Components
Affects versions
Priority
Major
I'm using NHibernate 1.2.0.4 in a C# Project.
In this project, I've a class 'Project' which has a collection (idbag) of 'ProjectMembers'. In order to map this, I've defined the mapping like this:
[code]
<class name="Project" table="Project" lazy="false">
<id name="Id" column="projectid" unsaved-value="-1" >
<generator class="identity" />
</id>
<property name="Name" column="name" />
<idbag name="ProjectMembers" table="ProjectMember" lazy="false" fetch="join">
<collection-id column="projectmemberid" type="Int32">
<generator class="identity" />
</collection-id>
<key column="ProjectId" />
<composite-element class="ProjectMember">
<many-to-one name="UserInformation" class="Person" column="PersonId" fetch="join"/>
<property name="MemberRole" column="Role" />
</composite-element>
</idbag>
</class>
/code
As you can see, the idbag should use the 'join' fetch-strategy, as I've defined it like this in the mapping file.
However, when I perform a query which would retrieve all projects (and their project members), like this:
[code]
session.CreateQuery ("from Project").List<Project>();/code
I notice that NHibernate is not retrieving all the required data within one single SQL statement. Instead, NHibernate fetches the projects in multiple queries:
First, one query is executed to retrieve all the Projects.
Then, for each project, another query is executed which retrieves the projectmembers for that project.
This means that, when I have 100 projects, 101 queries are executed. I do not understand how this is possible, since I've defined the 'join' fetch strategy.
(I'm using SQL Server 2005).
In order to make things a little bit less abstract, I've provided a test-case which you'll find included.
In the attached zip-file, you'll find:
a VS.NET 2008 solution which you can use to see the problem
a directory 'createdatabasescript' which contains an SQL script which will create and populate the necessary Database. (SQL Server 2005).
Create a database which is called 'testcase', and execute the 'create_and_populate_db.sql' file on it. (You'll find this file in 'createdatabasescript' folder).
Make sure that the connectionstring in the app.config file is specified correctly
Launch the program, and launch a SQL Server Profiler - Trace
Click the 'Get All Projects With their members' button
See in the profiler that multiple queries are executed, while it should be possible to retrieve those projects in one query.