Friday, November 2, 2012

NHibernate: the little-known difference between <set> and <bag>

Set is a collection of unique items. Bag is none unique collection of items. This restriction influences on set's and bag's behavior from a performance point of view.
Take a look on pretty simple model:
 SupplierModel
We have a supplier with thousands of orders. Let's try to add new order to the supplier and see what happens in the database.

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
var supplier = session.Get<Supplier>(id);
supplier.Orders.Add(new SupplierOrder
{
Supplier = supplier,
OrderTime = DateTime.Now
});

tx.Commit();
}
If we map the orders as a <set> then NHibernate produces the next sql queries:

SELECT
supplier0_.Id as Id6_0_,
supplier0_.Name as Name6_0_
FROM Suppliers supplier0_
WHERE supplier0_.Id=@p0

SELECT
orders0_.SupplierId as SupplierId1_,
orders0_.Id as Id1_,
orders0_.Id as Id7_0_,
orders0_.SupplierId as SupplierId7_0_,
orders0_.OrderTime as OrderTime7_0_
FROM SupplierOrders orders0_
WHERE orders0_.SupplierId=@p0

INSERT INTO SupplierOrders (SupplierId, OrderTime, Id) VALUES (@p0, @p1, @p2)
As we see it queries for the supplier row, its orders and then inserts new order. What happens if supplier already has thousands of orders? We fetch them all!
Let's try to map orders as a <bag>

SELECT
supplier0_.Id as Id6_0_,
supplier0_.Name as Name6_0_
FROM Suppliers supplier0_
WHERE supplier0_.Id=@p0

INSERT INTO SupplierOrders (SupplierId, OrderTime, Id) VALUES (@p0, @p1, @p2)

This one seems better. Now we just fetched supplier and inserted new order.

Take it into account when you choose between set and bag.

Pay attention. It behaves different if you are not using bidirectional association between master and detail classes. Also it behaves different when you map detail class as a composite-element. But that's another story.

No comments:

Post a Comment