Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server

A Comparison on Data Access Methods - Part 3

Rate me:
Please Sign up or sign in to vote.
4.67/5 (14 votes)
12 Oct 2013CPOL8 min read 34.5K   816   23   12
This is the part 3 of a comparison on Data Access methods in the .NET environment.

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 NHibernateGenerally 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. 

SQL
SET NOCOUNT ON
	
USE [master]
GO
	
-- Create a login user
IF EXISTS
	(SELECT * FROM sys.server_principals
		WHERE name = N'TestUser')
DROP LOGIN [TestUser]
GO
	
EXEC sp_addlogin @loginame = 'TestUser', @passwd  = 'Password123';
GO
	
-- Create the database
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
	
-- Grant the required database access to the login 
sp_grantdbaccess 'TestUser'
GO
sp_addrolemember 'db_datareader', 'TestUser'
GO
sp_addrolemember 'db_datawriter', 'TestUser'
GO
	
CREATE TABLE [dbo].[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
	
-- Add the courses
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
		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
		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
		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
	
-- Bring the database on-line
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.  

Image 1

In the [SQLPerformanceInsertUpdateDelete] database, we have 3 tables and 2 stored procedures. The database diagram of the database [SQLPerformanceInsertUpdateDelete] is shown as the following: 

 Image 2

When creating the database, the [TCourse] table is filled with the following data: 

Image 3

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. 

SQL
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. 

SQL
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
		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
		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
		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. 

SQL
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)
) 
SQL
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. 

 Image 4

Image 5 

Image 6

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. 

Image 7 

  • 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.  

C#
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.  

XML
<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. 

C#
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 

 Image 8

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. 

C#
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)
            {
                // Get the student
                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);
	
                // Get the courses
                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. 

C#
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; }
    }
} 
C#
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; }
    }
} 
C#
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. 

C#
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();
        }
    }
} 
C#
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();
        }
    }
} 
C#
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. 

C#
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>();
	
            // Load course from the database, so we can have
            // the course Id for each student course
            // The xml file only has the course name
            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())
            {
                // Make sure to eager load the student scores for
                // better performance
                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())
                {
                    //Delete students & scores not in XML
                    foreach (var dbStudent in dbStudents)
                    {
                        // Check if the student is in the XML
                        var studentId = dbStudent.Id;
                        var xmlStudent = (from s in xmlStudents
                                          where s.Id == studentId
                                          select s).SingleOrDefault();
	
                        // student does not exist in xml, delete everything
                        if (xmlStudent == null)
                        {
                            foreach (var dbscore in dbStudent.StudentScores)
                            {
                                session.Delete(dbscore);
                            }
	
                            session.Delete(dbStudent);
                            continue;
                        }
	
                        // student exist in the xml, check if any scores
                        // need to delete
                        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);
                            }
                        }
                    }
	
                    // Save or update the studens and the scores
                    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. 

C#
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. 

C#
 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. 

JavaScript
<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. 

Image 9 

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  

Image 10

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

License

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


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

Comments and Discussions

 
QuestionGreat article Pin
zeego5-Feb-14 18:02
zeego5-Feb-14 18:02 
Questionis NHibernate provide easy binding to controls like dataset? Pin
ibrahim_ragab15-Nov-13 13:03
professionalibrahim_ragab15-Nov-13 13:03 
QuestionEntity Framework instead of NHibernate Pin
Md. Shafiuzzaman9-Nov-13 19:55
professionalMd. Shafiuzzaman9-Nov-13 19:55 
AnswerRe: Entity Framework instead of NHibernate Pin
Dr. Song Li11-Nov-13 4:03
Dr. Song Li11-Nov-13 4:03 
I have used EF, I am not impressed by its performance. I did not do any head to head comparison though. From the information that I read from the internet, EF is not good for performance.
GeneralRe: Entity Framework instead of NHibernate Pin
Md. Shafiuzzaman11-Nov-13 16:31
professionalMd. Shafiuzzaman11-Nov-13 16:31 
GeneralMy vote of 1 Pin
Member 1033103116-Oct-13 2:34
Member 1033103116-Oct-13 2:34 
GeneralRe: My vote of 1 Pin
S. M. Ahasan Habib9-Nov-13 2:17
professionalS. M. Ahasan Habib9-Nov-13 2:17 
QuestionRe: My vote of 1 Pin
thatraja10-Nov-13 20:31
professionalthatraja10-Nov-13 20:31 
QuestionAnd again you are comparing apples with oranges Pin
Klaus Luedenscheidt12-Oct-13 18:56
Klaus Luedenscheidt12-Oct-13 18:56 
AnswerRe: And again you are comparing apples with oranges Pin
Dr. Song Li14-Oct-13 4:09
Dr. Song Li14-Oct-13 4:09 
GeneralRe: And again you are comparing apples with oranges Pin
Klaus Luedenscheidt14-Oct-13 19:31
Klaus Luedenscheidt14-Oct-13 19:31 
QuestionI am not voting yet... Pin
Paulo Zemek9-Oct-13 11:56
mvaPaulo Zemek9-Oct-13 11:56 

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

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