Benchmarking: ADO.NET Entity Framework vs Subsonic

ADO.NET Entity Framework vs Subsonic: performance benchmarking and other comparisons

Entity Framework is a relatively new feature of .NET, for automatically generating and maintaining the data access layer and business objects in an application. The developer can simply point the framework at the database using a simple wizard in the Visual Studio IDE, click "Finish", and the work is done. Data is then available in every imaginable way with simple, intuitive properties and methods in unmanaged code. Strongly-typed objects are also available for every data structure.

Subsonic is an open source command line utility which does a similar job. It's very well established and has been widely adopted.

There have been various attempts at solutions for this approach to rapid development, broadly known as object-relational mapping. But how good is the generated code, and how good are the libraries? There are numerous measures that would be useful in comparing any two ORM products, both qualitative and quantitative. I aim to test all of them. My initial test was to compare Entity Framework and Subsonic for the simple task of loading all records in a table. I created a basic table with 1000 rows and loaded all of the data into strongly-typed lists 100 times in an ASP.NET MVC web page. I loaded the web page 5 times, allowing 5 seconds between each refresh, and recorded the time taken for the loop to execute.

Entity Framework is much faster than Subsonic.

This speed difference between Entity Framework and Subsonic is greatest in the first burst of database reads. I observed in other tests that the gap between the two increases exponentially with the number of records. Perfect integration with Visual Studio also makes EF much quicker and easier to set up and maintain. The code the developer needs to write to utilise EF was simpler and clearer, although in both cases it is minimal (1 line).

Raw data from the first test below. Downloadable "try it yourself" project and source code from my tests coming soon... More data coming soon... More tests coming soon...

Entity Framework Subsonic (milliseconds)
1312 3937
140 1906
156 1062
140 1078
140 1062

01 June 2009

Share the love:

Comments: 18

Add Comment

marc scheuner (20 Jul 09, 15:24)

Which version of Subsonic where you using in your comparisons? What kind of operations did you time?

Rob Conery (21 Jul 09, 05:08)

EF is *not* faster than SubSonic and I think your tests might be a bit flawed. I have hammered SubSonic WRT to perf and put it up against Linq to Sql and ADO (and ADO wins) with 10,000 data operations in 2.34 seconds - your numbers just aren't right. Or your tests are weird.

Tim Acheson (21 Jul 09, 09:18)

Thanks for your response, Bob. Rest assured that the measurements on this page are accurate and reproducible results from the simple test procedure outlined above. As promised, I'll be providing source code along with more raw test results as soon as I've finished performing the benchmarking exercises.

Perhaps you would you be interested in posting another comment here with performance measurements from your own tests and an outline of your methodology (ideally showing the line(s) of code in which the library is executed).

Tim Acheson (21 Jul 09, 10:25)

In the meantime, suffice to say that the test was very clear and simple -- a loop containing one line of code. In the one line of code being tested, rows were loaded into a typed list directly from a database table using either Entity Framework or Subsonic.

Entity Framework test:

var itemList = testModel.TestItemSet.ToList();

Subsonic test:

DB.Select().From("TestItems").ExecuteTypedList<TestItem>();

This is literally the simplest test possible, involving nothing more than one database table and a loop of a single line of code. It highlights the slower transit of data through Subsonic, as well as the slower loading of data into the data entity by Subsonic. Obviously it's worth doing a much wider range of tests, but this is one legitimate test.

Tim Acheson (21 Jul 09, 10:38)

For reference, here are the results of a slightly different version of the same test, executing exactly the same database operation, but not reading the data into a typed list.

Entity Framework Subsonic (milliseconds)
828 2312
48 93
31 109
31 109
31 109
Tim Acheson (21 Jul 09, 11:15)

For reference, results from a loop of 50 calls to a stored procedure containing a simple select all query, loading the returned data into the a typed list of the generated entity.

Entity Framework code (50 iterations):

var itemList = testModel.TestItemsSelectAll().ToList();

Subsonic code (50 iterations):

var itemList = SPs.TestItemsSelectAll().ExecuteTypedList<TestItem>();

Entity Framework Subsonic (milliseconds)
390 1390
156 1109
156 1078
156 1078
156 1093

I have been developing my own code generator over the last few years whenever I have any spare time. There are loads out there and it is hard to evaulate every one of them.

The tool can be found here and is free http://www.elencysolutions.co.uk

The generator creates stored procedures for all of your CRUD operations and using SqlDataReaders for all retrieval operations. I have done my own benchmark testing which is on the site and it is more performant than linq and the entity framework.

Tim Acheson (15 Jan 10, 17:10)

I'll try that out, Lee! How does your solution compare with Fluent NHibernate? Does it exhibit lazy loading?


FrameworkGen doesn't handle lazy loading! Stop being so lazy ;)

You can load an entity and specify a depth of loading e.g. businessObject.GetByCustomerID(customerID, depth).

Tim Acheson (21 Jan 10, 12:15)

Ok, well I'll certainly give FrameworkGen a try.


Hi all,

Just made a test with Subsonic 3 with 958 rows data in a table named Posts with 3 columns where 2 of them are int and 3rd one is varchar.

for (int j = 0; j < 5; j++)
{
System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
watch.Start();
for (int i = 0; i < 100; i++)
{
var DB = new myDB();
List<Post> data = DB.Select.From("Posts").ExecuteTypedList<Post>();
}
watch.Stop();
System.Diagnostics.Debug.WriteLine("Try #" + j.ToString() + ": " + watch.ElapsedMilliseconds.ToString() + " ms");
System.Threading.Thread.Sleep(5000);
}

Debug output result:

Try #0: 1752 ms
Try #1: 1744 ms
Try #2: 1769 ms
Try #3: 1722 ms
Try #4: 1706 ms

Tim Acheson (08 Feb 10, 10:06)

Thanks, these are interesting results. I want to look at Subsonic 3 some more, and also Fluent NHibernate.

Charles Chen (17 Feb 10, 19:26)

Why not release the source (DDL and all) and let the masses independently verify those discrepancies instead.

Tim Acheson (20 Feb 10, 12:30)

I do intend to upload it to my SkyDrive. It's just 50 iterations of the exact 1 line of code I specified above, so it's easy to try and replicate in the meantime! :)


Frameworkgen b4 has now been released which includes new entity caching features and inversion of control for data components.

Tim Acheson (25 Jul 10, 17:01)

Hi Lee, yes I like the look of your FramworkGen ORM. :)


So where is the downloadable try-it-yourself project?

Robin Embry (31 Oct 11, 12:31)

How would I add a property (that will not be written to the DB) to one of my entities?

If 'lazy loaded', I would add the following property to the entity’s class via [Entity]Ex.cs (under partial classes):

private int _parentId;
[XmlElement(ElementName = "ParentID")]
public int ParentID
{
get
{
if (_parentId == -1)
{
//set _parentId
}
return _parentId;
}
}

I would then need to call an [Entity]Manger.cs (partial class) to retrieve the data to set _parentId?

What is the correct way to do this with FrameworkGen?

Tags:


  • Twitter
  • LinkedIn
  • Facebook
  • Windows Live / Messenger
  • Xbox Live
  • RSS
  • Email