Lazy column loading with NHibernate
15 March 2011
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[]
Order
- Id int
- Lines ICollection
- ShippingAddress string
OrderLine
- 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)
{
Console.WriteLine(line.Product.Description);
}
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 :)