nHibernate 大查询超时
我们正在为客户开发一个新系统,我们有一个会员表,约有 23600 名会员。当我们试图获取它们时,就会超时。我已经使用 sql profiler 来获取问题并单独运行它,大约花费了 3 秒。
using (ISession s = SessionFactory.OpenSession())
{
return CreateCriteria(typeof(Member)).List<Member>();
}
看起来问题是 23600 个对象的结果的映射花费的时间太长。 例如,当将结果限制为 300 (.SetMaxResults(300)) 时,它可以正常工作。我现在不知道我们是否需要真正让所有会员进入最终系统,但我知道到时候我们希望让大多数会员在我们网站的会员提供商中生成他们的帐户。
成员映射(流畅的 nHibernate):
Id(x => x.ID).Default("NEWID()");
Map(x => x.LegacyID).ReadOnly();
Map(x => x.Username).Length(32);
Map(x => x.Password).Length(32);
Map(x => x.MemberID).Length(10);
Map(x => x.FirstName).Length(50);
Map(x => x.LastName).Length(50);
Map(x => x.Gender).CustomType<int>();
Map(x => x.BirthDate);
Component(c => c.Home);
Map(x => x.Email).Length(80);
Map(x => x.SendInformation).CustomType<int>();
Map(x => x.SendInvoice).CustomType<int>();
Map(x => x.Comment);
Map(x => x.PublicProfile);
Map(x => x.EntryDate);
Map(x => x.ResignationDate);
References<ProfileItem>(x => x.MemberStatus, "StatusID");
References<ProfileItem>(x => x.MemberType, "TypeID");
References<ProfileItem>(x => x.NationalAssociation);
References<ProfileItem>(x => x.Position, "PositionID");
References<ProfileItem>(x => x.SpecialSkills, "SpecialSkillsID");
References<ProfileItem>(x => x.CompanyType, "CompanyTypeID");
References<ProfileItem>(x => x.JobType, "JobTypeID");
References<ProfileItem>(x => x.GraduateCity, "GraduateCityID");
HasManyToMany<ProfileItem>(x => x.Interessts).Table("MemberInterests")
.ParentKeyColumn("UserID").ChildKeyColumn("ProfileItemID").Cascade.AllDeleteOrphan().Not.LazyLoad();
HasManyToMany<ProfileItem>(x => x.Properties).Table("MemberProperties")
.ParentKeyColumn("UserID").ChildKeyColumn("ProfileItemID").Cascade.AllDeleteOrphan().Not.LazyLoad();
Component(c => c.Company).ColumnPrefix("Work");
Component(c => c.Invoice).ColumnPrefix("Invoice");
Map(x => x.Created);
Map(x => x.CreatedBy).Length(32);
Map(x => x.LatestChange);
Map(x => x.LatestChangeBy).Length(32);
Map(x => x.ElementarySchool);
Map(x => x.University);
Map(x => x.GraduateYear);
Map(x => x.Title).Length(50);
Map(x => x.LibraryAccess);
有人知道这个问题或如何解决它吗?
We are developing a new system for a customer and we have a member table with about 23600 members. And when we are trying to get them all it times out. I have used sql profiler to get the question and run it separatly and it took about 3 secs.
using (ISession s = SessionFactory.OpenSession())
{
return CreateCriteria(typeof(Member)).List<Member>();
}
It looks like the problem is that the mapping of the result of 23600 objects takes too long.
When limiting the results to for an example 300 (.SetMaxResults(300)) it works fine. I dont know right now if we ever would need to actually get all members in the final system, but i know when the time comes we would like to get most members to generate the their accounts in the membership provider of our site.
Member mapping (fluent nHibernate):
Id(x => x.ID).Default("NEWID()");
Map(x => x.LegacyID).ReadOnly();
Map(x => x.Username).Length(32);
Map(x => x.Password).Length(32);
Map(x => x.MemberID).Length(10);
Map(x => x.FirstName).Length(50);
Map(x => x.LastName).Length(50);
Map(x => x.Gender).CustomType<int>();
Map(x => x.BirthDate);
Component(c => c.Home);
Map(x => x.Email).Length(80);
Map(x => x.SendInformation).CustomType<int>();
Map(x => x.SendInvoice).CustomType<int>();
Map(x => x.Comment);
Map(x => x.PublicProfile);
Map(x => x.EntryDate);
Map(x => x.ResignationDate);
References<ProfileItem>(x => x.MemberStatus, "StatusID");
References<ProfileItem>(x => x.MemberType, "TypeID");
References<ProfileItem>(x => x.NationalAssociation);
References<ProfileItem>(x => x.Position, "PositionID");
References<ProfileItem>(x => x.SpecialSkills, "SpecialSkillsID");
References<ProfileItem>(x => x.CompanyType, "CompanyTypeID");
References<ProfileItem>(x => x.JobType, "JobTypeID");
References<ProfileItem>(x => x.GraduateCity, "GraduateCityID");
HasManyToMany<ProfileItem>(x => x.Interessts).Table("MemberInterests")
.ParentKeyColumn("UserID").ChildKeyColumn("ProfileItemID").Cascade.AllDeleteOrphan().Not.LazyLoad();
HasManyToMany<ProfileItem>(x => x.Properties).Table("MemberProperties")
.ParentKeyColumn("UserID").ChildKeyColumn("ProfileItemID").Cascade.AllDeleteOrphan().Not.LazyLoad();
Component(c => c.Company).ColumnPrefix("Work");
Component(c => c.Invoice).ColumnPrefix("Invoice");
Map(x => x.Created);
Map(x => x.CreatedBy).Length(32);
Map(x => x.LatestChange);
Map(x => x.LatestChangeBy).Length(32);
Map(x => x.ElementarySchool);
Map(x => x.University);
Map(x => x.GraduateYear);
Map(x => x.Title).Length(50);
Map(x => x.LibraryAccess);
Anyone know anything about this problem or how to fix it ?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
获取如此大的数据集并不是一个好主意。考虑一下内存消耗。如果您需要处理此数据,请考虑批量处理(最多 1000 个项目)。如果您需要向用户呈现数据,请考虑分页。如果无法减少获取的数据集,可以增加超时时间,但不建议这样做。
Fetching so big dataset is not a good idea. Think about memory consumption. If you need process this data, consider processing in batches (up to 1000 items). If you need present data to user, consider paging. If cannot reduce fetched dataset, you can increase timeout, but it is not recommended practice.