Adding another "HasMany" relationship slows retrieval down by two orders of magnitude.
Debbie
via web
I have a problem that I'm sure if caused by a lack of conceptual understanding.
I have a Company entity that has ILists of Employee and Customer entities (i.e. a company have many employees and customers, but the employees and customers only have one company).
I'm mapping it as follows:
public CompanyMap()
{
Table("Companies");
Id(x => x.Id, "COMPANY_ID");
Map(x => x.Name, "COMPANY_NAME");
HasMany<Employee>(Reveal.Member<Company>("_employees"))
.Inverse()
.Cascade.All()
.Fetch.Join();
HasMany<Customer>(Reveal.Member<Company>("_customers"))
.Inverse()
.Cascade.All()
.Fetch.Join();
}
What I have either the Employee or Customer relationships in the mapping, it runs in about 3 seconds. However, with both of them in, retrieval takes 5 minutes!
The (Oracle) database isn't that big. Less than 10-100 records in each table.
Any idea what I'm doing wrong?
Support Staff 2 Posted by Paul Batum on 19 Jun, 2010 07:40 AM
Why do you have your relationships set up as fetch join? I suspect that is
the issue - have you profiled the sql that nhibernate is generating? Have
you tried leaving the fetch strategy as the default?
3 Posted by dcroft10 on 19 Jun, 2010 10:18 AM
I want to eager load the data, as I'll need it all.
I haven't profiled the SQL. How would I extract it?
Support Staff 4 Posted by Paul Batum on 22 Jun, 2010 09:51 AM
There are several ways. The first three that come to mind are:
1. You can turn on nhibernate's "showsql" setting. This will output the SQL
that nhibernate generates to Console.Out. Some of the example configurations
in this page have it:
http://wiki.fluentnhibernate.org/Database_configuration
<http://wiki.fluentnhibernate.org/Database_configuration>2. Use your own
database profiling tools such as sql server profiler.
3. Use NHProf <http://nhprof.com/>
5 Posted by Dmitry on 23 Jun, 2010 02:10 PM
You have 100 records in every table. The query will return you 100100100 = 10^6 records (.Fetch.Join();). If every table has 10 columns and 4 bytes for every column(for example) the total amount of data will be 10^61010104 = 3,72 Gb. A lot of time is needed to process, transmit and allocate such amount of data.
Another way: 3 sec * 100 records = 300 sec = 5 min ))
Two interesting things about Fetch.Join():
1) If you use a CreateCriteria don't forget about Distinct() method, otherwise you will have a lot of duplicate objects.
2) If you have an object which contains a list of "Companies" (HasMany association .Fetch.Select();) the NH will create an additional query for "Employees" when you will querying this object ignoring .Fetch.Join();
Please, correct me if I'm wrong.