Introduction

For some time now I have been working on a project that utilizes a custom-built data access framework, rather than popular ORM frameworks such as Entity Framework or NHibernate.

While the custom framework has worked well for the project, I had questions about it. For example, it uses stored procedures to implement basic CRUD operations, and I wondered if inline parameterized SQL statements might perform better. Also, I wondered about the performance of the custom framework compared to the leading ORMs.

Besides my questions about the custom framework, I recognized the importance of having at least a basic understanding of how to use the other ORM frameworks.

In order to answer my questions about the custom framework and to gain some practical experience with the other ORMs, I created a simple web application that uses each of those frameworks to perform basic CRUD applications. While executing the CRUD operations, the application times them and produces a summary report of the results.

NOTE: I assume that most readers are familiar with the basics of Entity Framework and NHibernate, so I will not provide an overview of them here.

Using the custom framework is similar to Entity Framework and NHibernate’s “database-first” approach. Any project that uses the library references a single assembly containing the base functionality of the library. A T4 template is used to generate additional classes based on tables in a SQL Server database. Some of the classes are similar to EF’s Model classes and NHibernate’s Domain classes. The others provide the basic CRUD functionality for the domain/model classes.

For these tests I made a second copy of the custom framework classes that provide the basic CRUD functionality, and edited them to replace the CRUD stored procedures with parameterized SQL statements.

The custom framework includes much less overhead on top of ADO.NET than the popular ORMs, so I expected the tests to show that it was the best-performing framework. The question was, how much better?

In the rest of this post, I will describe the results of my experiment, as well as some of the optimization tips I learned along the way. Use the following links to jump directly to a topic.

Test Application Overview

A SQL Express database was used for the tests. The data model is borrowed from Microsoft’s Contoso University sample application. Here is the ER diagram for the database:

The database was pre-populated with sample data. The number of rows added to each table were:

Department: 20 Course: 200 Person: 100000 Enrollment: 200000

This was done because SQL Server’s optimizer will behave differently with an empty database than it will with a database containing data, and I wanted the database to respond as it would in a “real-world” situation. For the tests, all CRUD operations were performed against the Enrollment table.

Five different data access frameworks were tested:

Custom framework with stored procedures

Custom framework with parameterized SQL statements

Entity Framework

NHibernate

Fluent NHibernate

The testing algorithm follows the same pattern for each of the frameworks:

01) Start timer 02) For a user-specified number of iterations 03) Submit an INSERT statement to the database 04) Save the identifier of the new database record 05) End timer 06) Start timer 07) For each new database record identifier 08) Submit a SELECT statement to the database 09) End timer 10) Start timer 11) For each new database record identifier 12) Submit an UPDATE statement to the database 13) End timer 14) Start timer 15) For each new database record identifier 16) Submit a DELETE statement to the database 17) End timer

Note that after the test algorithm completes, the database is in the same state as when the tests began.

"Out-of-the-Box" Performance

I first created very basic tests for each framework. Essentially, these were the “Hello World” versions of the CRUD code for each framework. No optimization was attempted.

Here is an example of the code that performs the INSERTs for the custom framework. There is no difference between the version with stored procedures and the version without, other than the namespace from which EnrollmentDAL is instantiated.

The SELECT, UPDATE, and DELETE code for each framework followed similar patterns.

NOTE: A SQL Server Profiler trace proved that the actual interactions with the database were the same for each framework. The same database connections were established, and equivalent CRUD statements were submitted by each framework. Therefore, any measured differences in performance are due to the overhead of the frameworks themselves.

NOTE: For all tests, each combination of Framework and Operation was executed 10000 times. Looking at the first line of the preceding results, this means that Custom framework took 7.45 seconds to perform 10000 INSERTs.

As you can see, both instances of the the custom framework outperformed Entity Framework and NHibernate. In addition, the version of the custom framework that used parameterized SQL was very slightly faster than the version that used stored procedures. Most interesting however, was the performance for INSERT and UPDATE operations. Entity Framework and both versions of NHibernate were not just worse than the two custom framework versions, they were much MUCH worse. Clearly, some optimization and/or configuration changes were needed.

Entity Framework Performance After Code Optimization

AutoDetectChangesEnabled and DetectChanges()

It turns out that much of Entity Framework’s poor performance appears to have been due to the nature of the tests themselves. Information on Microsoft’s MSDN website notes that if you are tracking a lot of objects in your DbContext object and call methods like Add() and SaveChanges() many times in a loop, your performance may suffer. That scenario describes the test almost perfectly.

The solution is to turn off Entity Framework’s automatic detection of changes by setting AutoDetectChangesEnabled to false and explicitly calling DetectChanges(). This instructs Entity Framework to only detect changes to entities when explicitly instructed to do so. Here is what the updated code for performing INSERTs with Entity Framework looks like (changes highlighted in red):

As you can see, INSERT and UPDATE performance improved significantly, and SELECT and DELETE performance also improved slightly.

Note that turning off AutoDetectChangesEnabled and calling DetectChanges() explicitly in all cases WILL slightly improve the performance of Entity Framework. However, it could also cause subtle bugs. Therefore, it is best to only use this optimization technique in very specific scenarios and allow the default behavior otherwise.

Recycling the DbContext

While Entity Framework performance certainly improved by changing the AutoDetectChangesEnabled value, it was still relatively poor.

Another problem with the tests is that the same DbContext was used for every iteration of an operation (i.e. one DbContext object was used for all 10000 INSERT operations). This is a problem because the context maintains a record of all entities added to it during its lifetime. The effect of this was a gradual slowdown of the INSERT (and UPDATE) operations as more and more entities were added to the context.

Here is what the Entity Framework INSERT code looks like after modifying it to periodically create a new Context (changes highlighted in red):

Much better! The time to perform the SELECT operations was little changed, but the DELETE time was reduced by half, and the INSERT and UPDATE times decreased from a little more than 10 minutes to about 14 seconds.

NHibernate Performance After Configuration Optimization

For the NHibernate frameworks, the tests themselves were not the problem. NHibernate itself needs some tuning.

An optimized solution was achieved by changing the configuration settings of the NHibernate Session object. Here is the definition of the SessionFactory for NHibernate (additions highlighted in red):

The following table gives a brief description of the purpose of these settings:

Setting PurposeFormatSql Format the SQL before sending it to the database GenerateStatistics Produce statistics on the operations performed Hbm2ddlKeyWords Should NHibernate automatically quote all db object names PrepareSql Compiles the SQL before executing it PropertyBytecodeProvider What bytecode provider to use for the generation of code QueryStartupChecking Check all named queries present in the startup configuration ShowSql Show the produced SQL UseProxyValidator Validate that mapped entities can be used as proxies UseSecondLevelCache Enable the second level cache

Notice that several of these (FormatSQL, GenerateStatistics, ShowSQL) are most useful for debugging. It is not clear why they are enabled by default in NHibernate; it seems to me that these should be opt-in settings, rather than opt-out.

Here are the results of tests of the NHibernate frameworks with these changes in place:

These results are much improved, with the INSERT, SELECT, and DELETE operations nearly matching the results achieved by the custom framework. The UPDATE performance, while improved, is still relatively poor.

What’s Up with Update Performance in NHibernate?

The poor update performance is a mystery to me. I have researched NHibernate optimization techniques and configuration settings, and have searched for other people reporting problems with UPDATE operations. Unfortunately, I have not been able to find a solution.

This is disappointing, as I personally found NHibernate more comfortable to work with than Entity Framework, and because it beats or matches the performance of Entity Framework for SELECT, INSERT, and DELETE operations.

If anyone out there knows of a solution, please leave a comment!

Final Results

The following table summarizes the results of the tests using the optimal configuration for each framework. These are the same results shown earlier in this post, combined here in a single table.

This is part of a series of posts containing my notes from the sessions I attended at the 2011 St. Louis Day of .NET conference.

This series does not attempt to give complete accounts of the information presented in each session; it is just a way to capture the bullet points, notes, and opinions that I recorded while attending the conference. I have previously posted a list of all of the session materials and sample code that I have been able to find online, so if you are looking for a more precise account of a session, try looking there.

Brad Tutterow presented The Tasty Flavors of Entity Framework 4.1, which introduced the various ways (Code-First, Model-First, and Database-First) to develop with the latest version of Entity Framework. Here are my (rather brief) notes from the session.

The ‘DbContext’ object was added in Entity Framework 4.1, and is the preferred object for interacting with Entity Framework. Previously, the only option was ‘ObjectContext’.

If you have existing data access classes that represent your data model, you can use the code-first method for EF. (This is Interesting… worth trying out to see how it works. Perhaps offers another way to migrate to EF from existing code.)

Code-first requires *manual* creation of the DBContext class. Then, add a connection string with a name that matches the name of the DBContext class. On the first run of the code-first application, the database will be created automatically. (Whoa, really? Will have to think about how this can work in a controlled production environment.)

With the code-first approach, use the Fluent API if your entities (classes) differ from the data model.

Earlier this week I realized that I had let the development environment on my laptop get a bit out of date. Sure, it was running Visual Studio 2010, but I had not applied Service Pack 1, nor any of the big buzzy out-of-band add-ons from the first half of 2011.

Step 1) Install Visual Studio 2010 Service Pack 1. This installation was a painless hands-off procedure, although it did take 2-3 hours to complete. My dev tools are set up in virtual machines (one for Visual Studio 2010 and related tools, one for WAMP platform tools), so running the upgrade against the virtual machine probably slowed things down. Regardless, it was a lengthy install.

Step 2) Go to nuget.org and install the NuGet Visual Studio extension. Again, this was painless; I downloaded the installation package, chose Run, and that was it. NuGet is a package manager for easily installation and updating of open source libraries and tools in Visual Studio. Package managers are already popular in other environments, but are a relatively new addition to the Microsoft development stack.

Step 3) Start Visual Studio. So far, so good.

Here is where things started to go awry. My own fault, as you will see. I followed the instructions found at http://docs.nuget.org/docs/start-here/Using-the-Package-Manager-Console to attempt to install the latest versions of ASP.NET MVC and Entity Framework. I could have just done this via the usual download-and-install process, but I wanted to give NuGet a spin.

Step 4) From the Tools menu, use NuGet by selecting Library Package Manager and then clicking Package Manager Console. This opens a PowerShell dialog within Visual Studio.

Step 5) Type "get-help NuGet" to view all of the available NuGet commands.

Step 6) Type "Get-Package -ListAvailable" to view a list of all available NuGet packages. Locate the “Entity Framework” package in the list.

Step 7) Type "Install-Package Entity Framework". Oops… FAIL. I received the following error:

What I’d uncovered was a fundamental misunderstanding on my part about what NuGet does. My desire was to add capabilities to Visual Studio itself, not just to a single project or solution. Turns out that’s not what NuGet is for.

Step 8 ) Type "get-help Install-Package" to get more information about what I had just attempted. Included in the description of the “Install-Package” command is the statement "Installs a package and its dependencies into the project." Ok then, that confirms it… NuGet is for individual projects, not for the development environment as a whole.

My initial misunderstanding of NuGet stems from my only previous Package Manager experience, which was with the “apt” system that is a part of Ubuntu Linux.. Using that Package Manager, I was able to add capabilities and tools to the operating system… things like MySQL and Apache. This, in fact, is somewhat similar to the Web Platform Installer from Microsoft. But not NuGet. NuGet is actually more like RubyGems, which are used to adding capabilities to software projects written in Ruby.

So, I learned some useful information, but as far as installing ASP.NET MVC and Entity Framework, it was back to the drawing board

Step 9) Shut down Visual Studio.

Step 10) Install ASP.NET MVC 3. Speaking of the Web Platform Installer, I hadn’t used it a while, so I decided to go that route to install ASP.NET MVC 3. I browsed to http://www.microsoft.com/web/gallery/install.aspx?appid=MVC3, and clicked Install Now. The Web Platform Installer itself installed first, followed by ASP.NET MVC 3. This took much longer than expected to install. Overall, though, Web Platform Installer was as painless to use as I remembered. Nice.

Step 11) Install IIS Express 7.5. Though not part of the original plan, I decided to also install IIS Express 7.5. I did this using a "normal" download-and-install procedure. Easy. As advertised, very lightweight.

Step 12) Install Entity Framework 4.1. I did this via the download-and-install process (choosing to "save" and then run the downloadable installer… Web Platform Installer was not used). This installation was very quick when compared to MVC3. I’m not sure if that was due to the NON-use of Web Platform Installer to install EF, or if EF is simply a much more lightweight install than MVC.

While looking for the correct Entity Framework 4 installer package I noticed the following statement on the ADO.NET blog: "Note: The NuGet package only includes the EF 4.1 runtime and does not include the Visual Studio item templates for using DbContext with Model First and Database First development." So, that provides further clarification that NuGet adds the necessary run-time libraries to specific projects, but does NOT add full development tools to VS.

Step 13) Start Visual Studio and verify all installs. To confirm that everything was as it should be, I checked the following:

Step 13a) File/New/Project shows ASP.NET MVC3 Web Application as a project type. Step 13b) EntityFramework 4.1.0.0 shows up as an option when adding a reference to a project. Step 13c) There is a new option for Add Library Package Reference when adding a reference to a project. Step 13d) On the property page for a a web project, there is a new option for running under IIS Express.

That’s it. My Visual Studio 2010 development environment is now up-to-date. Along the way, I gained greater insight into NuGet, the hot new Package Manager add-on for Visual Studio In particular, I learned that if you’re looking to upgrade your entire development environment, and want to use a package-manager type application, Web Platform Installer is your choice. On the other hand, if you want to add libraries to a project on which you are working, Nuget is the tool to use.