Let's say we have a Category table and Product table. And each Product references Category. So each Category has many Products. I want to load many Categories without Products(to decrease DB access time) then check what Categories we actually need and define a much less subset of Categories. After this I need to load all Products for selected Categories and attach them to Categories with a single DB query. I can load Products separately but in that case they will not be attached to Categories.
Add a comment
|
2 Answers
This can be achieved with HQL and futures
given the entities and maps as follows,
public class Category
{
private IList<Product> _products;
public Category()
{
_products = new List<Product>();
}
public virtual int Id { get; set; }
public virtual string CategoryName { get; set; }
public virtual IList<Product> Products
{
get { return _products; }
set { _products = value; }
}
}
public class CategoriesClassMap : ClassMap<Category>
{
public CategoriesClassMap()
{
Table("Categories");
Id(x => x.Id).GeneratedBy.Native();
Map(x => x.CategoryName);
HasMany<Product>(c => c.Products).LazyLoad();
}
}
public class Product
{
public virtual int Id { get; set; }
public virtual string ProductName { get; set; }
public virtual Category Category { get; set; }
}
public class ProductSClassMap : ClassMap<Product>
{
public ProductSClassMap()
{
Table("Products");
Id(x => x.Id).GeneratedBy.Native();
Map(x => x.ProductName);
References<Category>(x => x.Category).Not.Nullable();
}
}
With following HQL, it will load all the categories and the products in a single query,
var categories = session.CreateQuery("from Category c join fetch c.Products where c.Id in (1,2)")
.Future<Category>().Distinct().ToList();
It only fetches data related to category id 1 and 2. The SQL generated looks like,
select category0_.Id as Id1_0_, products1_.Id as Id3_1_, category0_.CategoryName as Category2_1_0_, products1_.ProductName as ProductN2_3_1_, products1_.Category_id as Category3_3_1_, products1_.Category_id as Category3_0__, products1_.Id as Id0__ from Categories category0_ inner join Products products1_ on category0_.Id=products1_.Category_id where category0_.Id in (1 , 2);
The same (using future) is applicable for queryover or criteria
2 Comments
Serhiy
This is actually the way we are handling it now. But it's still have an issue - selected categories being extracted(and probably reconstructed) for the second time.
Low Flying Pelican
but this only requires 2 SQL executions, 1. for loading all categories in order to filter 2. The query to fetch all categories + products for filtered categories. So even if you manage to do attach loaded products to already loaded categories what difference does it make?
This approach, solution is natively built in NHiberante. It is called:
19.1.5. Using batch fetching
NHibernate can make efficient use of batch fetching, that is, NHibernate can load several uninitialized proxies if one proxy is accessed (or collections. Batch fetching is an optimization of the lazy select fetching strategy. There are two ways you can tune batch fetching: on the class and the collection level.
Batch fetching for classes/entities is easier to understand. 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 call cat.Owner on 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" batch-size="10">...</class>
NHibernate will now execute only three queries, the pattern is 10, 10, 5.
You may also enable batch fetching of collections. For example, if each Person has a lazy collection of Cats, and 10 persons are currently loaded in the ISesssion, iterating through all persons will generate 10 SELECTs, one for every call to person.Cats. If you enable batch fetching for the Cats collection in the mapping of Person, NHibernate can pre-fetch collections:
<class name="Person">
<set name="Cats" batch-size="3">
...
</set>
</class>
SUMMARY: There is an optimization mapping setting: batch-size="25".
We can use it on class level (used later for many-to-one relations) or on collections (directly on one-to-many realtion)
This will lead to very few SELECT statements to load complex object graph. And most important benefit is, that we can use paging (Take(), Skip()) when we query the root entity (no multiple rows)
Check also this, with even some more links...
2 Comments
Serhiy
The problem here is that I need to load Products only for selected Categories, not for all that has been loaded
Radim Köhler
I would really not try to solve, not to be smarter then native, built in functionality. Just try, to load all products for all categories with my (well NHibernate) approach. Check the performance. Then select just few categories with a query, and let again load all products. I am suer, you will be suprised, that there is almost nothing different. The performance gain, to load just selected can be hardly replaced with a use of built in feature. I am using batch-size on a daily bases, beeing sure, that I can query just a root, and if needed, all relations are loaded. Working really well...