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.

9 thoughts on “Entity Framework 6 vs. LINQ-to-SQL smackdown!

  1. Mblm is correct that copying bulk data from a flat file into the database would be better using bulkcopy. However, this article is just a speed test to compare the insert speed difference between LINQ-to-SQL vs. EF-6. I'm just using the flat file of names because it was an easy to obtain large chunk of data. An application might be inserting data from other sources (including an interface) and it might only be inserting a couple records at a time, but it's difficult to time something with only a couple of records.

  2. linq to sql beats ef (as of 6) on ease of use.
    ef 6 beats linq to sql on multiple database support.

    On peformance, things need to be said in category: linq 2 sql vs ef 6:
    1. default vs default, linq to sql is faster.
    2. default vs tuned, ef6 is faster.
    3. tuned vs tuned, not indicated in this article, but imo, faster should be faster.

  3. I was not happy with the fact that EF performed worse than Microsoft's Linq2Sql. I also was not happy that they dropped support for including tables from multiple databases. I'll try and retest these performance numbers with the latest version of EF and post the results to see if anything has changed.

Leave a Reply