Why is NHibernate pulling more data than is needed?

Question

I'm running the following query using NHibernate:

var query = session.QueryOver<TaskMeeting>()
    .JoinQueryOver(x => x.Task)
    .Where(x => x.CloseOn == null)
    .List();

Which then generates the following SQL:

SELECT
    this_.Id as Id89_1_,
    this_.TaskMeetingTypeID as TaskMeet2_89_1_,
    this_.DateTime as DateTime89_1_,
    this_.Description as Descript4_89_1_,
    this_.TaskID as TaskID89_1_,
    ltaskalias1_.Id as Id87_0_,
    ltaskalias1_.Title as Title87_0_,
    ltaskalias1_.Description as Descript7_87_0_,
    ltaskalias1_.CreatedOn as CreatedOn87_0_,
    ltaskalias1_.LastUpdated as LastUpda9_87_0_,
    ltaskalias1_.ClosedOn as ClosedOn87_0_,
FROM
    dbo.[TaskMeeting] this_ 
inner join
    dbo.[Task] ltaskalias1_ 
        on this_.TaskID=ltaskalias1_.Id 
WHERE
    ltaskalias1_.ClosedOn is null

Is pulling joined table information normal behavior? I would think it should only be selecting data pertaining to the root entity unless I specify a .Fetch clause or manually select the data.

I'm using Fluent NHibernate for the class mappings, but they are basic mappings for something like this, no eager loading specified - just simple Map and References where appropriate.

I've tried removing the where clause thinking perhaps that was the cause. The additional SELECTs continue to persist unless I remove the join itself.

score 1 · Answer 1 · answered Nov 25 '13 at 10:17

1

The way you queried your data, you get back a generic IList<> containing TaskMeeting entities. So it's normal for NHibernate to query all columns of your TaskMeeting table to put that data into your TaskMeeting entity properties. But i guess that is known already.

But because you want to restrict the data through another table you have to make a join to it. And that's the expensive part for the database server. Querying those few additional columns is peanuts compared to it and NHibernate might as well use the data to fill the Task references in your TaskMeeting entities.

At least that's how i understand it.

answered Nov 25 '13 at 10:17

Thomas Lazar

315
3
10

But isn't the point of having lazily loaded reference entities that they be, you know, lazily loaded. In this particular case, the Task is already likely loaded in secondary cache and pulling it over just adds to the amount of data which has to be pulled across the network. – Anthony Nov 25 '13 at 13:27
Not really. Lazy loading happens for references not already loaded. If you gotten a TaskMeeting via "var taskMeeting = session.Get(1)" and then would access taskMeeting.Task.Description you would lazy load the Task entity referenced by the TaskMeeting entity from the DB (or the 2nd lvl cache). Same if you had just used "sesseion.QueryOver().List()". But the way you queried the TaskMeeting and restricted the result via join, NHibernate already had all the data it needed in the query to fill your TaskMeeting+Task entities. – Thomas Lazar Nov 25 '13 at 21:03
NHibernate always has all the data it needs to query the TaskMeeting+Task entities because it has the class mapping information when you configure it; that doesn't mean it always pulls the data. – Anthony Nov 25 '13 at 21:39
You misread my sentence. Lets rephrase it. The result of the query you made already has data in it for nhibernate to fill your TaskMeeting and the associated Task entities. Because you joined the Task entities in your query the heavy lifting for the database (the join from TaskMeeting to Task) is already done, might es well select the columns of the Task table and fill the Task entities as well. The data is, as you say it, already pulled because of that join. – Thomas Lazar Nov 26 '13 at 08:31

score 0 · Answer 2 · answered Nov 23 '13 at 19:17

The quick answer is you told it to. You called JoinQueryOver which is going to create a join query on the task.

An easier way to get only what you want is to create a linq query and project only the fields you want into am anonymous type. That will generate a query with only the columns declared in the anonymous type.

var meetings = from m in session.Query () where m.Task.ClosedOn <> null select new { somefield = m.Task.Name, ...};

Why is NHibernate pulling more data than is needed?

2 Answers2