Lazy column loading with NHibernate

Lets take a look at the following simple schema, for brevity’s sake I’ll just use three entities.

**Product **
+ Id int
+ Description string
+ BasePrice decimal
+ Image byte[]

+ Id int
+ Lines ICollection
+ ShippingAddress string

**+ Id int
+ Customer Customer
+ Product Product
+ LinePrice decimal
+ Qty_ int_

From a conceptual point of view it’s great, we have a nice easy object graph to traverse and it’s pretty discoverable as well.

However lets look at it from a SQL point of view. If I were to run this code:

order = session.Get<Order>(orderId);
foreach (var line in order.Lines) 

NHibernate has to retrieve the whole Product object for every line in the order. Even with eager-fetching we would bring back the image for every iteration of the loop. If we’re going across to SQL every time, this becomes painfully slow.

In comes lazy column loading to save the day!

By adding the code lazy=”true” to the column’s XML mapping we can lazily load a single column – as below:

<property name="Image" lazy="true" />

This will cause NHibernate to ignore the Image column when initially building the object.

It will then issue a separate SELECT query to retrieve the image later if we access the Product.Image property.

Very cool and very easy performance tweak :)