使用NHibernate获取大块数据-性能问题

Question

我有一个大小为50k的数据块。我使用NHibernate检索所有数据（检索全部是必要的）。但由于这个大型数据集是通过连接5-7个表创建的，NHibernate需要大约一分钟的时间来完成操作。

慢速获取的主要原因可能是连接表时，NHibernate为每个表中的每一行创建查询。我知道这是必要的，因为NHibernate需要将每一行映射到一个对象，但这种开销必须被消除。

有没有办法以块的形式获取数据，然后再使用NHibernate创建对象。

我同时附上了我的映射文件和代码 - App.config

    <?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
        <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" />
    </configSections>
    <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">

        <bytecode-provider type="lcg"/>
        <reflection-optimizer use="true"/>
        <session-factory>
            <property name="connection.provider" >
                NHibernate.Connection.DriverConnectionProvider
            </property>
            <property name="connection.driver_class">
                NHibernate.Driver.SqlClientDriver
            </property>
            <property name="connection.connection_string">
                Data Source=dewashish-pc\sqlexpress;Initial Catalog=NHibernateTest;Integrated Security=True;
            </property>
            <property name="dialect">
                NHibernate.Dialect.MsSql2005Dialect
            </property>
            <property name="show_sql">
                false
            </property>
            <property name='proxyfactory.factory_class'>NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle</property>

        </session-factory>
    </hibernate-configuration>
</configuration>

Branch.hbm.xml

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="NHibernateSample" namespace="NHibernateSample">
<class name="Branch" table="Branch">
  <id name="BranchCode"/>
  <property name="BranchCode"/>
  <property name="BranchName"/>
  <bag name="EmployeeList" cascade="all-delete-orphan" inverse="false"  fetch="join" lazy="false">
      <key column="BranchCode"/>
      <one-to-many class="Employee" />
  </bag>
</class>

</hibernate-mapping>

Employee.hbm.xml

    <?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="NHibernateSample" namespace="NHibernateSample">
  <class name="Employee" table="Employee">
      <id name="EmployeeId"/>
      <property name="EmployeeId"/>
      <property name="FirstName"/>
      <property name="LastName"/>
      <property name="BranchCode"/>
  </class>
</hibernate-mapping>

Banch.cs

    using System.Collections.Generic; 
using System.Text; 
using System; 


namespace NHibernateSample 
{
    [Serializable]
    public class Branch
    {
        private String branchCode;
        private String branchName;
        private IList<Employee> employeeList = new List<Employee>();

        public virtual IList<Employee> EmployeeList
        {
            get { return employeeList; }
            set { employeeList = value; }
        }
        public virtual String BranchCode
        {
            get { return branchCode; }
            set { branchCode = value; }
        }

        public virtual String BranchName
        {
            get { return branchName; }
            set { branchName = value; }
        }

        public Branch() { }
    }
}

Employee.cs

    using System;
using System.Collections.Generic;
using System.Text;

namespace NHibernateSample
{
    public class Employee
    {
        String employeeId;
        String firstName;
        String lastName;
        String branchCode;

        public virtual String EmployeeId
        {
            get { return employeeId; }
            set { employeeId = value; }
        }

        public virtual String FirstName
        {
            get { return firstName; }
            set { firstName = value; }
        }

        public virtual String LastName
        {
            get { return lastName; }
            set { lastName = value; }
        }

        public virtual String BranchCode
        {
            get { return branchCode; }
            set { branchCode = value; }
        }

        public Employee()
        { }
    }
}

Form1.cs

    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using NHibernate;
using NHibernate.Cfg;
using System.Reflection;
using System.Collections;

namespace NHibernateSample
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            ConfigureNHibernate();
            LoadData();
        }

        static ISessionFactory SessionFactory;
        System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();


        private void LoadData()
        {
            sw.Start();

            using (ISession session = SessionFactory.OpenSession())
            {
                long b = sw.ElapsedMilliseconds;
                try
                {

                    if (session.IsConnected)
                    { 
                        // as br order by br.BranchCode asc
                        IQuery query = session.CreateQuery("from Branch");
                        IList<Branch> iList = query.List<Branch>();
                        dvData.DataSource = iList;
                        int a = 0;
                        foreach (Branch br in iList)
                        {
                            a++;
                        }
                        MessageBox.Show(((sw.ElapsedMilliseconds - b)) + " - MilliSeconds to fetch " + System.Environment.NewLine + a.ToString() + " - Rows");
                    }

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);

                }
            }
        }


        private void ConfigureNHibernate()
        {
            try
            {
                Configuration cfg = new Configuration();
                cfg.Configure();

                Assembly allocationAssembly = typeof(Branch).Assembly;
                cfg.AddAssembly(allocationAssembly);

                SessionFactory = cfg.BuildSessionFactory();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

    }
}

由于我的声誉不够高，我无法发布 SQL-Profiler 的图像，请根据需要提供。 谢谢。

- Chaturvedi Dewashish

---

我认为唯一的方法是编写 HQL。 - shajivk

请问您能否详细说明一下。谢谢。 - Chaturvedi Dewashish

我们可以这样编写代码 var query = this.Session.CreateQuery(@"from Employee as e inner join e.Branch as b with b.BranchName = :branchName");。有关 HQL 的一些信息可以在此处找到 "http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-joins"。 - shajivk

不是SQL，而是HQL。您可以在此处查看更好的示例http://nhforge.org/wikis/howtonh/lazy-loading-eager-loading.aspx。 - shajivk

我已经成功地实现了自己想要的目标，大大缩短了时间。但是从代码中创建查询而不是从XML配置文件中创建查询会降低应用程序的灵活性。如果在不久的将来我想将另一个表加入到类中，我需要修改查询并编写内部连接提取。是否有办法通过.hbm.xml文件获得相同的行为？ - Chaturvedi Dewashish

Answer 1

另一种选择是使用轻量级的微型ORM，例如Dapper，用于与数据库进行大数据量/高频率通信。

这将显著提高您的性能。

- niallNoigeallach

---

我一定会检查这个 ORM 来处理我的数据复杂度。 - Chaturvedi Dewashish