Saturday, November 30, 2013

Entity Framework 6 vs. LINQ-to-SQL smackdown!

Today, I'm doing my own performance testing on Entity Framework 6 and LINQ to SQL.  I created two tables in a sample SQL server database.  Here is the ERD for the two tables I created:


Next, I created two console application projects.  One to test LINQ to SQL and the other with EF-6.  You can download the LINQ to SQL test project here: linq2sqltest.zip and the EF6 test project here: ef6test.zip.

I started with the LINQ to SQL test using a basic insert loop like this:

for (int j = 0; j < 10; j++)
{
    for (int i = 0; i < 1000; i++)
    {
        person personRecord = new person()
        {
            first = firstnames[i],
            last = lastnames[i],
            department = 1
         };


         db.persons.InsertOnSubmit(personRecord);
    }
}

db.SubmitChanges();


I used the same code for the normal EF6 test:

for (int j = 0; j < 10; j++)
{
    for (int i = 0; i < 1000; i++)
    {
        person personRecord = new person()
        {
            first = firstnames[i],
            last = lastnames[i],
            department = 1
         };


         db.persons.Add(personRecord);
    }
}

db.SaveChanges();

I am using EF 6 version 6.0.1 as of this writing.  Microsoft has indicated that they are working on a bug fix version of EF6 that will be version 6.0.2.  There is no expected date when that version will become available, so keep your eyes open for this one.

In order to make the test measurable, I downloaded two text files full of first and last names with at least 1000 rows.  Then I ran the loop through 10 times to make it 10,000 rows of data inserted into the person table.  You can download the text files from the census like I did (if you want to include more names) by going to this stack overflow article and click on the suggested links: Raw list of person names.

I also attempted to move the db.SaveChanges(); inside the loop to see what effect that would have on the timing, and received the expected result of slower-than-dirt!  So I did some research to find a method to speed up the inserts and came across this stack overflow hint on bulk inserts: EF codefirst bulk insert.  By changing the configuration of the context before the inserts were performed, I was able to increase the insert speed significantly:

db.Configuration.AutoDetectChangesEnabled = false;
db.Configuration.ValidateOnSaveEnabled = false;

Here are the final results of all four tests:


The first test is the base-line LINQ-to-SQL.  I did not attempt to optimize this query (and I'm sure there is a way to make it go faster as well).  My interest was to see if I can make EF6 perform as fast or faster than straight LINQ-to-SQL.  The first test is the slow test where I put the SaveChanges() inside the loop (epic fail).  T2 is the second test, which is shown in the code above.  The third test (T3) is the code where I added the two configuration changing lines before running the insert loops.  All timings are in seconds.  As you can see from the results LINQ-to-SQL, un-optimized ran for 18.985 seconds to insert 10,000 records.  EF6 in test 3 ran for 2.371 seconds.  Just using the same code in EF6 produced a poor result at just over 46 seconds.  Be conscious of the time that EF6 is going to take some extra work to make it perform.

UPDATE: I have since written a test for NHibernate inserts and re-ran the above tests.  The LINQ-to-SQL test was able to perform the same inserts at a measured time of 3.49 seconds.  Because of this discrepency, I re-ran all tests several times and it seems that all the measurements are close except for the LINQ-to-SQL.  This is not a scientifically accurate test and I would recommend downloading my examples and run your own tests on your own hardware.