2

For the sake of the example, let's say that I have to model the "person" entity of the database of my country's revenue service, and that in my very small country the first name and the last name of a person are enough to uniquely identify the person. Additionally, the revenue service's database does not use surrogate keys, and adding a surrogate key to it would zero out the GDP of the country for the next 10 years.

The Persons table has three fields:

  • FirstName
  • LastName
  • CurrentAddress

And, given the size of my country, the table has a unique constraint on the <FirstName, LastName> pair of columns.

Given this schema, my very simple Person class has the following members:

  • Key: an instance of a PersonKey class, which in turn has FirstName and LastName members, and of course implements Equals() and GetHashCode();
  • CurrentAddress: a simple string.

The NHibernate mapping looks as follows:

<class name="Person" table="Persons" lazy="false">    

  <composite-id name="Key" class="PersonKey">
    <key-property name="FirstName" type="string" column="FirstName"/>
    <key-property name="LastName" type="string" column="LastName"/>
  </composite-id>

  <property name="CurrentAddress" type="string" column="CurrentAddress" not-null="true" />

</class>

So far so good, this mapping works fine and I can happily load Person entities from the DB.

However, when I look under the hood, I can see that when loading the entire set of persons, NHibernate does the following:

  1. Opens a recordset to load key properties only (i.e. exclusively the FirstName and LastName fields) from the Persons table;
  2. For each <FirstName, LastName> pair loaded from Persons, it issues a SELECT - of course against Persons as well - to load the CurrentAddress for the person having that FirstName and LastName.

In other words, NHibernate is first loading the keys, and then it issues a series of SELECT's to load each Person separately providing the key in the WHERE clause.

Provided that I am not interested in writing to the database, is there a way to tell NHibernate that it could use a single recordset to retrieve both key and non-key properties from the table?

Gabriele Giuseppini
  • 1,541
  • 11
  • 19
  • Update: this happens only when I use `IQuery.Enumerable()`. If I use `IQuery.List()` NHibernate does the smart thing and only queries the table once. However, when I have 3.7 gazillions of persons in the table and my app only needs to process a person at a time, storing all persons in a list is a _bit_ overkill. – Gabriele Giuseppini Jul 26 '11 at 15:22
  • how does it behave when you use `IQuery.Future()` ? – Firo Jul 26 '11 at 15:47
  • Thanks for the suggestion. `IQuery.Future()` behaves differently depending on the driver's capabilities. If I use a driver that does not support multiple queries, but which supports multiple result sets (e.g. OdbcDriver), then `::Future()` operates exactly like `::List()`. On the other hand, if the driver supports multiple queries and no multiple result sets (e.g. SqlClientDriver), then `::Future()` reads the entire recordset in memory (killing the app). Unfortunately NHibernate does not ship with a driver for SQL Server that supports both multiple queries and multiple record sets. – Gabriele Giuseppini Jul 26 '11 at 16:26
  • Update: even if I had a driver that supported both, it appears that `IQuery.Future()` would load all results in a list. – Gabriele Giuseppini Jul 26 '11 at 16:43

1 Answers1

0

IQuery.Enumerable has the behavior you mentioned in your comment (loads the keys first, the elements on MoveNext)

In any case, NH is not designed for the mass-processing scenario you are trying to create.

You'll have much better performance by using a raw DataReader.

Diego Mijelshon
  • 52,548
  • 16
  • 116
  • 154