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.
SET NOCOUNT ON
USE [master]
GO
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
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
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
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
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.
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:
It is initiated as follows when we create the database.
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:
It is initiated as follows when we create the database.
The stored procedure [InsertStudentsByStoredProcedure] is created as follows:
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:
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.
- 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.
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.
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
ADO.NET Data Access Method
The file ADONET\AdoNetDbAccess.cs implements the code to insert the students into the database using ADO.NET.
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.
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.
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.
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.
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.
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.
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.
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.
<%@ 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:
<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.
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
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:
After inserting 5000 students, the [THiloIDGenerator] table has the following result.
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:
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.
