Large block data extraction using problems with NHibernate

Question

I have a block of 50 thousand data. I use NHibernate to retrieve everything (everything needed is required). But having a large dataset that is created by joining 5-7 tables, NHibernate takes about one minute. The main reason for slow fetching may be the joining of tables for which NHibernate creates a query for each row from each table. I understand that this is necessary because NHibernate needs to map each row to an object, but this overhead must be deleted.

Is there a way to get data in a block and then create objects using NHibernate. I include a 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 can’t publish the SQL-Profiler image because I don’t have enough reputation, but it will be available on request. thanks....

Answer 1

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

This will greatly improve your productivity.