Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / C#

A Comparison of Data Access Methods - Part 2

Rate me:
Please Sign up or sign in to vote.
4.96/5 (15 votes)
12 Oct 2013CPOL8 min read 46.4K   1.1K   31   13
This is part 2 of a comparison on Data Access methods in the .NET environment.

Introduction 

This is part 2 of the comparison on Data Access methods in the .NET environment. In this part, I will be focusing on insert operations. I will make comparisons between ADO.NET, and NHibernate. Generally speaking, NHibernate can be slow when inserting data into the database, particularly when we have a large data set. To improve performance, NHibernate introduced Hilo. I will show you how to use Hilo in this article and show you what it is and what kind of challenges it can present to us. Although the comparison is in the .NET environment on a SQL Server database, the concept and the result should be applicable to other environments such as Java and Oracle.

Background

In part 1 of this article, I demonstrated how to set up NHibernate in .NET applications and did a comparison on the read operations among different data access methods. In this part, I will strongly recommend you to take a look at part 1, if you are not already an NHibernate expert. The code to set up the NHibernate environment in this part is almost the same as in part 1 and I will not go over the details again. I will quickly jump to the insert operations using NHibernate and make a performance comparison with the direct ADO.NET method that does not use NHibernate at all. Since we will be working on insertion operations only, I did not include Dapper in the example application.

The Example Database

The examples in this article use Visual Studio 2010 and SQL Server 2008 R2. If you want to repeat the examples by yourself, you will need SQL Server and you will need to have administrative permissions. If you run the following script in your database server, you will have the example database and all the tables and stored procedures for the example.

SQL
SET NOCOUNT ON
    
USE [master]
GO
    
-- Create a login user
IF EXISTS
    (SELECT * FROM sys.server_principals
        WHERE name = N'TestUser')
BEGIN
    DROP LOGIN [TestUser]
END
GO
     
EXEC sp_addlogin @loginame = 'TestUser',
    @passwd  = 'Password123';
GO
    
-- Create the database
IF EXISTS 
    (SELECT name FROM sys.databases 
        WHERE name = N'SQLPerformanceInsert')
BEGIN
 ALTER DATABASE [SQLPerformanceInsert] SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE
 DROP DATABASE [SQLPerformanceInsert]
END
GO
     
CREATE DATABASE SQLPerformanceInsert
GO
    
USE [SQLPerformanceInsert]
GO
    
-- Grant the required database access to the login 
sp_grantdbaccess 'TestUser'
GO
sp_addrolemember 'db_datareader', 'TestUser'
GO
sp_addrolemember 'db_datawriter', 'TestUser'
GO
    
CREATE TABLE [dbo].[THiloIDGenerator](
    [TableName] [varchar](50) NOT NULL,
    [Hi] [int] NOT NULL,
 CONSTRAINT [PK_THilo] PRIMARY KEY CLUSTERED 
(
    [TableName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
    
CREATE TABLE [dbo].[TStoredProcedureIDGenerator](
    [TableName] [varchar](50) NOT NULL,
    [NextId] [int] NOT NULL,
 CONSTRAINT [PK_TIDGenerator] PRIMARY KEY CLUSTERED 
(
    [TableName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
    
CREATE TABLE [dbo].[TStudent_Identity](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [LastName] [varchar](50) NULL,
    [FirstName] [varchar](50) NULL,
    [Score] [int] NULL,
 CONSTRAINT [PK_TStudent_Identity] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
    
CREATE TABLE [dbo].[TStudent_StoredProcedure](
    [Id] [int] NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [Score] [int] NOT NULL,
 CONSTRAINT [PK_TStudent_StoredProcedure] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
    
CREATE TABLE [dbo].[TStudent_Hilo](
    [Id] [int] NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [Score] [int] NOT NULL,
 CONSTRAINT [PK_TStudent_Hilo] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
    
-- Initiate the Hilo table and the ID Generator table
INSERT INTO [THiloIDGenerator] VALUES ('TStudent_Hilo', 0)
INSERT INTO [TStoredProcedureIDGenerator]
    VALUES ('TStudent_StoredProcedure', 1)
GO
    
CREATE TYPE [dbo].[StudentTableType] AS TABLE(
    [Id] [int] NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [Score] [int] NOT NULL
)
GO
    
CREATE PROCEDURE [dbo].[InsertStudentsByStoredProcedure] 
    @Students AS StudentTableType READONLY
AS
BEGIN
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    
    DECLARE @FirstID AS INT
    DECLARE @Count AS INT
    
    SELECT @Count = COUNT(*) FROM @Students
    
    BEGIN TRANSACTION
    BEGIN TRY
        SELECT @FirstID = NextId
        FROM TStoredProcedureIDGenerator
            WHERE TableName = 'TStudent_StoredProcedure'
        UPDATE TStoredProcedureIDGenerator
            SET NextId = @FirstID + @Count
            WHERE TableName = 'TStudent_StoredProcedure'
        
        INSERT INTO TStudent_StoredProcedure
        SELECT Id + @FirstID, LastName, FirstName, Score
        FROM @Students
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION
    
        RAISERROR ('Unable to save students', 16, 1)
        RETURN
    END CATCH
    
    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION
    
    SELECT @FirstID AS FirstId
END
GO
    
GRANT EXECUTE ON TYPE::[dbo].[StudentTableType] TO TestUser
GRANT EXECUTE ON OBJECT::[dbo].[InsertStudentsByStoredProcedure]
    TO TestUser
GO
    
-- Bring the database on-line
ALTER DATABASE [SQLPerformanceInsert] SET MULTI_USER
GO
    
PRINT 'SQLPerformanceInsert database is created'

Upon successful run of the script, we will have a database named [SQLPerformanceInsert] in the database server.

Image 1

In [SQLPerformanceInsert], we have five tables and one stored procedure. The following three tables have almost exactly the same structure, except that the [ID] field is an identity field in the [TStudent_Identity] table.

  • TStudent_Identity
  • TStudent_Hilo
  • TStudent_StoredProcedure

In this example, we will upload an XML file that has information of 5000 artificially generated students through different data access methods and we compare the time spent by each method. When we insert the students into the database, we will assign each student an ID. The [THiloIDGenerator] table is the table to generate the IDs when students are inserted into the [TStudent_Hilo] table. It is defined as follows:

Image 2

It is initiated as follows when we create the database.

Image 3

The stored procedure [InsertStudentsByStoredProcedure] will be used in the example to insert students into the [TStudent_StoredProcedure] table by a direct ADO.NET stored procedure call. It uses the [TStoredProcedureIDGenerator] table to generate the IDs. [TStoredProcedureIDGenerator] is defined as follows:

Image 4

It is initiated as follows when we create the database.

Image 5

The stored procedure [InsertStudentsByStoredProcedure] is created as follows:

SQL
CREATE PROCEDURE [dbo].[InsertStudentsByStoredProcedure] 
    @Students AS StudentTableType READONLY
AS
BEGIN
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    
    DECLARE @FirstID AS INT
    DECLARE @Count AS INT
    
    SELECT @Count = COUNT(*) FROM @Students
    
    BEGIN TRANSACTION
    BEGIN TRY
        SELECT @FirstID = NextId
        FROM TStoredProcedureIDGenerator
            WHERE TableName = 'TStudent_StoredProcedure'
        UPDATE TStoredProcedureIDGenerator
            SET NextId = @FirstID + @Count
            WHERE TableName = 'TStudent_StoredProcedure'
        
        INSERT INTO TStudent_StoredProcedure
        SELECT Id + @FirstID, LastName, FirstName, Score
        FROM @Students
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION
    
        RAISERROR ('Unable to save students', 16, 1)
        RETURN
    END CATCH
    
    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION
    
    SELECT @FirstID AS FirstId
END

The user defined table type [StudentTableType] used by the stored procedure is defined as follows:

SQL
CREATE TYPE [dbo].[StudentTableType] AS TABLE(
    [Id] [int] NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [Score] [int] NOT NULL
)

The Example MVC Web Application

As in part 1 of this article, the example application is developed in Visual Studio 2010 and MVC 2, so most of you can download and run it without any environmental problems.

Image 6

  • Controllers\HomeController.cs is the application's controller.
  • ViewModels\IndexVM.cs implements the MVC view model.
  • The files in the "DataAccessLayer" implement the code to access the database using each method.

The files in the DataAccessUtilities folder set up the NHibernate environment. It also has some utility classes to help us access the database through ADO.NET. In part 1 of this article, I went through how to set up the NHibernate environment in detail, so I will not discuss the files in the DataAccessUtilities folder in this part. The DataAccessMethod.cs file has an enum for the three methods that we will compare.

C#
namespace SQLPerformanceEntityInsert.DataAccessUtilities
{
    namespace SQLPerformanceRead.DataAccessUtilities
    {
        public enum DataAccessMethod { AdoNet, NHibernateIdentity, NHibernateHilo };
    }
}

The View Models

The view models of the application are implemented in the ViewModels\IndexVM.cs file.

C#
using System;
using System.Collections.Generic;
using SQLPerformanceEntityInsert.DataAccessUtilities.SQLPerformanceRead.DataAccessUtilities;
    
namespace SQLPerformanceEntityInsert.ViewModels
{
    public class Student
    {
        public int Id { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public int Score { get; set; }
    }
    
    public class IndexVm
    {
        public DataAccessMethod AccessMethod { get; set; }
        public TimeSpan? TimeSpent { get; set; }
        public List<Student> UploadedStudents { get; set; }
    }
}

The Student class represents a student uploaded to the web application and the IndexVm class will be used by the UI to display the uploaded students and the time spent to insert the students into the database by each data access method.

The Data Access Layer

Image 7

ADO.NET Data Access Method  

The file ADONET\AdoNetDbAccess.cs implements the code to insert the students into the database using ADO.NET.

C#
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Linq;
using System.Xml.Linq;
using SQLPerformanceEntityInsert.DataAccessUtilities.ADONET;
using SQLPerformanceEntityInsert.ViewModels;
    
namespace SQLPerformanceEntityInsert.DataAccessLayer.ADONET
{
    public static class AdoNetDbAccess
    {
        private static DataTable GetStudentList(HttpPostedFileBase fileStudents)
        {
            var table = new DataTable();
            table.Columns.Add("Id", System.Type.GetType("System.Int32"));
            table.Columns.Add("LastName", System.Type.GetType("System.String"));
            table.Columns.Add("FirstName", System.Type.GetType("System.String"));
            table.Columns.Add("Score", System.Type.GetType("System.Int32"));
    
            var streamStudents = fileStudents.InputStream;
            streamStudents.Position = 0;
            var doc = XDocument.Load(streamStudents);
            var students = doc.Descendants("student");
    
            var i = 0;
            foreach(var student in students)
            {
                var row = table.NewRow();
                row["Id"] = i;
                row["LastName"] = student.Descendants("LastName").First().Value;
                row["FirstName"] = student.Descendants("FirstName").First().Value;
                row["Score"] = student.Descendants("Score").First().Value;
                table.Rows.Add(row);
    
                i++;
            }
    
            return table;
        }
    
        public static IndexVm UploadFile(HttpPostedFileBase fileStudents)
        {
            var vm = new IndexVm();
    
            var startTime = DateTime.Now;
            vm.TimeSpent = DateTime.Now.Subtract(startTime);
    
            var cmd = new SqlCommand
            {
                CommandType = CommandType.StoredProcedure,
                CommandText = "InsertStudentsByStoredProcedure"
            };
            var table = GetStudentList(fileStudents);
            cmd.Parameters.AddWithValue("@Students", table);
    
            var idT = AdoNetUtility.GetADataTable(cmd);
            var firstId = Convert.ToInt32(idT.Rows[0]["FirstId"]);
    
            vm.TimeSpent = DateTime.Now.Subtract(startTime);
            vm.UploadedStudents
                = (from s in table.AsEnumerable()
                    select new Student()
                                {
                                    Id = Convert.ToInt32(s["Id"]) + firstId,
                                    LastName = Convert.ToString(s["LastName"]),
                                    FirstName = Convert.ToString(s["FirstName"]),
                                    Score = Convert.ToInt32(s["Score"]) + firstId
                                }).ToList();
    
    
            return vm;
        }
    }
}

The UploadFile method calls the stored procedure [InsertStudentsByStoredProcedure] to insert the students in a single batch into the [TStudent_StoredProcedure] table. The transaction is controlled inside the stored procedure, so if it is successful, all the students are inserted. If the transaction fails, none is inserted.

The NHibernate Data Access Method Using Identity ID Field  

NHibernateIdentity\DataModel\TStudent_Identity.cs defines the data model to insert the students into the [TStudent_Identity] table.

C#
namespace SQLPerformanceEntityInsert.DataAccessLayer.NHibernateIdentity.DataModel
{
    public class TStudent_Identity
    {
        public virtual int Id { get; set; }
        public virtual string LastName { get; set; }
        public virtual string FirstName { get; set; }
        public virtual int Score { get; set; }
    }
}

The NHibernate mapping of the TStudent_Identity class is defined in the TableMapping\TStudentIdentityMap.cs file.

C#
using FluentNHibernate.Mapping;
using SQLPerformanceEntityInsert.DataAccessLayer.NHibernateIdentity.DataModel;
    
namespace SQLPerformanceEntityInsert.DataAccessLayer.NHibernateIdentity.TableMapping
{
    public class TStudentIdentityMap : ClassMap<TStudent_Identity>
    {
        public TStudentIdentityMap()
        {
            Id(x => x.Id).Column("Id").GeneratedBy.Identity();
            Map(x => x.LastName).Column("LastName");
            Map(x => x.FirstName).Column("FirstName");
            Map(x => x.Score).Column("Score");
        }
    }
}

The NHibernateIdentity\NHibernateIdentityDbAccess.cs file implements the code to insert the student into the [TStudent_Identity] table on top of the NHibernate mapping.

C#
using System;
using System.Data;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using System.Collections.Generic;
using SQLPerformanceEntityInsert.DataAccessLayer.NHibernateIdentity.DataModel;
using SQLPerformanceEntityInsert.DataAccessUtilities.NHibernate;
using SQLPerformanceEntityInsert.ViewModels;
    
namespace SQLPerformanceEntityInsert.DataAccessLayer.NHibernateIdentity
{
    public static class NHibernateIdentityDbAccess
    {
        private static List<TStudent_Identity> GetStudentList(HttpPostedFileBase fileStudents)
        {
            var streamStudents = fileStudents.InputStream;
            streamStudents.Position = 0;
            var doc = XDocument.Load(streamStudents);
            var students = (from s in doc.Descendants("student")
                            select new TStudent_Identity()
                            {
                                LastName = s.Descendants("LastName").First().Value,
                                FirstName = s.Descendants("FirstName").First().Value,
                                Score = Convert.ToInt16(s.Descendants("Score").First().Value)
                            }).ToList();
    
            return students;
        }
    
        public static IndexVm UploadFile(HttpPostedFileBase fileStudents)
        {
            var vm = new IndexVm();
    
            var startTime = DateTime.Now;
    
            var students = GetStudentList(fileStudents);
            using (var session = NHibernateUtility.OpenSession())
            {
                using (var transaction = session.BeginTransaction(IsolationLevel.Serializable))
                {
                    foreach (var student in students)
                    {
                        session.Save(student);
                    }
    
                    transaction.Commit();
                }
            }
    
            vm.TimeSpent = DateTime.Now.Subtract(startTime);
            vm.UploadedStudents 
                = (from student in students
                    select new Student()
                                {
                                    Id = student.Id,
                                    LastName = student.LastName,
                                    FirstName = student.FirstName,
                                    Score = student.Score
                                }).ToList();
            
            return vm;
        }
    }
}

The NHibernate Hilo Data Access Method 

NHibernateHilo\DataModel\TStudent_Hilo.cs defines the data model to insert the students into the [TStudent_Hilo] table.

C#
namespace SQLPerformanceEntityInsert.DataAccessLayer.NHibernateHilo.DataModel
{
    public class TStudent_Hilo
    {
        public virtual int Id { get; set; }
        public virtual string LastName { get; set; }
        public virtual string FirstName { get; set; }
        public virtual int Score { get; set; }
    }
} 

The NHibernate mapping of the TStudent_Hilo class is defined in the TableMapping\TStudentHiloMap.cs file.

C#
using FluentNHibernate.Mapping;
using SQLPerformanceEntityInsert.DataAccessLayer.NHibernateHilo.DataModel;
    
namespace SQLPerformanceEntityInsert.DataAccessLayer.NHibernateHilo.TableMapping
{
    public class TStudentHiloMap : ClassMap<TStudent_Hilo>
    {
        public TStudentHiloMap()
        {
            Id(x => x.Id).Column("Id").GeneratedBy.HiLo("THiloIDGenerator", "Hi",
                "100", "TableName = 'TStudent_Hilo'");
            Map(x => x.LastName).Column("LastName");
            Map(x => x.FirstName).Column("FirstName");
            Map(x => x.Score).Column("Score");
        }
    }
} 

When using NHibernate Hilo, we will need to specify the table used to generate the IDs for the students when mapping the "Id" column. The NHibernateHilo\NHibernateHiloDbAccess.cs file implements the code to insert the students into the [TStudent_Hilo] table using NHibernate Hilo.

C#
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using SQLPerformanceEntityInsert.DataAccessLayer.NHibernateHilo.DataModel;
using SQLPerformanceEntityInsert.DataAccessUtilities.NHibernate;
using SQLPerformanceEntityInsert.ViewModels;
    
namespace SQLPerformanceEntityInsert.DataAccessLayer.NHibernateHilo
{
    public static class NHibernateHiloDbAccess
    {
        private static List<TStudent_Hilo> GetStudentList(HttpPostedFileBase fileStudents)
        {
            var streamStudents = fileStudents.InputStream;
            streamStudents.Position = 0;
            var doc = XDocument.Load(streamStudents);
            var students = (from s in doc.Descendants("student")
                            select new TStudent_Hilo()
                            {
                                LastName = s.Descendants("LastName").First().Value,
                                FirstName = s.Descendants("FirstName").First().Value,
                                Score = Convert.ToInt16(s.Descendants("Score").First().Value)
                            }).ToList();
    
            return students;
        }
    
        public static IndexVm UploadFile(HttpPostedFileBase fileStudents)
        {
            var vm = new IndexVm();
    
            var startTime = DateTime.Now;
    
            var students = GetStudentList(fileStudents);
            using (var session = NHibernateUtility.OpenSession())
            {
                using (var transaction = session.BeginTransaction(IsolationLevel.Serializable))
                {
                    foreach (var student in students)
                    {
                        session.Save(student);
                    }
    
                    transaction.Commit();
                }
            }
    
            vm.TimeSpent = DateTime.Now.Subtract(startTime);
            vm.UploadedStudents 
                = (from student in students
                    select new Student()
                                {
                                    Id = student.Id,
                                    LastName = student.LastName,
                                    FirstName = student.FirstName,
                                    Score = student.Score
                                }).ToList();
            
            return vm;
        }
    }
} 

To insert data into the database using NHibernate or NHibernate Hilo, we will need to initiate the transaction in the code and commit it when all the students are inserted.

The Controller

The controller of this MVC application is implemented in the Controllers\HomeController.cs file.

C#
using System.Web;
using System.Web.Mvc;
using SQLPerformanceEntityInsert.DataAccessLayer.ADONET;
using SQLPerformanceEntityInsert.DataAccessLayer.NHibernateHilo;
using SQLPerformanceEntityInsert.DataAccessLayer.NHibernateIdentity;
using SQLPerformanceEntityInsert.DataAccessUtilities.SQLPerformanceRead.DataAccessUtilities;
using SQLPerformanceEntityInsert.ViewModels;
    
namespace SQLPerformanceEntityInsert.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            var vm = new IndexVm
            {
                AccessMethod = DataAccessMethod.AdoNet,
                TimeSpent = null,
                UploadedStudents = null
            };
    
            return View(vm);
        }
    
        [HttpPost]
        public ActionResult UploadStudents(DataAccessMethod radAccessMethod,
            HttpPostedFileBase fileStudents)
        {
            var vm = (radAccessMethod == DataAccessMethod.AdoNet)
                         ? AdoNetDbAccess.UploadFile(fileStudents)
                         : (radAccessMethod == DataAccessMethod.NHibernateIdentity)
                         ? NHibernateIdentityDbAccess.UploadFile(fileStudents)
                         : NHibernateHiloDbAccess.UploadFile(fileStudents);
            
            vm.AccessMethod = radAccessMethod;
    
            return View("Index", vm);
        }
    }
}   

The action method Index simply loads the main page of the application. When a file is uploaded, the UploadStudents method inserts the students into the database using the chosen data access method. It also passes the uploaded students and the time spent to insert the students to the view page to display.

The View

The example application is a single view MVC application. The view is implemented in the "Views\Home\Index.aspx" file.

ASP.NET
<%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master"
    Inherits="System.Web.Mvc.ViewPage<SQLPerformanceEntityInsert.ViewModels.IndexVm>" %>
<%@ Import Namespace="SQLPerformanceEntityInsert.DataAccessUtilities.SQLPerformanceRead.DataAccessUtilities" %>
    
<asp:Content ID="MainContent" ContentPlaceHolderID="MainContent" runat="server">
<form id="frmMain" method="POST" enctype="multipart/form-data">
<div>
    <label class="textLabel">Data access method</label>
    <span style="margin-right: 10px">
        <input type="radio" name="radAccessMethod"
               value="<%=DataAccessMethod.AdoNet.ToString() %>"
               <% if (Model.AccessMethod == DataAccessMethod.AdoNet)
                  { %> checked="checked" <% } %> />
        <label>ADO.Net</label>
        
        <input type="radio" name="radAccessMethod"
               value="<%=DataAccessMethod.NHibernateIdentity.ToString() %>" 
               <% if (Model.AccessMethod == DataAccessMethod.NHibernateIdentity)
                  { %> checked="checked" <% } %> />
        <label>NHibernate Identity</label>
    
        <input type="radio" name="radAccessMethod"
               value="<%=DataAccessMethod.NHibernateHilo.ToString() %>" 
               <% if (Model.AccessMethod == DataAccessMethod.NHibernateHilo)
                  { %> checked="checked" <% } %> />
        <label>NHibernate Hilo</label>
    </span>
    <label class="textLabel">File to upload</label>
    <span>
        <input type="file" id="fileStudents" name="fileStudents"/>
        <input type="button" id="btnSubmitFile" value="Submit file" />
    </span>
</div>
</form>
    
<%if (Model.TimeSpent != null)
  { %>
    <br/>
    <label>Database Access Time: <span style="color: red">
        <%=Model.TimeSpent%></span></label>
<% } %>    
 
<%if (Model.UploadedStudents != null)
  {%>
    <div id="divStudentSummary" class="divTable">
    <div class="tableHeader">
        <span>StudentId</span>
        <span>Last Name</span>
        <span>First Name</span>
        <span>Score</span>
    </div>
    
    <%foreach (var student in Model.UploadedStudents)
      {%>
        <div>
            <span><%=student.Id %></span>
            <span><%=student.LastName %></span>
            <span><%=student.FirstName %></span>
            <span><%=student.Score%></span>
        </div>
    <% } %>
</div>
<% } %>
</asp:Content>

This MVC view is responsible to upload the XML file and displays the result of the inserted students. The JavaScript code used by this view is as follows:

JavaScript
<script type="text/javascript">
    
    $(document).ready(function () {
        $('#btnSubmitFile').click(function () {
            if ($.trim($('#fileStudents').val()) == '') {
                alert('Please browse a file to upload ...');
                return;
            }

            $('#frmMain').attr('action', fileUploadUrl).submit();
        });
    });
</script>  

Run the Application

Make sure your SQL Server is running and is accessible and make sure the [SQLPerformanceInsert] database is created successfully. We can then run the application.

Image 8

We can choose a data access method and then upload the file to the server. The picture shows the result using the NHibnerate Identity method.

The Comparison Result

Image 9

The above result is the time spent to insert 5000 students by averaging 10 runs. We can see that the NHibernate Hilo indeed runs faster than the NHibernate Identity method by 11 times. But it is slower than the direct ADO.NET method, which runs faster than the NHibernate Identity method by 592 times.

An Additional Problem with NHibernate Hilo

When the example database is first created, we have initiated the [THiloIDGenerator] table as follows:

Image 10

After inserting 5000 students, the [THiloIDGenerator] table has the following result.

Image 11

The Hi value of 50 makes sense because we have inserted 5000 students and for every 100 students, we increment the Hi value by 1. But after inserting 30000 students, I get the following result:

Image 12

What does the Hi value 298 mean? I tried to search around in the internet to find the algorithm that NHibernate Hilo uses to generate the IDs, but I could not find anything helpful. Anyway, if we choose to use NHibernate Hilo to insert data to the database, we have the following problems:

  • If multiple applications try to insert data to the same database table and if one of the applications uses NHibernate Hilo, it leaves other applications no other option. They have to use NHibernate Hilo and they have to specify the same number of the Hilo value in the Hibernate mapping. Otherwise, different applications may generate duplicate IDs. This is a problem when one of the applications requires fast response time, but it is forced to use the slower NHibernate Hilo method.
  • If for some reason, the database administrator needs to add a single record to the table directly for some emergency fixes and the table is being used through NHibernate Hilo by some application, he will need to figure out the Hilo algorithm to create his ID for the record. Even if he finds the correct ID, he will need to get 100 IDs (as configured in this example), and use only 1 of them, resulting in 99 wasted IDs, if he does not keep his record for his IDs, which is an additional duty beyond his already busy days.

Points of Interest

  • This is part 2 of the comparison on Data Access methods in the .NET environment.
  • In this part of the article, we made a performance comparison on inserting data into database tables. The comparison shows that without NHibernate, we can achieve very good performance through stored procedures. If we use NHibernate and we generate the IDs for the data records by identity fields, the performance is pretty bad. Although NHibernate Hilo shows some improvement, it is still significantly slower than the method which does not use NHibernate at all.
  • NHibernate Hilo does show some performance improvement compared with the NHibernate with identity field method, it also creates some other problems, which can be huge maintenance issues once the application is deployed to production.
  • If you are new to these subjects, the example application can show you how to use these methods to insert data to the database.
  • If you are interested in this subject, you can take a look at the part 3 of the article. 
  • I hope you like my postings and I hope this article can help you one way or the other.

History

  • First revision - 8/1/2013.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
I have been working in the IT industry for some time. It is still exciting and I am still learning. I am a happy and honest person, and I want to be your friend.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Member 1033103116-Oct-13 2:35
Member 1033103116-Oct-13 2:35 
QuestionAgain you are comparing apples with oranges Pin
Klaus Luedenscheidt12-Oct-13 18:53
Klaus Luedenscheidt12-Oct-13 18:53 
AnswerRe: Again you are comparing apples with oranges Pin
Dr. Song Li14-Oct-13 4:10
Dr. Song Li14-Oct-13 4:10 
Questiongood idea Pin
M.Ebrahimi.Ahouei12-Oct-13 18:20
M.Ebrahimi.Ahouei12-Oct-13 18:20 
SuggestionLines Pin
Athari11-Sep-13 3:03
Athari11-Sep-13 3:03 
QuestionFaster than dapper Pin
carlosast126-Aug-13 12:55
carlosast126-Aug-13 12:55 
AnswerRe: Faster than dapper Pin
Dr. Song Li26-Aug-13 15:20
Dr. Song Li26-Aug-13 15:20 
GeneralMy vote of 5 Pin
Matin hemmati12-Aug-13 18:21
Matin hemmati12-Aug-13 18:21 
GeneralMy vote of 5 Pin
Shao Voon Wong11-Aug-13 4:04
mvaShao Voon Wong11-Aug-13 4:04 
GeneralRe: My vote of 5 Pin
Dr. Song Li12-Aug-13 3:55
Dr. Song Li12-Aug-13 3:55 
GeneralAnother way Pin
mixirika222-Aug-13 4:22
mixirika222-Aug-13 4:22 
GeneralRe: Another way Pin
Dr. Song Li2-Aug-13 4:33
Dr. Song Li2-Aug-13 4:33 
GeneralInteresting Pin
Espen Harlinn1-Aug-13 9:40
professionalEspen Harlinn1-Aug-13 9:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.