Introduction
This is the part 1 of the comparison on Data Access methods in the .NET environment. In this part, I will be focusing on read operations. I will make comparisons among
ADO.NET, Dapper, and NHibernate.
Although the comparison is in the .NET Environment on a SQL Server, the concept and the result should be applicable to other environments such as Java and Oracle.
Background
Many years ago, when I went to any website, I was so pleased that I could just sit in my room and get information around the world. The computer that I used was of course
by any standard an extremely old computer. These extremely old computers have long gone into trash cans and I have upgraded my computer. The new computer has at least
hundreds times more memory, hundreds times more hard drive space, and are many times faster CPU (Moore's law).
The old computer had only one CPU core, and a discount laptop that I can buy from Walmart today can easily have at least a few cores. The internet speed also has
had
a very impressive increase over the years. The web servers
and database servers have both improved as much as my personal computers. Solid state hard-drives
are no longer uncommon on these servers. These are all exciting human achievements that we should all be proud of. But when I sit in front of the modern
computer to access some websites through the much improved internet, I am so surprised that the websites that serve the same functions many years ago do not get much faster.
At least not fast enough to match the improvements on the infrastructure. Some of these websites are in fact hosted by some world class companies, such as super
large banks, insurance companies, and wireless phone service providers.
From what I can see, there are many reasons why websites do not get the speed that
they deserve, which include but not limited to the following:
- Excessive and uncontrolled use of CSS, images, and JavaScript. These files are not cached
properly and not loaded in an efficient fashion, which results in unnecessary round trips to the server.
- Improper choice and excessive use of asynchronous or synchronous AJAX calls resulting in
an exaggerated number of round trips to the server.
- Rigid, unconditional, and excessive use of design patterns that do not favor performance.
- And of course, many other factors.
When talking about the speed of a website, we should always keep in mind that a simple functional enterprise-level website is supported by at least two servers,
the web server and the database server. In many cases, the choice of the method to access the database server from the web server can result in significant performance differences.
In this article, I will show you how significant the difference is with running examples. If you take a look
at this link, you will find that there are simply too many ways to access the database.
In this article, I will be focusing on the most commonly used ones in the .NET
environment, namely ADO.NET,
NHibernate, and Dapper. I will be using
MVC web applications to show you how to use them and make performance comparisons among them.
- In the .NET environment, ADO.NET is the default data access method. In fact if you only want to access
SQL Server databases, all the assemblies that you need are included in the .NET
Framework,
and you do not need to carry any additional DLLs when you deploy your applications. In a typical application using ADO.NET,
stored procedures
are commonly used to group certain programming logic in the database servers,
which provides separation of concerns and some performance advantages. It also provides
additional security benefits against SQL injections. If an application accesses the database through
stored procedures exclusively, the database administrators can maximize the security of the database by only granting the application the execute permission
to these stored procedures, but denying the access to other database objects such as tables and views.
- The data returned through ADO.NET is in the form of either a
DataReader
or a DataSet. This is sufficient in most cases. But in an MVC application,
people normally prefer to pass the data from the controller to the view in the form
of view models,
which gives programmers the advantage of
intellisense.
Dapper was introduced to make this transfer for us. As the creators of the
Dapper clearly states, the only purpose of Dapper
is to help programmers to make this transfer.
Dapper is very light-weight. It is implemented
as a set of extension methods around
the
IDbConnection
interface. Since Dapper is just a set of extension methods, you can use all the functions
that the ADO.NET connection object gives
you, but take the advantage of Dapper's object mapping capability. - The design goal of NHibernate is different from
that of Dapper. It is
designed to completely cover ADO.NET. It is very heavy weight. If you want to use Nhibernate, you will need to add a few additional DLLs to your application and set up
your mappings in either XML or C# classes depending on if you use Fluent NHibernate. The examples come with this article
use Fluent NHibernate. To overcome some of the shortcomings,
NHibernate introduced a lot of extensions, including but not limited
to Entity Hilo
and Hibernate Query Language. In part 2 of this article,
I will spend some time on Entity Hilo
to show you what it is and what it gives to us. I will not get into Hibernate
Query Language, since it is a more complex and redundant version of the well-established ANSI SQL.
For many new programmers, setting up the NHibernate environment can be a challenge.
In this article, I will show you the simplest method that I found when I prepare the example applications. I hope the method still works at the time
you read this article. Just keep in mind, the only way to communicate with a database server is SQL,
and ADO.NET is the default method used to connect to the database servers in the .NET environment. When connecting to database servers,
NHibernate does use ADO.NET, regardless if the NHibernate document tells us or not.
The Example Database
Part one of this article serves two purposes. It will show you how to use the three data access methods in the applications, and it also does a comparison
on the performance of a simple read operation. The examples in this article use Visual Studio 2010 and SQL
Server 2008 R2. If you want to repeat the examples,
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 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'SQLPerformanceRead')
BEGIN
ALTER DATABASE [SQLPerformanceRead] SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
DROP DATABASE [SQLPerformanceRead]
END
GO
CREATE DATABASE SQLPerformanceRead
GO
USE [SQLPerformanceRead]
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]
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 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])
DECLARE @i AS INT
DECLARE @j AS INT
DECLARE @NoOfStudents AS INT
SET @NoOfStudents = 5000
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)
SET @i = 1
WHILE @i <= @NoOfStudents
BEGIN
INSERT INTO TStudent VALUES
(@i, 'L Name No.' + CAST(@i AS VARCHAR(4)),
'F Name No.' + CAST(@i AS VARCHAR(4)),
DATEADD (y , -1 , GETDATE()))
SET @i = @i + 1
END
SET @i = 1
WHILE @i <= @NoOfStudents
BEGIN
SET @j = 1
WHILE @j <= 5
BEGIN
INSERT INTO TStudentScore
VALUES(@i, @j, ROUND(40 + 60*RAND(), 0), GETDATE())
SET @j = @j + 1
END
SET @i = @i + 1
END
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.GetStudentScores
@StudentId AS INT
AS
BEGIN
SET NOCOUNT ON;
SELECT SS.CourseID CourseId, C.Name CourseName,
C.CreditHours CreditHours, SS.Score
FROM TStudentScore SS
LEFT JOIN TCourse C ON SS.CourseID = C.ID
WHERE SS.StudentID = @StudentId
END
GO
GRANT EXECUTE ON OBJECT::[dbo].[GetStudents] TO TestUser
GRANT EXECUTE ON OBJECT::[dbo].[GetStudentScores] TO TestUser
GO
ALTER DATABASE [SQLPerformanceRead] SET MULTI_USER
GO
PRINT 'SQLPerformanceRead database is created'
Upon successful runnng of the script, we will have a database named [SQLPerformanceRead] in SQL
Server.
- The [SQLPerformanceRead] database has three tables and two stored procedures. The script also adds proper indexes and foreign key restrictions on the tables.
- Besides creating the tables, the script also adds some data into the tables. We will be using the data in the tables to do the performance comparisons.
- The script also creates a user name/password pair "TestUser/Password123" and grants it the required permissions to access the stored procedures and the tables.
The example application will be using this user name to access the database.
If you run the stored procedure [GetStudents], you will get the following result,
and this will be the data for the application to read and display in the web browser.
The [TStudent] table keeps the basic information of 5000 students, and the [TCourse] table keeps total 5 courses with the course name and the credit hours.
The [TStudentScore] table hooks up the [TStudent] table and the [TCourse] table to keep the score for each student on each course. The [GetStudents] stored procedure
lists all the students, aggregates the average score for each student, and calculates the total credits that the student gets. For each course, only when the student gets
a score over 60, he/she gets the credits. The example application also loads the courses that each student takes and the score for each class shown in the following picture
by running the stored procedure [GetStudentScores].
The Example MVC Application
The example MVC application is developed in Visual Studio 2010. I am well aware that the current MVC version is 4.
But I am also aware that not everyone is using MVC 4, so this application is developed with MVC 2, so most people can download and run the application without many problems.
- The "Controllers\HomeController.cs" file implements the MVC application's controller.
- The "ViewModels\StudentCoursesVm.cs" and "ViewModels\StudentSummariesVm.cs" files implement the application's view model.
- The files in the "Views\Home" folder are the MVC views.
The database connection string is saved in the application's "web.config"
file in the "appSettings" section.
"Data Source=localhost;Initial Catalog=SQLPerformanceRead;User Id=TestUser;Password=Password123"
Before you start working the application, we will need to enable the application to use all the three data access methods. All the fundamental pieces to use these data access methods
are implemented in the "DataAccessUtilities" folder.
The Data Access Utilities
Each of the data access methods has some helper classes or initial setup utilities implemented in the corresponding folder.
The "DataAccessMethod.cs" file defines some "enum"
that will be used across the application to identify the data access method used to load the data from the database.
namespace SQLPerformanceRead.DataAccessUtilities
{
public enum DataAccessMethod
{ AdoNet, Dapper, NHibernateDefault, NHibernateImproved };
}
The ADO.NET Utilities
As we have talked about earlier, ADO.NET is part of the .NET Framework. If we want to connect to a SQL
Server database only, we do not need any special configuration
to use ADO.NET. But to make our later programming easier, I wrote some helper methods in the "ADONET\ADONetUtility.cs" file.
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace SQLPerformanceRead.DataAccessUtilities.ADONET
{
public static class AdoNetUtility
{
private static readonly string ConnectionString;
static AdoNetUtility()
{
ConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
}
private static SqlConnection GetConnection()
{
return new SqlConnection
{
ConnectionString = ConnectionString
};
}
public static void ExecuteCommand(SqlCommand cmd)
{
using (var connection = GetConnection())
{
cmd.Connection = connection;
connection.Open();
cmd.ExecuteNonQuery();
}
}
public static DataTable GetADataTable(SqlCommand cmd)
{
var aTable = new DataTable();
using (var connection = GetConnection())
{
cmd.Connection = connection;
cmd.CommandTimeout = 0;
connection.Open();
var adapter = new SqlDataAdapter { SelectCommand = cmd };
adapter.Fill(aTable);
}
return aTable;
}
public static SqlDataReader GetADataReader(SqlCommand cmd)
{
SqlDataReader aReader = null;
var connection = GetConnection();
cmd.Connection = connection;
cmd.CommandTimeout = 0;
connection.Open();
aReader = cmd.ExecuteReader();
return aReader;
}
}
}
We are not going to use all of these helper methods in this part of the article, since we are going to only work on read operations. In the future parts of the article,
we will be working on insert, update, and delete operations, and we will be using all these methods.
The Dapper Utilities
Dapper is very easy to use. If you visit the Dapper website, you will find
a single file that you can download.
You can compile this file into a DLL and reference it in your project. You can also simply add this file in your project.
I chose to add it in my project in the "Dapper\Dapper.cs" file, so I do not have too many DLLs to worry about
at application deployment time. As we know, Dapper is simply a set of extension methods around
the IDbConnection interface. We do not need to do any special configuration to use it.
The "Dapper\DapperUtility.cs" file only implements a method to return us the database connection object to save us some typing when we try to use Dapper to query the database.
using System.Configuration;
using System.Data.SqlClient;
namespace SQLPerformanceRead.DataAccessUtilities.Dapper
{
public class DapperUtility
{
private static readonly string ConnectionString;
static DapperUtility()
{
ConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
}
public static SqlConnection GetConnection()
{
return new SqlConnection
{
ConnectionString = ConnectionString
};
}
}
}
The NHinerate Utilities
NHibernate is not very easy to set up. It does require additional DLLs in your application. After some trial and error, I found that the easiest way to get all the DLLs
for Fluent NHibernate is through Nuget. If you have already set up Nuget in your Visual Studio, you can launch it for your project.
If you have never referenced any NHibernate related DLLs in your project, you can simply do a search and find the "FluentNHibernate" package and install it.
The installation will download all the DLLs to use Fluent NHibernate and add the references in your project. If for some reason, your project already references any
DLLs that are NHibernate related, you may encounter problems when you install
the package. You will be very likely to encounter version mismatch problems.
Nuget does make your downloading packages automatically, but it is somewhat "buggy".
It does not guarantee to download the minimum set of DLLs that you need. If
anything goes wrong, you will have to manually fix the problems, which can be
very time consuming and may not be a pleasant experience. Sometimes Nuget may simply fail to launch and the solutions to fix the problem
are largely case by case.
Anyhow this article is not about Nuget. At least I used it to get the DLLs that I need to create this example application and I hope the best of luck for
you too. In the worst case, the minimum set of DLLs to use Fluent NHibernate is in the "NHibernateDLLs" folder in the zip file attached in the article.
After referencing the DLLs in the project, let us take a look at the "NHibernate\NHibernateUtility.cs" file.
using System.Configuration;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using SQLPerformanceRead.DataAccessLayer.NHibernate.DataModel;
namespace SQLPerformanceRead.DataAccessUtilities.NHibernate
{
public static class NHibernateUtility
{
private static ISessionFactory _sessionFactory;
private static string GetConnectionString()
{
return ConfigurationManager.AppSettings["ConnectionString"];
}
private static ISessionFactory SessionFactory
{
get
{
InitializeSessionFactory();
return _sessionFactory;
}
}
public static void InitializeSessionFactory()
{
if (_sessionFactory == null)
{
string conStr = GetConnectionString();
_sessionFactory = Fluently.Configure()
.Database(MsSqlConfiguration.MsSql2008.ConnectionString(conStr))
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<TStudent>())
.BuildSessionFactory();
}
}
public static ISession OpenSession()
{
return SessionFactory.OpenSession();
}
}
}
The InitializeSessionFactory method initiates the NHibernate session factory.
What the OpenSession method returns to you is a NHibernate session object.
We will be using this session object to do queries against the database. In most
applications, the InitializeSessionFactory method is called only once. For this MVC
project, it is called in the Global.asax file in the Application_Start event.
protected void Application_Start()
{
AreaRegistration.RegisterAllAreas();
RegisterRoutes(RouteTable.Routes);
NHibernateUtility.InitializeSessionFactory();
}
The InitializeSessionFactory method will go through all the mapping classes and database objects that
are used by the application.
We will see the mapping classes later in this article. For this simple three-table example application, it is not a problem. But for any moderately
large practical application, this process does take some time and this happens every time you make a small change to your code and re-compile
your application. If you use NHibernate and if you need to make changes to your
code when you develop your application and you want to debug it, please be
patient because the application needs to finish InitializeSessionFactory before it launches.
The View Models
The view models of the application are implemented in the "ViewModels\StudentSummariesVm.cs" and "ViewModels\StudentCoursesVm.cs" files.
using System;
using System.Collections.Generic;
namespace SQLPerformanceRead.ViewModels
{
public class StudentSummary
{
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 StudentSummariesVm
{
public List<StudentSummary> StudentSummaries { get; set; }
public TimeSpan LoadTime { get; set; }
}
}
using System;
using System.Collections.Generic;
namespace SQLPerformanceRead.ViewModels
{
public class StudentCourse
{
public int CourseId { get; set; }
public string CourseName { get; set; }
public int CreditHours { get; set; }
public int Score { get; set; }
}
public class StudentCoursesVm
{
public List<StudentCourse> StudentCourses { get; set; }
public TimeSpan LoadTime { get; set; }
}
}
Each view model has a list of data that will be presented in the web browser
and each has a member variable named LoadTime to keep the total time used
to query the database and to create the view model. We will be using all the three data access methods to create the view models and compare the time spent by each method.
The Data Access Layer
The data access layer is the place where the application queries the database
to create the view models through each data access method. It is implemented in
the DataAccessLayer folder.
The ADO.NET Data Access Method
The "ADONET\AdoNetDbAccess.cs" file implements the method to access the database using ADO.NET.
using System;
using System.Data;
using System.Data.SqlClient;
using SQLPerformanceRead.DataAccessUtilities.ADONET;
using SQLPerformanceRead.ViewModels;
using System.Collections.Generic;
namespace SQLPerformanceRead.DataAccessLayer.ADONET
{
public class AdoNetDbAccess
{
public static StudentSummariesVm LoadStudentSummaries()
{
var summaries = new StudentSummariesVm();
var startTime = DateTime.Now;
var cmd = new SqlCommand
{
CommandType = CommandType.StoredProcedure,
CommandText = "GetStudents"
};
var studentSummaries = new List<StudentSummary>();
using (var aReader = AdoNetUtility.GetADataReader(cmd))
{
while (aReader.Read())
{
var summary = new StudentSummary
{
StudentId = Convert.ToInt32(aReader["StudentId"]),
LastName = aReader["LastName"].ToString(),
FirstName = aReader["FirstName"].ToString(),
AverageScore = Convert.ToInt32(aReader["AverageScore"]),
Credits = Convert.ToInt32(aReader["Credits"])
};
studentSummaries.Add(summary);
}
}
var timeSpent = DateTime.Now.Subtract(startTime);
summaries.StudentSummaries = studentSummaries;
summaries.LoadTime = timeSpent;
return summaries;
}
public static StudentCoursesVm LoadStudentCourses(int studentId)
{
var courses = new StudentCoursesVm();
var startTime = DateTime.Now;
var cmd = new SqlCommand
{
CommandType = CommandType.StoredProcedure,
CommandText = "GetStudentScores"
};
cmd.Parameters.Add("@StudentId", SqlDbType.Int).Value = studentId;
var studentCourses = new List<StudentCourse>();
using (var aReader = AdoNetUtility.GetADataReader(cmd))
{
while (aReader.Read())
{
var course = new StudentCourse
{
CourseId = Convert.ToInt32(aReader["CourseId"]),
CourseName = aReader["CourseName"].ToString(),
CreditHours = Convert.ToInt32(aReader["CreditHours"]),
Score = Convert.ToInt32(aReader["Score"])
};
studentCourses.Add(course);
}
}
var timeSpent = DateTime.Now.Subtract(startTime);
courses.StudentCourses = studentCourses;
courses.LoadTime = timeSpent;
return courses;
}
}
}
The LoadStudentSummaries method creates the StudentSummariesVm view model object and the LoadStudentCourses method creates the
StudentCoursesVm view model object. Both methods simply make a call to the stored procedures created earlier and loop through the
DataReader to create the view models. The time spent for the whole process is kept
in the LoadTime variable.
The Dapper Data Access Method
The "Dapper\DapperDbAccess.cs" file implements the method to access the database using Dapper.
using System;
using System.Collections.Generic;
using System.Data;
using SQLPerformanceRead.ViewModels;
using SQLPerformanceRead.DataAccessUtilities.Dapper;
using System.Linq;
namespace SQLPerformanceRead.DataAccessLayer.Dapper
{
public class DapperDbAccess
{
public static StudentSummariesVm LoadStudentSummaries()
{
var summaries = new StudentSummariesVm();
var startTime = DateTime.Now;
List<StudentSummary> studentSummaries;
using (var cn = DapperUtility.GetConnection())
{
cn.Open();
studentSummaries
= cn.Query<StudentSummary>("GetStudents",
commandType: CommandType.StoredProcedure).ToList();
}
var timeSpent = DateTime.Now.Subtract(startTime);
summaries.StudentSummaries = studentSummaries;
summaries.LoadTime = timeSpent;
return summaries;
}
public static StudentCoursesVm LoadStudentCourses(int studentId)
{
var courses = new StudentCoursesVm();
var startTime = DateTime.Now;
List<StudentCourse> studentCourses;
using (var cn = DapperUtility.GetConnection())
{
cn.Open();
studentCourses
= cn.Query<StudentCourse>("GetStudentScores",
new {StudentId = studentId},
commandType: CommandType.StoredProcedure).ToList();
}
var timeSpent = DateTime.Now.Subtract(startTime);
courses.StudentCourses = studentCourses;
courses.LoadTime = timeSpent;
return courses;
}
}
}
This file is almost identical to the "ADONET\AdoNetDbAccess.cs" file but simpler. The generic extension
method "Query" from Dapper is used to create the view models to save some coding from us.
The Fluent NHibernate Data Access Method
Accessing the database through NHibernate is not as simple as the previous two methods. We will first create the data model classes
in the "NHibernate\DataModel\DataModels.cs" file.
using System;
using System.Collections.Generic;
namespace SQLPerformanceRead.DataAccessLayer.NHibernate.DataModel
{
public class TCourse
{
public virtual int Id { get; set; }
public virtual string Name { get; set; }
public virtual int CreditHours { get; set; }
}
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; }
}
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;
}
}
}
}
Normally, each of the class corresponds to a table in the database. In the case of referential relations, you will need to add
a "List" of objects in your class.
TStudentScore is a little special, which has two methods named Equals and GetHashCode. When using NHibernate, you do better add an "ID" for each
table in your database. If you do not do it, you will need these two methods in your corresponding data model class.
In my case, [TStudentScore] is simply a relational table between the [TStudent] and [TCourse] tables.
Adding an "ID" to this table is really pointless. If I do add the "ID", I will also need to create an Index on the "ID" column,
which will cost a lot of unnecessary SQL Server resources. So I chose to add the Equals and GetHashCode methods in the data model
class. When creating the data model classes, you will need to remember to declare each member variable as
virtual
as required by NHibernate. After creating the data model classes, we will need to set up the mappings. Since I am using Fluent NHibernate, the mappings are set up
in the "NHibernate\TableMapping\Mappings.cs" file.
using FluentNHibernate.Mapping;
using SQLPerformanceRead.DataAccessLayer.NHibernate.DataModel;
namespace SQLPerformanceRead.DataAccessLayer.NHibernate.TableMapping
{
public sealed class TCourseMap : ClassMap<TCourse>
{
public TCourseMap()
{
Table("TCourse");
Id(x => x.Id).Column("ID");
Map(x => x.Name).Column("Name");
Map(x => x.CreditHours).Column("CreditHours");
}
}
public class TStudentMap : ClassMap<TStudent>
{
public TStudentMap()
{
Table("TStudent");
Id(x => x.Id).Column("ID");
Map(x => x.LastName).Column("LastName");
Map(x => x.FirstName).Column("FirstName");
Map(x => x.EnrollmentTime).Column("EnrollmentTime");
HasMany(x => x.StudentScores).KeyColumn("StudentId");
}
}
public sealed 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");
}
}
}
The mapping file is to tell NHibernate the correspondence of the member variable of the data model classes to the columns of the database tables.
In the case of reference relations, you will also need to tell the foreign key columns. After creating the data model classes and the mappings, we can then
use them to query the database through NHibernate. Let us first take a look at the "NHibernate\NHibernateDbAccessDefault.cs" file.
using System;
using System.Collections.Generic;
using SQLPerformanceRead.DataAccessLayer.NHibernate.DataModel;
using SQLPerformanceRead.DataAccessUtilities.NHibernate;
using SQLPerformanceRead.ViewModels;
using NHibernate.Linq;
using System.Linq;
namespace SQLPerformanceRead.DataAccessLayer.NHibernate
{
public class NHibernateDbAccessDefault
{
public static StudentSummariesVm LoadStudentSummaries()
{
var summaries = new StudentSummariesVm();
var startTime = DateTime.Now;
var studentSummaries = new List<StudentSummary>();
using (var session = NHibernateUtility.OpenSession())
{
var students = session.Query<TStudent>().ToList();
foreach (var student in students)
{
int count = 0;
int credits = 0;
double totalScore = 0;
foreach (var score in student.StudentScores)
{
count++;
totalScore = totalScore + score.Score;
if (score.Score >= 60)
{
credits = credits + score.Course.CreditHours;
}
}
int averageScore = Convert.ToInt32(totalScore / count);
var studentSummary = new StudentSummary
{
StudentId = student.Id,
LastName = student.LastName,
FirstName = student.FirstName,
AverageScore = averageScore,
Credits = credits
};
studentSummaries.Add(studentSummary);
}
}
var timeSpent = DateTime.Now.Subtract(startTime);
summaries.StudentSummaries = studentSummaries;
summaries.LoadTime = timeSpent;
return summaries;
}
public static StudentCoursesVm LoadStudentCourses(int studentId)
{
var courses = new StudentCoursesVm();
var startTime = DateTime.Now;
var studentCourses = new List<StudentCourse>();
using (var session = NHibernateUtility.OpenSession())
{
var studentScores = session.Query<TStudentScore>()
.Where(s => s.StudentId == studentId).ToList();
foreach (var studentScore in studentScores)
{
var studentCourse = new StudentCourse
{
CourseId = studentScore.CourseId,
CourseName = studentScore.Course.Name,
CreditHours = studentScore.Course.CreditHours,
Score = studentScore.Score
};
studentCourses.Add(studentCourse);
}
}
var timeSpent = DateTime.Now.Subtract(startTime);
courses.StudentCourses = studentCourses;
courses.LoadTime = timeSpent;
return courses;
}
}
}
The NHibernateDbAccessDefault class does exactly the same thing
as what we have done through ADO.NET and Dapper, except the database access is using NHibernate.
Since NHibernate does not favor the use of stored procedures, this class directly accesses the database tables through the mappings. It is the reason why we granted the table level
permissions to the user name "TestUser". You may also notice that the aggregations are done in the C# code. We can actually let SQL
Server do the aggregations by using
Hibernate Query Language. Since Hibernate Query Language conflicts with
the original intent of the Object Relational Mapping concept and it is a more complex and redundant duplication
of the well-established ANSI SQL by the American National Standards Institute, I decided to skip it in this article. But if you are interested,
you may explore it from this link.
If you look into the NHibernateDbAccessDefault class closely, you will see that the database queries through NHibernate take the syntax shown below.
var students = session.Query<TStudent>().ToList();
and
var studentScores = session.Query<TStudentScore>()
.Where(s => s.StudentId == studentId).ToList();
Although each query is just a single line of code, when executed, this single line of code can result in thousands of round-trip database queries.
Later you will find that compared with the other method, the same task completed by NHibernate is much slower. To improve
performance, I created another class called NHibernateDbAccessImproved. This class is exactly the same as
NHibernateDbAccessDefault, except the queries are done with the following syntax:
var students = session.Query<TStudent>()
.FetchMany(s=>s.StudentScores)
.ThenFetch(s=>s.Course)
.ToList();
and:
var studentScores = session.Query<TStudentScore>()
.Where(s => s.StudentId == studentId)
.Fetch(s=>s.Course)
.ToList();
FetchMany and Fetch force NHibernate to retrieve information from the database
in an eager loading fashion,
which results in fewer number of database round trips.
The Controller
The controller of this MVC application is implemented in the "Controllers\HomeController.cs" file.
using System.Web.Mvc;
using SQLPerformanceRead.DataAccessLayer.ADONET;
using SQLPerformanceRead.DataAccessLayer.Dapper;
using SQLPerformanceRead.DataAccessLayer.NHibernate;
using SQLPerformanceRead.DataAccessUtilities;
using SQLPerformanceRead.ViewModels;
namespace SQLPerformanceRead.Controllers
{
public class HomeController : Controller
{
public ActionResult Index() { return View(); }
public ActionResult StudentSummaries(string method)
{
var summaries = new StudentSummariesVm();
if (method == DataAccessMethod.AdoNet.ToString())
{
summaries = AdoNetDbAccess.LoadStudentSummaries();
}
else if (method == DataAccessMethod.Dapper.ToString())
{
summaries = DapperDbAccess.LoadStudentSummaries();
}
else if (method == DataAccessMethod.NHibernateDefault.ToString())
{
summaries = NHibernateDbAccessDefault.LoadStudentSummaries();
}
else
{
summaries = NHibernateDbAccessImproved.LoadStudentSummaries();
}
return PartialView(summaries);
}
public ActionResult StudentCourses(int studentId, string method)
{
var courses = new StudentCoursesVm();
if (method == DataAccessMethod.AdoNet.ToString())
{
courses = AdoNetDbAccess.LoadStudentCourses(studentId);
}
else if (method == DataAccessMethod.Dapper.ToString())
{
courses = DapperDbAccess.LoadStudentCourses(studentId);
}
else if (method == DataAccessMethod.NHibernateDefault.ToString())
{
courses = NHibernateDbAccessDefault.LoadStudentCourses(studentId);
}
else
{
courses = NHibernateDbAccessImproved.LoadStudentCourses(studentId);
}
return PartialView(courses);
}
}
}
The action method Index simply loads the main page of the
application. The StudentSummaries and StudentCourses methods
call the corresponding data access methods to generate the view models and render the corresponding sub-views to be consumed by
AJAX calls
issued from the application's main page.
The Views
The application's main page is implemented in the "Views\Home\Index.aspx" file.
<div>
<label class="textLabel">Select a data access method</label>
<span>
<input type="radio" name="radAccessMethod"
value="<%=DataAccessMethod.AdoNet.ToString() %>" checked="checked" />
<label>ADO.Net</label>
<input type="radio" name="radAccessMethod"
value="<%=DataAccessMethod.Dapper.ToString() %>" />
<label>Dapper.Net</label>
<input type="radio" name="radAccessMethod"
value="<%=DataAccessMethod.NHibernateDefault.ToString() %>" />
<label>NHibernate Default</label>
<input type="radio" name="radAccessMethod"
value="<%=DataAccessMethod.NHibernateImproved.ToString() %>" />
<label>NHibernate Improved</label>
</span>
<label class="buttonlink" id="lblLoadStudents">Load Students</label>
<label class="buttonlink" id="lblClear">Clear</label>
</div>
<div id="divStudents"></div>
<div id="divStudentCourses"></div>
The radio buttons let us choose the data access methods to use. The labels
lblLoadStudents and lblClear serve as action buttons.
Clicking on the lblLoadStudents label will load the result from the
database by the chosen data access method and display the result on the web
page. Clicking on the lblClear label will clear the result. The JavaScript to support this page is the following:
<script type="text/javascript">
var MakeAjaxCall = function (url, successCallback) {
$.ajax({
cache: false,
type: "GET",
async: false,
url: url,
dataType: "text",
success: successCallback,
error: function (xhr) {
alert(xhr.responseText);
}
});
};
$(document).ready(function () {
$('#lblLoadStudents').click(function () {
var method = $('input[name=radAccessMethod]:checked').val();
var url = studentSummariesUrl + "?method=" + method;
MakeAjaxCall(url, function (htmlFragment) {
$('#divStudents').html(htmlFragment);
});
});
$('#lblClear').click(function () {
$('#divStudents').html('');
$("#divStudentCourses").dialog('close');
});
$('input[name=radAccessMethod]').change(function () {
$('#lblClear').click();
});
});
var ShowStudentCourses = function (studentId) {
var method = $('input[name=radAccessMethod]:checked').val();
var url = studentCoursesUrl + "?studentId=" + studentId
+ "&method=" + method;
MakeAjaxCall(url, function (htmlFragment) {
$('#divStudentCourses').html(htmlFragment);
var option = {
width: 500,
height: 300,
title: "Courses for student ID = " + studentId
};
$("#divStudentCourses").dialog(option);
});
};
</script>
The two sub-views StudentSummaries.ascx and StudentCourses.ascx are implemented as follows:
<%@ Control Language="C#"
Inherits="System.Web.Mvc.ViewUserControl<SQLPerformanceRead
.ViewModels.StudentSummariesVm>" %>
<br/>
<label>Database Access Time: <span style="color: red"><%=Model.LoadTime %></span></label>
<div id="divStudentSummary" class="divTable">
<div class="tableHeader">
<span>StudentId</span>
<span>LastName</span>
<span>FirstName</span>
<span>AverageScore</span>
<span>Credits</span>
</div>
<%foreach (var summary in Model.StudentSummaries)
{ %>
<div>
<span><label class="buttonlink"
onclick="return ShowStudentCourses(<%= summary.StudentId %>) ">
<%=summary.StudentId %>
</label></span>
<span><%=summary.LastName %></span>
<span><%=summary.FirstName %></span>
<span><%=summary.AverageScore %></span>
<span><%=summary.Credits %></span>
</div>
<% } %>
</div>
<%@ Control Language="C#"
Inherits="System.Web.Mvc.ViewUserControl<SQLPerformanceRead
.ViewModels.StudentCoursesVm>" %>
<br/>
<label>
Database Access Time: <span style="color: red"><%=Model.LoadTime %></span>
</label>
<div id="divStudentCourse" class="divTable">
<div class="tableHeader">
<span>CourseId</span>
<span>CourseName</span>
<span>CreditHours</span>
<span>Score</span>
</div>
<%foreach (var course in Model.StudentCourses)
{
var cssClass = (course.Score >= 60)?"succcess":"fail";
%>
<div class="<%=cssClass %>">
<span><%=course.CourseId %></span>
<span><%=course.CourseName %></span>
<span><%=course.CreditHours %></span>
<span><%=course.Score %></span>
</div>
<% } %>
</div>
Run the Application
Make sure your SQL Server is running and accessible and make sure the [SQLPerformanceRead] database is created successfully, we can then run the application.
We can check one of the radio buttons to indicate the data access method of choice and click the "Load Students"
label. The student summary is loaded
to the web page and you can see the time used to create the view model from the data in the database on the page. If you click on the "StudentId"
of any of the students, the courses taken by this student is loaded into the page and the time taken is also shown in the page.
This example is no where close to a commercial grade web application, I did not give any feedback to the user to indicate that an
AJAX call is going to load the requested information.
When you test the NHibernate methods, you will need to be patient, because it does take a long time to load the data and you may think that the application is dead. But if everything goes
well and if you are patient enough, the result will eventually come back.
The Comparison Result
The following is the comparison result by averaging 10 times of the running results for each method in
Debug mode.
We can see that direct ADO.NET does not have a visible difference compared to Dapper, but NHibernate is significantly slower to accomplish the same task.
Because the improved NHibernate method reduces the round-trips to the database
server, its performance is better than the default behavior of NHibernate. If
you set up SQL Profiler, you can find that the number of SQL queries taken
by the improved NHibernate method is much reduced. A further comparison by deploying the application to the web server generates the following result:
We can see from the result that all the methods improved performance by deploying to the web server.
The default NHibernate method improves the most,
the time it takes for the same task remains significantly longer, although it is less than 100 times longer. As technology improves, computer hardware
and network resources are getting cheaper and cheaper, but it is still prohibitively expensive if you want to have infrastructure that is 100 times faster to compensate
for the slow performance of the improper use of NHibernate. One interesting observation is that Dapper actually out-performed direct ADO.NET when deployed
to the web server. It may be because of some better implementation in Dapper than my own hand-written code. Well, it may also simply
be because of a measurement error
or possibly a different running condition of my testing computer at a different time, since the difference is really not very significant anyway.
Possible Further Comparisons
We can actually perform another comparison by changing the total number of the students in the database. If you change the script to create the database and
add a different number of students in the database by changing the following line of code in the script:
SET @NoOfStudents = 5000
Instead of 5000, you can give it a different number. After re-creating the database, you can check how different data access methods perform on different sizes
of the data set. I am not going to show the details of my own comparisons here. But what I found is that the larger the data set was, the worse NHibernate performed.
You are definitely encouraged to do the experiment. This experiment will help you to decide what data access method to use if your application is truly high volume
and mission critical. If you do want to do this experiment, when you run the SQL script, you may see some error messages in your
Management Studio, because the server
may still be connected by the test application. Do not worry about it. The SQL script is written as reentrant,
so you can run it as many times as you want until the database is dropped and recreated. I am sorry that I did not provide you a separate script to change the data
in the database without re-creating the database. But I am really busy on some more important works recently.
Points of Interest
- This is part 1 of the comparison on Data Access methods in the .NET environment.
- The example application showed you how to use ADO.NET,
Dapper, and Fluent NHibernate to perform read
operations on a SQL Server database. If you are not familiar with how to use these methods, you can use this example to get started.
- A performance comparison is done on these data access methods using the
example application. The result shows that ADO.NET
and Dapper take roughly the same amount of time to generate the MVC view models from the data in the database, but NHibernate performs significantly slower.
- The example also showed you that the performance of NHibernate can be improved by correctly setting the eager or lazy loading behaviors of the NHibernate
queries, but it does not give NHibernate the opportunity to out-perform ADO.NET and Dapper. If you do use NHibernate in your application, the loading behavior
can significantly affect your performance and you should spend enough time to work on it.
- The comparisons show that the performance difference is not as large when the application is deployed to the web server than in the debug environment.
As a programmer, you will most likely run your application in Debug mode. If you use a data access method that runs close to 1000 times slower on your
data set in your environment, you probably won't be able to do much in your day.
- If your application uses NHibernate, it will need to be initiated when the application starts.
If your application is moderately complex, the initiation will take some time. This normally is not a problem in
a production environment, since it only initiates once at the application start up time. But if you are a programmer, and you will need to change your program and re-compile your code many times a day,
you will have to wait for the initiation running many times in the day, which is definitely not a good news for your productivity.
- The comparison is on my personal computer. You are definitely encouraged to run the comparison on your own on a more comprehensive environment.
If you see a different trend, I definitely want to hear about it and re-do my own comparisons.
- I am kind of busy recently. But as promised, I will definitely post parts 2 and 3 of the article shortly. In part two,
I will be focusing on the insert operations and show you the NHibernate Hilo
and let you know what I find.
- I have tested the application in Firefox, Chrome, and a higher version of Internet Explorer. Although the application runs on all platforms,
it does run slow on Internet Explorer because we are displaying the information of 5000 students in a single page. If you want to run the application on your own,
I hope you are aware of this. The application runs fairly well on both Firefox and Chrome on my computer.
- If you are interested in this article, you can take a look at the part 2 and part 3 when you have some time.
- I hope you like my postings and I hope this article can help you one way or the other.
History
- First revision - 3/23/2013.
