Tuesday, December 06, 2011

Data Access Performance Comparison in .NET

When writing data access code you are probably using some sort of ORM or data mapper to make your life easier while retrieving and persisting data. We all know that this does not come for free, ORMs allow you to move faster, but there is always a performance hit. I recently started reading about Dapper .NET, an open-source, lightweight, single-file object mapper that was developed for the stackoverflow.com site to execute high performance queries. After seeing its amazing performance results on their project page, I decided to write my owns performance tests to compare it to other ORMs (Entity Framework, NHibernate), and also to plain ADO.NET code.

For these tests, I am using the Chinook Database for SQL Server. I defined a simple repository interface and implemented it using different data access approaches. The interface implemented is:

public interface IRepository
{
 Artist GetArtistById(int id);
 IEnumerable<Song> GetSongsByArtist(string name);
}

The first method executes a simple query in a single table by the primary key. The second method executes a query in multiple tables using joins.

There are multiple implementations of this repository interface including plain ADO.NET code with DataReaders, Entity Framework, NHibernate and Dapper .NET. For the Entity Framework, there are four implementations: Linq To Entities, compiled Linq To Entities, native SQL query and stored procedures. For NHibernate, there are implementations with HQL query, native SQL query, and stored procedure.

All the code is available at https://github.com/lerocha/DotNetDataAccessPerformance.

Single Table Query by Primary Key

The first set of tests is to execute a simple query in a single table to get one record by the primary key. The SQL query is:

SELECT ArtistId, Name FROM Artist WHERE Artist.ArtistId=@id

The following table contains the results per multiple runs:

1 run10 runs100 runs500 runs1000 runs
DataReaderNativeQuery0.02.024.9134.1264.4
DataReaderStoredProcedure0.02.226.3133.8266.4
DapperNativeQuery0.12.528.0141.5285.6
DapperStoredProcedure0.02.428.0137.5276.1
EntityFrameworkCompiledLinqQuery2.222.5207.21041.02099.8
EntityFrameworkLinqToEntities5.525.2267.71263.62522.5
EntityFrameworkNativeQuery0.78.285.2426.0859.5
EntityFrameworkStoredProcedure1.45.258.3275.8552.8
NHibernateHqlQuery0.84.448.1253.6509.5
NHibernateHqlQueryStrongType0.14.147.2251.4508.7
NHibernateNativeQuery1.14.446.1256.9490.1
NHibernateStoredProcedure0.05.257.2294.0589.8

The 1-run test is executed twice, the first execution result is not shown since I am only considering hot start. The following chart shows the results of 1,000 runs. As you can see, using Entity Framework with Linq To Entities (non-compiled) has the worst performance result (2,552.5 ms). It gets a little bit better if you use EF compiled Linq query (2,099.8), then improvements starts when using EF with native query (859.5 ms). EF using stored procedure and all NHibernate approaches are way better, all around 500 ms. And finally, the performance of Dapper is very close to the plain ADO.NET code, providing an level of abstraction with minimal performance impact.


Multiple Table Query with Joins

The second test executes joins in multiple tables and filters by a non-key column. The SQL query is:

 SELECT Album.Title as AlbumName, 
 Track.Name as SongName, Artist.Name as ArtistName
 FROM Artist
 INNER JOIN Album ON Album.ArtistId = Artist.ArtistId
 INNER JOIN Track ON Track.AlbumId = Album.AlbumId
 WHERE Artist.Name=@name

The following table contains the results per multiple runs:

1 run10 runs100 runs500 runs1000 runs
DataReaderNativeQuery0.05.963.0321.8644.2
DataReaderStoredProcedure0.06.265.0332.3658.6
DapperNativeQuery0.46.468.0346.1685.5
DapperStoredProcedure0.16.767.2342.3679.7
EntityFrameworkCompiledLinqQuery1.09.9103.3525.61071.0
EntityFrameworkLinqToEntities5.658.2539.52699.95398.3
EntityFrameworkNativeQuery1.113.1135.0658.81328.3
EntityFrameworkStoredProcedure1.19.799.0489.1983.7
NHibernateHqlQuery3.435.6359.51806.23647.1
NHibernateHqlQueryStrongType6.868.3681.53430.06841.4
NHibernateNativeQuery1.110.6103.2517.7998.7
NHibernateStoredProcedure0.810.1102.0516.21033.8

The following chart shows the results of 1,000 runs. As you can see, the slowest implementation is NHibernate with strong type query, e.g. using NHibernate.IQuery.List(), it took 6,841 ms. EF with LinqToEntities (non-compiled) is a little bit faster (5,398 ms). Using NHibernate without strong type query, e.g. using NHibernate.IQuery.List() is significant faster (3,647 ms) than its strong type equivalent (6,841 ms). Here, we can see the performance improvement when using EF compiled Linq queries (1,071 ms) in comparison with EF non compiled Linq To Entities (5,398 ms). The EF compiled Linq query implementation showed performance equivalent to EF with stored procedures, and NHibernate with native query and stored procedure. And finally, the performance of Dapper (679-685 ms) is again very close to the plain ADO.NET code (644-658 ms), with minimal performance impact.


The performance improvements using Dapper in comparison with Entity Framework and NHibernate is very significant. For database reads that need a high performance execution, you might consider using Dapper to get the best performance with some good abstraction from plain ADO.NET code.

The source code of these tests is available at github and I will continue to work on it to incorporate any feedback and also add additional tests for adding, deleting and updating records. Stay tunned!

Tuesday, October 11, 2011

Using Powershell with Application Pools

While developing Web applications and services using IIS, you need to recycle the application once in a while. You could just run iisreset, but sometimes you only want to affect one specific application pool. If you are using IIS7 or later, you could use appcmd.exe, but if you have some legacy then one option would be powershell. For example, to recycle the ASP.NET 4 application pool using powershell, you can run:

# Recycle the ASP.NET 4 application pool
([ADSI] "IIS://localhost/W3SVC/AppPools/ASP.NET v4.0").psbase.invoke("recycle")

# Stop the ASP.NET 4 application pool:
([ADSI] "IIS://localhost/W3SVC/AppPools/ASP.NET v4.0").psbase.invoke("stop")

# Start the ASP.NET 4 application pool:
([ADSI] "IIS://localhost/W3SVC/AppPools/ASP.NET v4.0").psbase.invoke("start")

# Verify the ASP.NET 4 application pool state: 1 = starting, 2 = started, 3 = stopping, 4 = stopped
([ADSI] "IIS://localhost/W3SVC/AppPools/ASP.NET v4.0").AppPoolState
Enjoy!

Sunday, October 09, 2011

Avoiding MvcBuildViews build time impact in developers environment by using ASP.NET compiler as an external tool


ASP.NET Razor views (.cshtml) and Web Form views (.aspx) are only compiled by the Web server when they are needed (runtime) and not by Visual Studio when you build your solution (build time). The MvcBuildViews option enables a post-build task (AspNetCompiler Task) to compile your ASP.NET views during build time, thus enabling you to catch syntax errors in the views earlier in the development process. However, enabling MvcBuildViews can significantly increase your build time depending on the size of the Web project you are working on.

To avoid the build time increase, you can enable MvcBuildViews in your build environment only (Release builds) and developers can use the aspnet_compiler.exe as an Visual Studio external tool to precompile ASP.NET views on demand instead of on every build to avoid the building time impact. This way, you will get the best of both worlds: be able to compile views at build time, but not on every build, only when you need it.

To enable MvcBuildViews in release builds only, you can follow the steps described here. To use aspnet_compiler.exe as an external tool in Visual Studio:

  • Go to Visual Studio Tools menu, and click on External Tools
  • Enter a title and consider adding a shortcut. I am using Compile ASP.NET &Views and I just use the short cut ALT+T+V to run it.
  • Enter the ASP.NET compiler command. For .NET 4, it is C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\aspnet_compiler.exe.
  • Add the following arguments: -c -v temp -p $(ProjectDir)  -  (option -c forces a full rebuild; option -v defines the virtual path of the application to be compiled, if you use temp, it will be in C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files\temp; option -p defines the physical path of the application to be compiled, by using $(ProjectDir), it will compile the views of the current project in Visual Studio).
  • Enter the initial directory: $(ProjectDir)
  • I use the output window, which makes easier if you have errors and want to open the file with errors by double clicking on it.
  • Press OK and you are all set. Just make sure to run this command when you are in your ASP.NET web project, or selecting or editing any of its files.

Alternately, you could import the following registry key:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\10.0\External Tools\Compile ASP.NET &Views]
"ToolCmd"="C:\\WINDOWS\\Microsoft.NET\\Framework\\v4.0.30319\\aspnet_compiler.exe"
"ToolDir"="$(ProjectDir)"
"ToolArg"="-c -v temp -p $(ProjectDir)"
"ToolOpt"=dword:00000018

One advantage of using the aspnet_compiler.exe as external tool is to be able to continue work on Visual Studio 2010 while watching the results in the output window. This is way better than using MvcBuildViews in developers build since Visual Studio 2010 hangs and display that annoying message that it is busy.

Happy building!

Tuesday, August 23, 2011

Managing Transactions with Entity Framework 4

The Entity Framework already supports native database transactions. When using the ObjectContext.SaveChanges method, it already operates within a database transaction. If any dirty ObjectStateEntry object cannot be persisted, then ObjectContext.SaveChanges will roll back the transaction and throw an exception. So, if you are working with only one object context then you have already built-in support for database transactions when using the ObjectContext.SaveChanges method.

However, there are other complex scenarios where you want to update a record in a database and add an entry to a message queue; or add, update, or delete records in different databases. In this situation, if you need these operations to be ACID, then you will need a distributed transaction that spans multiple databases and systems. A distributed transaction is a kind of transaction that requires the enlistment of additional resource managers.

If you have a complex persistence scenario as mentioned above, then you can use the TransactionScope class. This class uses the Microsoft Distributed Transaction Coordinator (MSDTC or DTC) which is a Windows service that coordinate transactions over different resource managers across multiple computers. Using MSDTC is an expensive task, however TransactionScope only uses MSDTC if it is necessary. If multiple instances of ObjectContext connect to the same database, then it uses a standard database transaction (SqlTransaction, OleDbTransaction). If there are multiple databases to be connected, then the transaction is promoted to MSDTC. If you are using SQL Server, transaction promotions in SQL Server 2008 happens less frequently than in SQL Server 2005, so SQL Server 2008 is more efficient in this matter. You can use the SQL Profiler tool to monitor how many transactions are promoted to MSDTC.

When using TransactionScope with multiple instances of ObjectContext, you should not use the parameterless ObjectContext.SaveChanges() method. Instead, you should use the ObjectContext.SaveChanges(SaveOptions) method and pass SaveOptions.DetectChangesBeforeSave. The reason is to maintain the ObjectContext state if something fails, otherwise the ObjectContext will be in a bad state, i.e., thinking that the changes were successfully committed when they were actually rolled back. When all operations within the transaction are successfully completed, then you should call the TransactionScope.Complete method, and after that it is safe to call ObjectContext.AcceptAllChanges().

Below you can see an example based on this post, but using the SaveChanges(SaveOptions) method instead of the obsolete SaveOptions(Boolean):

using (TransactionScope scope = new TransactionScope())
{
    // Save changes but maintain context1 current state.
    context1.SaveChanges(SaveOptions.DetectChangesBeforeSave);

    // Save changes but maintain context2 current state.
    context2.SaveChanges(SaveOptions.DetectChangesBeforeSave);

    // Commit succeeded since we got here, then completes the transaction.
    scope.Complete();

    // Now it is safe to update context state.
    context1.AcceptAllChanges();
    context2.AcceptAllChanges();
}

That is, the example above updates two databases within a distributed transaction and ObjectContext instances are only updated if the transaction is successfully committed. For an example of a transaction fail-retry scenario and using a database and a message queue, see the following link: http://msdn.microsoft.com/en-us/library/bb738523.aspx

Saturday, April 09, 2011

Extending the jQuery UI datepicker with new shortcuts

The jQuery UI datepicker has a set of default shortcuts to move to the next/previous month (PageDown/PageUp), to today's date (Ctrl+Home), etc. After working with QuickBooks for so long, I got used to the QuickBooks date control shortcuts that use letters instead of Ctrl+key combination like the jQuery UI datepicker. These shortcuts are very intuitive, e.g. the letter W is used for the first day of the Week and the letter K is used for the last day of the weeK. Here, we will show how to extend the datepicker to support the additional shortcuts below.

Shortcut Description
+ Next Day
- Previous Day
T Today
W First day of the Week
K Last day of the weeK
M First day of the Month
H Last day of the montH
Y First day of the Year
R Last day of the yeaR

The ideal solution would be to use the widget factory to subclass the datepicker and extend the _doKeyPress function with the new shortcuts. However, the datepicker does not use the widget factory in jquery-ui 1.8, see more details here. The solution is to extend the datepicker with a new function customKeyPress and then bind it to the datepicker keypress event.
/*!
 * Extensions for jQuery UI 1.8.7 datepicker
 *
 * Copyright 2011, Luis Rocha
 * Released under the MIT license.
 *
 */

// Extends datepicker with shortcuts for today, begin and end of the year and month.
$.extend($.datepicker, { customKeyPress: function (event) {
    var inst = $.datepicker._getInst(event.target);
    var c = String.fromCharCode(event.which).toLowerCase();
    switch (c) {
        case "y":
            // First day of the (y)ear.
            if (inst.selectedDay == 1 && inst.selectedMonth == 0) {
                // First day of previous year.
                $.datepicker._adjustDate(event.target, -12, "M");
            } else if (inst.selectedMonth == 0) {
                // First day of the current year.
                $.datepicker._adjustDate(event.target, 1 - inst.selectedDay, "D");
            } else {
                // First day of the current year.
                inst.selectedDay = 1;
                $.datepicker._adjustDate(event.target, -inst.selectedMonth, "M");
            }
            break;
        case "r":
            // Last day of the yea(r).
            if (inst.selectedDay == 31 && inst.selectedMonth == 11) {
                // Last day of next year.
                $.datepicker._adjustDate(event.target, +12, "M");
            } else if (inst.selectedMonth == 11) {
                // Last day of current year.
                $.datepicker._adjustDate(event.target, 31 - inst.selectedDay, "D");
            } else {
                // Last day of current year.
                inst.selectedDay = 31;
                $.datepicker._adjustDate(event.target, 11 - inst.selectedMonth, "M");
            }
            break;
        case "m":
            // First day of the (m)onth.
            if (inst.selectedDay == 1) {
                $.datepicker._adjustDate(event.target, -1, "M");
            } else {
                $.datepicker._adjustDate(event.target, 1 - inst.selectedDay, "D");
            }
            break;
        case "h":
            // Last day of the mont(h).
            var end = $.datepicker._getDaysInMonth(inst.selectedYear, inst.selectedMonth);
            if (inst.selectedDay == end) {
                var month = (inst.selectedMonth + 1) % 11;
                var year = inst.selectedYear + Math.floor((inst.selectedMonth + 1) / 11);
                inst.selectedDay = $.datepicker._getDaysInMonth(year, month);
                $.datepicker._adjustDate(event.target, +1, "M");
            } else {
                $.datepicker._adjustDate(event.target, end - inst.selectedDay, "D");
            }
            break;
        case "w":
            // First day of the (w)eek.
            var date = new Date(inst.selectedYear, inst.selectedMonth, inst.selectedDay);
            var offset = (date.getDay() > 0 ? date.getDay() : 7);
            $.datepicker._adjustDate(event.target, -offset, "D");
            break;
        case "k":
            // Last day of the wee(k).
            var date = new Date(inst.selectedYear, inst.selectedMonth, inst.selectedDay);
            var offset = (date.getDay() < 6 ? 6 - date.getDay() : 7);
            $.datepicker._adjustDate(event.target, offset, "D");
            break;
        case "t":
            // Today (same as Ctrl+Home).
            $.datepicker._gotoToday(event.target);
            break;
        case "+":
            // Increase day (same as Ctrl+Right).
            $.datepicker._adjustDate(event.target, +1, 'D');
            break;
        case "-":
            // Decrease day (same as Ctrl+Left).
            $.datepicker._adjustDate(event.target, -1, 'D');
            break;
    }
}
});
The following code snippet shows how to bind the customKeyPress function to the datepicker keypress:
$(function() {
    $("#datepicker").datepicker().keypress(function (event) { 
        $.datepicker.customKeyPress(event);
    });
});
Once the datepicker is refactored to use the widget factory (maybe in jquery-ui 1.9??), I will update this code to use the widget factory solution instead. For future updates, see https://github.com/lerocha/jquery-ui-extensions.

A demo of this extended datepicker is available here.