nHibernate 大查询超时

发布于 2024-12-07 11:56:07 字数 2728 浏览 9 评论 0原文

我们正在为客户开发一个新系统,我们有一个会员表,约有 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

自由范儿 2024-12-14 11:56:07

获取如此大的数据集并不是一个好主意。考虑一下内存消耗。如果您需要处理此数据,请考虑批量处理(最多 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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文