Fluent NHibernate executes single-row-selects on thousands of objects to link to parents

Question

The following is a simplification of my problem domain. We have a series of Trades, that each gets a record in Valuations every business day.

We filter our Valuations List used for a specific day, but to populate the Trade against each of the Valuation rows, NHibernate fires single row selects on the Trades table for around 50k rows in the valuation table. How can I change this so NHibernate does a single select on the Trade table?

CREATE TABLE Trades
(   TradeId   INT
,   InstrumentType VARCHAR(20)
,   TradeDate  DATETIME

,   PRIMARY KEY
(   TradeId ) )

CREATE TABLE Valuations
(   TradeId   INT
,   ValueDate  DATETIME
,   PresentValue NUMERIC(12,4)

,   PRIMARY KEY
(   TradeId
,   ValueDate ) )

.

class Trade
{
    public int TradeId;
    public string InstrumentType;
    public DateTime TradeDate;
}

class Valuation
{
    public int TradeId;
    public DateTime ValueDate;
    public double PresentValue;
    public Trade Trade;
}

.

class ValuationMap : ClassMap<Valuation>
{
    public ValuationMap()
    {
        WithTable("Valuations");
        UseCompositeId()
            .WithKeyProperty(x => x.ValueDate)
            .WithKeyProperty(x => x.TradeId);

        Map(x => x.PresentValue);

        References(x => x.Trade, "TradeId")
            .LazyLoad()
            .Cascade.None()
            .NotFound.Ignore()
            .FetchType.Join();
    }
}

class TradeMap : ClassMap<Trade>
{
    public TradeMap()
    {
        WithTable("Trades");

        Id( x => x.TradeId );

        Map(x => x.InstrumentType);
        Map(x => x.TradeDate);
        Map(x => x.Valuations);
    }
}

.

public List<Valuation> GetValuations(DateTime valueDate)
{
    return (from p in _nhibernateSession.Linq<Valuation>()
            where p.ValueDate == valueDate
            select p).ToList();
}

Answer 1

See Changing the initial LazyLoad behaviour in NHibernate

Also Solving the Select N+1 Problem

Answer 2

You should also look at batch fetching. This is quoted from the Nhib manual - actually google's cache as the site seems to be down for maintenance atm:

Imagine you have the following situation at runtime: You have 25 Cat instances loaded in an ISession, each Cat has a reference to its Owner, a Person. The Person class is mapped with a proxy, lazy="true". If you now iterate through all cats and get the Owner of each, NHibernate will by default execute 25 SELECT statements, to retrieve the proxied owners. You can tune this behavior by specifying a batch-size in the mapping of Person:

<class name="Person" lazy="true" batch-size="10">...</class>

NHibernate will now execute only three queries, the pattern is 10, 10, 5. You can see that batch fetching is a blind guess, as far as performance optimization goes, it depends on the number of unitilized proxies in a particular ISession.