加载大型集合时的 nHibernate 性能问题 [英] nHibernate performance issue when loading large collections
问题描述
(只是为了说明:我的应用并不是真正关于员工和部门.我只是为了举例而使用这些术语).
每个部门都有一个员工集合，它是延迟加载的.每当我添加一个新员工时，我都想确保它不存在于集合中，因此我将集合加载到内存中并对其进行检查.
问题是 - 在生产环境中，我有一些部门拥有 10,000 多名员工.
我发现获取集合然后保存新员工需要很多时间.
我做了一些实验，其中我将 nH 生成的 完全相同 select 语句复制到 ADO.Net SQLDataAdapter.结果如下:
(just to make clear: my app isn't really about employees and departments. I just use these terms for example's sake).
Each department has an employees collection, which is lazily-loaded. Whenever I add a new employee I want to make sure It doesn't already exist in the collection, so I load the collection to memory and perform the check on it.
Problem is- in production environment, I have some departments with 10,000+ employees.
I found that fetching the collection and then saving the new employee takes A LOT of time.
I've done a little experment, in which I copied the exact same select statement generated by nH to ADO.Net SQLDataAdapter. Here are the results:
***16:04:50:437*** DEBUG NHibernate.SQL - SELECT ... FROM dbo.[Employee] emp0_ left outer join dbo.[Department] department1_ on emp0_.Department_id=department1_.Id left outer join dbo.[TableC] TableC2_ on department1_.TableC_id=TableC2_.Id WHERE emp0_.SomeField_id=@p0;@p0 = 2
***16:05:00:250*** DEBUG NHibernate.SQL - SELECT ... FROM dbo.TableD codeshared0_ left outer join dbo.[Department] department1_ on codeshared0_.Department_id=department1_.Id left outer join dbo.[TableC] TableC2_ on department1_.TableC_id=TableC2_.Id WHERE codeshared0_.Employee_id in (select emp0_.Id FROM dbo.[Employee] emp0_ left outer join dbo.[Department] department1_ on emp0_.Department_id=department1_.Id left outer join dbo.[TableC] TableC2_ on department1_.TableC_id=TableC2_.Id WHERE emp0_.SomeField_id=@p0);@p0 = 2
16:05:04:984 DEBUG NHibernate.SQL - Reading high value:select next_hi from dbo._uniqueKey with (updlock, rowlock)
16:05:05:078 DEBUG NHibernate.SQL - Updating high value:update dbo._uniqueKey set next_hi = @p0 where next_hi = @p1;@p0 = 10686, @p1 = 10685
***16:05:05:328*** DEBUG MyApp.Managers - commiting
16:05:12:000 DEBUG NHibernate.SQL - INSERT INTO dbo.[Employee] (...) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9);@p0 = 23/04/2011 04:04:49, @p1 = 23/04/2011 03:34:49, @p2 = 23/04/2011 04:04:49, @p3 = 23/04/2011 03:34:49, @p4 = '', @p5 = False, @p6 = 433, @p7 = NULL, @p8 = 2, @p9 = 10685
16:05:12:140 DEBUG NHibernate.SQL - UPDATE dbo.[Employee] SET Department_id = @p0 WHERE Id = @p1;@p0 = 2, @p1 = 10685
16:05:12:343 DEBUG MyApp.Managers - success
16:05:12:359 DEBUG MyApp.Tests - ------------------------------------------------------------
16:05:12:359 DEBUG MyApp.Tests - Finished nHib stuff- now switching to ADO
16:05:12:359 DEBUG MyApp.Tests - starting SQL: SELECT ... FROM dbo.[Employee] emp0_ left outer join dbo.[Department] department1_ on emp0_.Department_id=department1_.Id left outer join dbo.[TableC] TableC2_ on department1_.TableC_id=TableC2_.Id WHERE emp0_.SomeField_id=2
16:05:14:750 DEBUG MyApp.Tests - total rows received: 10036
16:05:14:750 DEBUG MyApp.Tests - SQL: SELECT ... FROM dbo.TableD codeshared0_ left outer join dbo.[Department] department1_ on codeshared0_.Department_id=department1_.Id left outer join dbo.[TableC] TableC2_ on department1_.TableC_id=TableC2_.Id WHERE codeshared0_.Employee_id in (select emp0_.Id FROM dbo.[Employee] emp0_ left outer join dbo.[Department] department1_ on emp0_.Department_id=department1_.Id left outer join dbo.[TableC] TableC2_ on department1_.TableC_id=TableC2_.Id WHERE emp0_.SomeField_id=2)
16:05:15:250 DEBUG MyApp.Tests - total rows received: 2421
如您所见 - 使用 nH 提取需要约 15 秒，而使用 ADO.Net 需要约 2 秒.
通过一些研究，我知道 nH 可能并不意味着用于在会话中存储那么多项目.你能想到这个问题的任何其他可能的原因，或者除了在数据库级别过滤员工之外的其他建议吗?
as you can see- fetching takes ~15 secs with nH, compared to ~2 secs with ADO.Net.
From researching around a bit I know that nH probably isn't meant to be used to store that many items in session. Can you think of any other possible reason for this problem, or of another suggestion other than filtering the Employees at the DB level?
谢谢
--编辑--
按照以下建议，我尝试使用反射优化器(没有区别)和 IStatelessSession 来加载我的集合(抛出异常 - 无状态会话无法获取集合.).
我认为我在 Department 类中的代码必须从干净的:
--EDIT--
Following the below suggestions i've tried using Reflection Optimizer (made no difference), and IStatelessSession for loading my collection (throws an exception- collections cannot be fetched by a stateless session.).
I think my code in the Department class will have to change from the clean:
if (this.Employees.Contains(emp))
{
...
}
到这个更脏"的版本:
var employeesRepository = IOCContainer.Get<IEmployeesRepository>();
if (employeesRepository.EmployeeExists(this,emp))
{
...
}
有人有更好的建议吗?
推荐答案
您没有理由将所有员工都加载到内存中.你应该写一个查询使用 HQL/Critiria API/Linq 到 NHibernate 来检查员工是否已经存在于数据库中.例如:
There is no reason for you to load all the empoyees to memory. you should write a query using HQL/Critiria API/Linq to NHibernate to check if the employee already existing in the DB. for example:
var existingEmpoyee = session.Query<Employee>()
.Where(e => e.Equals(newEmployee))
.FirstOrDefault();
if(existingEmployee != null)
// Insert new employee to DB
这篇关于加载大型集合时的 nHibernate 性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
