Large block of data fetching using NHibernate-performance issue

Question

I have a block of 50k data. I use NHibernate to retrieve all (retrieving all is necessary). But as having large dataset which is created by joining 5-7 tables NHibernate takes around one minute. The main cause of slow fetching might be joining of tables for which NHibernate creates queryfor each row from each table. I understand it is necessary as NHibernate needs to map each row to an object, but this overhead must be removed.

Is there any way to fetch data in BLOCK and then creating objects using NHibernate. I am including my mapping file as well as code -

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);
            }
        }

    }
}

I cannot post image of SQL-Profiler as i dont have enough reputation, but will make available on demand. thanks....

Answer 1

An alternative would be to use a lightweight micro-ORM such as Dapper for any large data/high frequency communication with the DB.

It will improve your performance significantly.