Introduction
This is the part 3 of a comparison on Data Access Methods in the .NET environment. In this part, I will put everything together. I will show you a moderately complex example that performs insert, update, and delete operations in a single database trip. I will make comparisons between the ADO.NET, and NHibernate. Generally speaking, NHibernate can be slow when accessing the database, particularly when we have a large data set. In this example, I will show you how slow it is when compared with using ADO.NET directly. 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 the part 1 and the part 2 of the article, I have made some comparisons on the performance of the read operations and the insert operations. If you are not already an expert on NHibernate, I strongly recommend you to take a look at the part 1 and the part 2, particularly the part 1. In the part 1, I gave an overview of the structure of the different data access methods and discussed how to set up the NHibernate environment in a .Net application in detail. In this part of the article, I will directly jump to the database operations using both ADO.NET and NHibernate and make the comparisons on the data access performance.
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 a SQL Server and you will need to have administrative permissions to it. 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')
DROP LOGIN [TestUser]
GO
EXEC sp_addlogin @loginame = 'TestUser', @passwd = 'Password123';
GO
IF EXISTS
(SELECT name FROM sys.databases
WHERE name = N'SQLPerformanceInsertUpdateDelete')
BEGIN
ALTER DATABASE [SQLPerformanceInsertUpdateDelete] SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
DROP DATABASE [SQLPerformanceInsertUpdateDelete]
END
GO
CREATE DATABASE SQLPerformanceInsertUpdateDelete
GO
USE [SQLPerformanceInsertUpdateDelete]
GO
sp_grantdbaccess 'TestUser'
GO
sp_addrolemember 'db_datareader', 'TestUser'
GO
sp_addrolemember 'db_datawriter', 'TestUser'
GO
CREATE TABLE [dbo].[TStudent](
[ID] [int] NOT NULL,
[LastName] [varchar](50) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[EnrollmentTime] [datetime] NOT NULL,
CONSTRAINT [PK_TStudent] 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].[TCourse](
[ID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[CreditHours] [int] NOT NULL,
CONSTRAINT [PK_TCourse] 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]
CREATE UNIQUE NONCLUSTERED INDEX [IX_Name] ON [dbo].[TCourse]
(
[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TStudentScore](
[StudentID] [int] NOT NULL,
[CourseID] [int] NOT NULL,
[Score] [int] NOT NULL,
[LastUpdated] [datetime] NOT NULL,
CONSTRAINT [PK_TStudentScore] PRIMARY KEY CLUSTERED
(
[StudentID] ASC,
[CourseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[TStudentScore] WITH CHECK ADD
CONSTRAINT [FK_TStudentScore_TScore] FOREIGN KEY([CourseID])
REFERENCES [dbo].[TCourse] ([ID])
ALTER TABLE [dbo].[TStudentScore] WITH CHECK ADD
CONSTRAINT [FK_TStudentScore_TStudent] FOREIGN KEY([StudentID])
REFERENCES [dbo].[TStudent] ([ID])
GO
CREATE TYPE [dbo].[TStudentTableType] AS TABLE(
[ID] [int] NOT NULL,
[LastName] [varchar](50) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[EnrollmentTime] DATETIME NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
CREATE TYPE [dbo].[TStudentScoreTableType] AS TABLE
(
StudentID INT NOT NULL,
CourseName VARCHAR(50) NOT NULL,
Score INT NOT NULL,
LastUpdated DATETIME NOT NULL
)
GO
INSERT INTO TCourse VALUES(1, 'English', 3)
INSERT INTO TCourse VALUES(2, 'Math', 5)
INSERT INTO TCourse VALUES(3, 'Biology', 4)
INSERT INTO TCourse VALUES(4, 'Music', 3)
INSERT INTO TCourse VALUES(5, 'Basketball', 2)
GO
CREATE PROCEDURE DBO.GetStudents
AS
BEGIN
SET NOCOUNT ON;
SELECT S.ID StudentId, s.LastName, s.FirstName,
AVG(SS.Score) AverageScore,
SUM(CASE WHEN SS.Score >= 60 THEN C.CreditHours ELSE 0 END) Credits
FROM TStudent S
LEFT JOIN TStudentScore SS ON S.ID = SS.StudentID
LEFT JOIN TCourse C ON SS.CourseID = C.ID
GROUP BY S.ID, s.LastName, s.FirstName
ORDER BY S.ID
END
GO
CREATE PROCEDURE [dbo].[InsertUpdateDeleteStudents]
@Students TStudentTableType READONLY,
@StudentScores TStudentScoreTableType READONLY
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #TempStudentScore
(
StudentID INT NOT NULL,
CourseID INT NOT NULL,
Score INT NOT NULL,
LastUpdated DATETIME NOT NULL
)
CREATE INDEX #IX_StudentID ON #TempStudentScore (StudentID)
CREATE INDEX #IX_CourseID ON #TempStudentScore (CourseID)
INSERT INTO #TempStudentScore
SELECT SS.StudentID, C.ID, SS.Score, SS.LastUpdated
FROM @StudentScores SS INNER JOIN TCourse C ON SS.CourseName = C.Name
BEGIN TRANSACTION
BEGIN TRY
DELETE SS
FROM TStudentScore SS LEFT JOIN #TempStudentScore temp
ON SS.StudentID = temp.StudentID AND SS.CourseID = temp.CourseID
WHERE temp.StudentID IS NULL
DELETE TS
FROM TStudent TS LEFT JOIN @Students S
ON TS.ID = S.ID
WHERE S.ID IS NULL
UPDATE TS SET LastName = S.LastName, FirstName = S.FirstName,
EnrollmentTime = S.EnrollmentTime
FROM TStudent TS INNER JOIN @Students S ON TS.ID = S.ID
UPDATE TSS SET CourseID = temp.CourseID, LastUpdated = temp.LastUpdated
FROM TStudentScore TSS INNER JOIN #TempStudentScore temp
ON TSS.StudentID = temp.StudentID AND TSS.CourseID = temp.CourseID
INSERT INTO TStudent
SELECT S.ID, S.LastName, S.FirstName, S.EnrollmentTime
FROM @Students S LEFT JOIN TStudent TS ON S.ID = TS.ID
WHERE TS.ID IS NULL
INSERT INTO TStudentScore
SELECT Temp.StudentID, Temp.CourseID, Temp.Score, Temp.LastUpdated
FROM #TempStudentScore Temp LEFT JOIN TStudentScore SS
ON Temp.StudentID = SS.StudentID AND Temp.CourseID = SS.CourseID
WHERE SS.StudentID IS NULL
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
RAISERROR ('Unable to save students', 16, 1)
RETURN
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END
GO
GRANT EXECUTE ON TYPE::[dbo].[TStudentTableType] TO TestUser
GRANT EXECUTE ON TYPE::[dbo].[TStudentScoreTableType] TO TestUser
GRANT EXECUTE ON OBJECT::[dbo].[GetStudents] TO TestUser
GRANT EXECUTE ON OBJECT::[dbo].[InsertUpdateDeleteStudents] TO TestUser
GO
ALTER DATABASE [SQLPerformanceInsertUpdateDelete] SET MULTI_USER
GO
PRINT 'SQLPerformanceInsertUpdateDelete database is created' Upon a successful run of the script, we will have a database named [SQLPerformanceInsertUpdateDelete] in the database server. The script also created the proper indices and referential relations among the tables.
In the [SQLPerformanceInsertUpdateDelete] database, we have 3 tables and 2 stored procedures. The database diagram of the database [SQLPerformanceInsertUpdateDelete] is shown as the following:
When creating the database, the [TCourse] table is filled with the following data:
The stored procedure [GetStudents] is created as the following. It is used by the example application to retrieve the information from the database to display by the example application.
CREATE PROCEDURE [dbo].[GetStudents]
AS
BEGIN
SET NOCOUNT ON;
SELECT S.ID StudentId, s.LastName, s.FirstName,
AVG(SS.Score) AverageScore,
SUM(CASE WHEN SS.Score >= 60 THEN C.CreditHours ELSE 0 END) Credits
FROM TStudent S
LEFT JOIN TStudentScore SS ON S.ID = SS.StudentID
LEFT JOIN TCourse C ON SS.CourseID = C.ID
GROUP BY S.ID, s.LastName, s.FirstName
ORDER BY S.ID
END
The stored procedure [InsertUpdateDeleteStudents] is used by the application to upload information to the database.
CREATE PROCEDURE [dbo].[InsertUpdateDeleteStudents]
@Students TStudentTableType READONLY,
@StudentScores TStudentScoreTableType READONLY
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #TempStudentScore
(
StudentID INT NOT NULL,
CourseID INT NOT NULL,
Score INT NOT NULL,
LastUpdated DATETIME NOT NULL
)
CREATE INDEX #IX_StudentID ON #TempStudentScore (StudentID)
CREATE INDEX #IX_CourseID ON #TempStudentScore (CourseID)
INSERT INTO #TempStudentScore
SELECT SS.StudentID, C.ID, SS.Score, SS.LastUpdated
FROM @StudentScores SS INNER JOIN TCourse C ON SS.CourseName = C.Name
BEGIN TRANSACTION
BEGIN TRY
DELETE SS
FROM TStudentScore SS LEFT JOIN #TempStudentScore temp
ON SS.StudentID = temp.StudentID AND SS.CourseID = temp.CourseID
WHERE temp.StudentID IS NULL
DELETE TS
FROM TStudent TS LEFT JOIN @Students S
ON TS.ID = S.ID
WHERE S.ID IS NULL
UPDATE TS SET LastName = S.LastName, FirstName = S.FirstName,
EnrollmentTime = S.EnrollmentTime
FROM TStudent TS INNER JOIN @Students S ON TS.ID = S.ID
UPDATE TSS SET CourseID = temp.CourseID, LastUpdated = temp.LastUpdated
FROM TStudentScore TSS INNER JOIN #TempStudentScore temp
ON TSS.StudentID = temp.StudentID AND TSS.CourseID = temp.CourseID
INSERT INTO TStudent
SELECT S.ID, S.LastName, S.FirstName, S.EnrollmentTime
FROM @Students S LEFT JOIN TStudent TS ON S.ID = TS.ID
WHERE TS.ID IS NULL
INSERT INTO TStudentScore
SELECT Temp.StudentID, Temp.CourseID, Temp.Score, Temp.LastUpdated
FROM #TempStudentScore Temp LEFT JOIN TStudentScore SS
ON Temp.StudentID = SS.StudentID AND Temp.CourseID = SS.CourseID
WHERE SS.StudentID IS NULL
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
RAISERROR ('Unable to save students', 16, 1)
RETURN
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END The 2 user defined table types are used by the stored procedure [InsertUpdateDeleteStudents] to receive the information from the application.
CREATE TYPE [dbo].[TStudentTableType] AS TABLE(
[ID] [int] NOT NULL,
[LastName] [varchar](50) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[EnrollmentTime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
CREATE TYPE [dbo].[TStudentScoreTableType] AS TABLE(
[StudentID] [int] NOT NULL,
[CourseName] [varchar](50) NOT NULL,
[Score] [int] NOT NULL,
[LastUpdated] [datetime] NOT NULL
)
The Sample Data to Upload
To compare the performance of the data access methods, I included 2 sample data files in this article.
The goal of this article is to create a .Net web application that uploads the xml files to the database. Upon a successful upload, the data in the database will match the information in the xml file, which means the following:
- If a student and the scores are in the database but not in the xml file, they will be deleted;
- If a student and the scores are not in the database but in the xml file, they will be inserted;
- If a student and the scores are in the database and in the xml file but the information are different, they will be updated to match the information in the xml file.
The example web application will use both ADO.Net and NHibernate to access the database and compare the performance.
The MVC Web Application
As in the part 1 and part 2 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 implement some very basic functionality to support the database operations by each data access method. The files are almost the same as what have been discussed in the part 1 of the article. I will strongly recommend you to take a look at the part 1, where I gave some detailed discussion on the architectural structure of the different data access methods and how to set up the environment in the application to use ADO.Net, NHibernate and Dapper. I will use Dapper to load the data from the database to display in this application. The "DataAccessMethod.cs" file implements an "enum" that represents the data access methods to upload the students to the database.
namespace SQLPerformanceInsertUpdateDelete.DataAccessUtilities
{
public enum DataAccessMethod { AdoNet, NHibernate };
} In this article, I will compare the performance on insert, update, and delete database operations between the ADO.Net and NHibernate. The database connection string is kept in the "Web.config" file.
<appSettings>
<add key="ConnectionString"
value="Data Source=localhost;Initial Catalog=SQLPerformanceInsertUpdateDelete;
User Id=TestUser;Password=Password123;"/>
</appSettings> The View Models
The view models of the application are implemented in the ViewModels\IndexVM.cs file.
using System;
using System.Collections.Generic;
using SQLPerformanceInsertUpdateDelete.DataAccessUtilities;
namespace SQLPerformanceInsertUpdateDelete.ViewModels
{
public class Student
{
public int StudentId { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
public int AverageScore { get; set; }
public int Credits { 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 by the insert, update, and delete operations on 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, update, and delete the students on the database using ADO.NET.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using SQLPerformanceInsertUpdateDelete.DataAccessUtilities.ADONET;
using SQLPerformanceInsertUpdateDelete.ViewModels;
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.ADONET
{
public static class AdoNetDbAccess
{
private static DataSet GetStudentList(HttpPostedFileBase fileStudents)
{
var studentTable = new DataTable("studentTable");
studentTable.Columns.Add("ID",
System.Type.GetType("System.Int32"));
studentTable.Columns.Add("LastName",
System.Type.GetType("System.String"));
studentTable.Columns.Add("FirstName",
System.Type.GetType("System.String"));
studentTable.Columns.Add("EnrollmentTime",
System.Type.GetType("System.DateTime"));
var studentCourseTable = new DataTable("studentCourseTable");
studentCourseTable.Columns.Add("StudentID",
System.Type.GetType("System.Int32"));
studentCourseTable.Columns.Add("CourseName",
System.Type.GetType("System.String"));
studentCourseTable.Columns.Add("Score",
System.Type.GetType("System.Int32"));
studentCourseTable.Columns.Add("LastUpdated",
System.Type.GetType("System.DateTime"));
var streamStudents = fileStudents.InputStream;
streamStudents.Position = 0;
var doc = XDocument.Load(streamStudents);
var students = doc.Descendants("student");
foreach (var student in students)
{
var studentrow = studentTable.NewRow();
var studentId = Convert.ToInt32(student.Attribute("Id").Value);
studentrow["Id"] = studentId;
studentrow["LastName"] = student.Descendants("LastName")
.First().Value;
studentrow["FirstName"] = student.Descendants("FirstName")
.First().Value;
studentrow["EnrollmentTime"] = student
.Descendants("EnrollmentTime").First().Value;
studentTable.Rows.Add(studentrow);
var scores = student.Descendants("score");
foreach (var score in scores)
{
var studentscorerow = studentCourseTable.NewRow();
studentscorerow["StudentID"] = studentId;
studentscorerow["CourseName"] = score.Attribute("Class").Value;
studentscorerow["Score"] = score.Descendants("Score").First().Value;
studentscorerow["LastUpdated"] = score
.Descendants("LastUpdated").First().Value;
studentCourseTable.Rows.Add(studentscorerow);
}
}
var set = new DataSet();
set.Tables.Add(studentTable);
set.Tables.Add(studentCourseTable);
return set;
}
public static IndexVm UploadFile(HttpPostedFileBase fileStudents)
{
var vm = new IndexVm();
var startTime = DateTime.Now;
var cmd = new SqlCommand
{
CommandType = CommandType.StoredProcedure,
CommandText = "InsertUpdateDeleteStudents"
};
var set = GetStudentList(fileStudents);
cmd.Parameters.AddWithValue("@Students",
set.Tables["studentTable"]);
cmd.Parameters.AddWithValue("@StudentScores",
set.Tables["studentCourseTable"]);
AdoNetUtility.ExecuteCommand(cmd);
vm.TimeSpent = DateTime.Now.Subtract(startTime);
return vm;
}
}
} The UploadFile method calls the stored procedure [InsertUpdateDeleteStudents] in a single batch. It changes the data in both [TStudent] and [TStudentScore] tables. The transaction is controlled inside the stored procedure, so if it is successful, all the students and scores are changed in the database. If the transaction fails, none is changed.
The NHibernate Data Access Method
To use the NHibernate data access method, we need first create the data model classes for each database table that we will need to work on.
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.DataModel
{
public class TCourse
{
public virtual int Id { get; set; }
public virtual string Name { get; set; }
public virtual int CreditHours { get; set; }
}
}
using System;
using System.Collections.Generic;
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.DataModel
{
public class TStudent
{
public virtual int Id { get; set; }
public virtual string LastName { get; set; }
public virtual string FirstName { get; set; }
public virtual DateTime EnrollmentTime { get; set; }
public virtual IList<TStudentScore> StudentScores { get; set; }
}
}
using System;
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.DataModel
{
public class TStudentScore
{
public virtual int StudentId { get; set; }
public virtual int CourseId { get; set; }
public virtual int Score { get; set; }
public virtual DateTime LastUpdated { get; set; }
public virtual TCourse Course { get; set; }
public override bool Equals(object obj)
{
var other = obj as TStudentScore;
if (ReferenceEquals(null, other)) return false;
if (ReferenceEquals(this, other)) return true;
return StudentId == other.StudentId &&
CourseId == other.CourseId;
}
public override int GetHashCode()
{
unchecked
{
int hash = GetType().GetHashCode();
hash = (hash * 31) ^ StudentId.GetHashCode();
hash = (hash * 31) ^ CourseId.GetHashCode();
return hash;
}
}
}
} We will also need to create the NHibernate mapping classes that connect each data model class to each database table.
using FluentNHibernate.Mapping;
using SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.DataModel;
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.TableMapping
{
public class TCourseMap : ClassMap<TCourse>
{
public TCourseMap()
{
Table("TCourse");
Id(x => x.Id).Column("ID");
Map(x => x.Name).Column("Name").CustomSqlType("varchar").Length(50);
Map(x => x.CreditHours).Column("CreditHours");
ReadOnly();
}
}
}
using FluentNHibernate.Mapping;
using SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.DataModel;
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.TableMapping
{
public class TStudentMap : ClassMap<TStudent>
{
public TStudentMap()
{
Table("TStudent");
Id(x => x.Id).Column("ID").GeneratedBy.Assigned();
Map(x => x.LastName).Column("LastName").CustomSqlType("varchar").Length(50);
Map(x => x.FirstName).Column("FirstName").CustomSqlType("varchar");
Map(x => x.EnrollmentTime).Column("EnrollmentTime");
HasMany(x => x.StudentScores).KeyColumn("StudentId").Inverse().ReadOnly();
}
}
}
using FluentNHibernate.Mapping;
using SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.DataModel;
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.TableMapping
{
public class TStudentScoreMap : ClassMap<TStudentScore>
{
public TStudentScoreMap()
{
Table("TStudentScore");
CompositeId().KeyProperty(x => x.StudentId, "StudentId")
.KeyProperty(x => x.CourseId, "CourseId");
Map(x => x.Score).Column("Score");
Map(x => x.LastUpdated).Column("LastUpdated");
References(x => x.Course).ForeignKey("CourseId").Column("CourseId").ReadOnly();
}
}
} Build upon the data model classes and the mapping classes, the "NHibernateDbAccess.cs" file implements the database operations to insert, update, and delete the students and the scores in the database using NHibernate.
using System;
using System.Collections.Generic;
using System.Web;
using System.Xml.Linq;
using SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.DataModel;
using SQLPerformanceInsertUpdateDelete.DataAccessUtilities.NHibernate;
using SQLPerformanceInsertUpdateDelete.ViewModels;
using System.Linq;
using NHibernate.Linq;
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate
{
public class NHibernateDbAccess
{
public static List<TStudent> GetUploadedStudentList(HttpPostedFileBase fileStudents)
{
var students = new List<TStudent>();
List<TCourse> courses;
using (var session = NHibernateUtility.OpenSession())
{
courses = session.Query<TCourse>().ToList();
}
var streamStudents = fileStudents.InputStream;
streamStudents.Position = 0;
var doc = XDocument.Load(streamStudents);
var xmlstudents = doc.Descendants("student");
foreach (var xmlstudent in xmlstudents)
{
var studentId = Convert.ToInt32(xmlstudent.Attribute("Id").Value);
var student = new TStudent()
{
Id = studentId,
LastName = xmlstudent.Descendants("LastName").First().Value,
FirstName = xmlstudent.Descendants("FirstName").First().Value,
EnrollmentTime =
Convert.ToDateTime(xmlstudent
.Descendants("EnrollmentTime").First().Value)
};
var studentScores = new List<TStudentScore>();
student.StudentScores = studentScores;
students.Add(student);
var xmlscores = xmlstudent.Descendants("score");
foreach (var xmlscore in xmlscores)
{
var course = (from c in courses
where c.Name == xmlscore.Attribute("Class").Value
select c).Single();
var studentScore = new TStudentScore()
{
StudentId = studentId,
CourseId = course.Id,
Course = course,
Score = Convert.ToInt32(xmlscore.Descendants("Score")
.First().Value),
LastUpdated =
Convert.ToDateTime(xmlscore.Descendants("LastUpdated")
.First().Value)
};
studentScores.Add(studentScore);
}
}
return students;
}
private static List<TStudent> GetDbStudentList()
{
List<TStudent> students;
using (var session = NHibernateUtility.OpenSession())
{
students = session.Query<TStudent>()
.FetchMany(s => s.StudentScores).ToList();
}
return students;
}
public static IndexVm UploadFile(HttpPostedFileBase fileStudents)
{
var vm = new IndexVm();
var startTime = DateTime.Now;
var xmlStudents = GetUploadedStudentList(fileStudents);
var dbStudents = GetDbStudentList();
using (var session = NHibernateUtility.OpenSession())
{
using (var transaction = session.BeginTransaction())
{
foreach (var dbStudent in dbStudents)
{
var studentId = dbStudent.Id;
var xmlStudent = (from s in xmlStudents
where s.Id == studentId
select s).SingleOrDefault();
if (xmlStudent == null)
{
foreach (var dbscore in dbStudent.StudentScores)
{
session.Delete(dbscore);
}
session.Delete(dbStudent);
continue;
}
var xmlScores = xmlStudent.StudentScores;
foreach (var dbscore in dbStudent.StudentScores)
{
var courseId = dbscore.CourseId;
var xmlScore = (from s in xmlScores
where s.StudentId == studentId
&& s.CourseId == courseId
select s).SingleOrDefault();
if (xmlScore == null)
{
session.Delete(dbscore);
}
}
}
foreach (var xmlStudent in xmlStudents)
{
session.SaveOrUpdate(xmlStudent);
foreach (var xmlScore in xmlStudent.StudentScores)
{
session.SaveOrUpdate(xmlScore);
}
}
transaction.Commit();
}
}
vm.TimeSpent = DateTime.Now.Subtract(startTime);
return vm;
}
}
} The Dapper Data Access Method
In this article, I use Dapper to read the database only. The "DapperDbAccess.cs" implements the code to load the student information from the database to be displayed on the web application.
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using SQLPerformanceInsertUpdateDelete.DataAccessUtilities.ADONET;
using SQLPerformanceInsertUpdateDelete.DataAccessUtilities.Dapper;
using SQLPerformanceInsertUpdateDelete.ViewModels;
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.Dapper
{
public static class DapperDbAccess
{
public static List<Student> LoadStudentSummaries()
{
List<Student> students;
using (var cn = AdoNetUtility.GetConnection())
{
cn.Open();
students
= cn.Query<Student>("GetStudents",
commandType: CommandType.StoredProcedure).ToList();
}
return students;
}
}
} The Dapper is very lightweight and efficient. If you are interested to find how Dapper performs, you can take a look at the part 1 of the article.
The Controller
The controller of this MVC application is implemented in the Controllers\HomeController.cs file.
using System.Web;
using System.Web.Mvc;
using SQLPerformanceInsertUpdateDelete.ActionFilters;
using SQLPerformanceInsertUpdateDelete.DataAccessLayer.ADONET;
using SQLPerformanceInsertUpdateDelete.DataAccessLayer.Dapper;
using SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate;
using SQLPerformanceInsertUpdateDelete.DataAccessUtilities;
using SQLPerformanceInsertUpdateDelete.ViewModels;
namespace SQLPerformanceInsertUpdateDelete.Controllers
{
[HandleError]
[NoCache]
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)
: NHibernateDbAccess.UploadFile(fileStudents);
vm.UploadedStudents = DapperDbAccess.LoadStudentSummaries();
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 performs the database operations using the chosen data access method. It also passes the student information in the database and the time spent to insert, update and delete 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#" Inherits="System.Web.Mvc.ViewPage<dynamic>" %>
<%@ Import Namespace="SQLPerformanceInsertUpdateDelete.DataAccessUtilities" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>SQL Access Performance - Insert Update Delete ALL in One!</title>
<link href="<%=Url.Content("~/Content/Site.css") %>"
rel="stylesheet" type="text/css" />
<script src="<%=Url.Content("~/Scripts/jquery-1.8.2.min.js") %>"
type="text/javascript"></script>
</head>
<body>
<div id="container">
<div id="header">
<h1>SQL Access Performance - Insert Update Delete ALL in One!</h1>
</div>
<div id="content">
<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.NHibernate.ToString()%>"
<%if (Model.AccessMethod == DataAccessMethod.NHibernate)
{ %> checked="checked" <% } %> />
<label>NHibernate</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>
<div id="divResult">
<%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>Average Score</span>
<span>Credits</span>
</div>
<% foreach (var student in Model.UploadedStudents)
{%>
<div>
<span><%=student.StudentId%></span>
<span><%=student.LastName%></span>
<span><%=student.FirstName%></span>
<span><%=student.AverageScore%></span>
<span><%=student.Credits%></span>
</div>
<% }%>
</div>
<% }%>
</div>
</div>
<div id="footer">Copyright © SL. 2012</div>
</div>
</body>
</html> The JavaScript code used by this view is the following.
<script type="text/javascript">
$(document).ready(function () {
var clearResult = function () {
$('#divResult').html('');
};
$('#btnSubmitFile').click(function () {
if ($.trim($('#fileStudents').val()) == '') {
alert('Please browse a file to upload ...');
return;
}
clearResult();
$('#frmMain').attr('action', fileUploadUrl).submit();
});
$('input[name=radAccessMethod]').change(function () {
clearResult();
});
});
</script> Run the Application
Make sure your SQL Server is running and is accessible and make sure the [SQLPerformanceInsertUpdateDelete] 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 ADO.Net method.
The Comparison Result
Averaging the time spent for 10 times, I got the above result on my computer, which shows that the ADO.Net method is about 150 times faster than the NHibernate method. If you have time, you may want to repeat my experiment. You may also try to create larger xml files to upload more students to see how the comparison result goes.
Points of Interest
- I finally finished the part 3 of the comparison on the data access methods in the .Net environment. As I mentioned earlier, although the examples are in .Net and SQL servers, the concept can be definitely applied to other environments, such as Java and Oracle.
- If you take your time to take a look at the part 1 and part 2 of the article, you will find that the reading of this part becomes so much easier.
- In the part 1 of the article, I compared the performance on read operations among the different methods. The comparison showed that Dapper is a very efficient ORM tool in read operations.
- In the part 2 of the article, I compared the performance on the insert operations between the ADO.Net and NHibernate. I also showed you the NHibernate Hilo, and the problems that it brought to us.
- In this part of the article, I put the insert, update, and delete operations together and compared the performance between the ADO.Net and NHibernate.
- In all the comparisons, the ADO.Net outperformed the NHibernate with some very significant margins.
- To use the data access methods, you do not need to do any special configurations with ADO.Net. You can also use Dapper easily. But if you want to use NHibernate, you will need to include many additional DLLs. Sometimes, getting these DLLs may not be easy.
- I hope you like my postings and I hope this article can help you one way or the other.
History
First Revision - 10/9/2013
