I encountered funny performance problem with IQuery.List():
(I hope that class names are self-explaining)
This query takes 9 seconds of *nhibernate time* i.e. to materialize objects:
from ProjectSubject ps inner join fetch ps.Subject s left join fetch s.Addresses a left join fetch a.Province p left join fetch a.County c left join fetch a.City city where ps.Project.ID = :projectId and coalesce(a.Flag, 1) = 1
This one takes less than 0,1 seconds and does luckily the same:
select ps, s, a from ProjectSubject ps inner join fetch ps.Subject s left join fetch s.Addresses a left join fetch a.Province p left join fetch a.County c left join fetch a.City city where ps.Project.ID = :projectId and coalesce(a.Flag, 1) = 1
regional units (Province, County and City) have one-to-many relationship with Address and latter has inverse many-to-one relationship with all three ones. Also, city has many-to-one relationship with county and county has same with province.
|