Performance analyze ADO.NET and Entity Framework

Question

Which one gives better performance? ADO.NET or Entity Framework.

These are the two method I want to analyze.

ADO.NET Test Method

public void ADOTest()
{
    Stopwatch stopwatch = Stopwatch.StartNew();
    using (SqlConnection con = new SqlConnection(connection))
    {
        string Query = "select * from Product ";
        SqlDataAdapter da = new SqlDataAdapter(Query, con);
        DataSet ds = new DataSet();
        con.Open();
        da.Fill(ds);
        DataView dv = ds.Tables[0].DefaultView;
    }
    stopwatch.Stop();
    Console.WriteLine("ADO.NET Time Elapsed={0}", stopwatch.Elapsed);
}

Entity Framework Test Method

public void EFTest()
{
    Stopwatch stopwatch = Stopwatch.StartNew();
    var list = _OnlineStoreEntities.Products.ToList();
    stopwatch.Stop();
    Console.WriteLine("Entity Framework Elapsed={0}", stopwatch.Elapsed);
}

Result in first time execution

When I ran this above method in more than 100 times. The average execution time is shown in the image:

ADO.NET took only 2 milliseconds whether Entity Framework took more than 4 milliseconds.

Result in second time execution

When I ran this method again and again in single run. The average execution time between ADO.NET and EF is not much more:

Question

1. I think EF gives very worst performance in first time execution Then why we use EF?
2. Why EF second time execution was faster than first time execution?

Solution 1

1. First time EF loads metadata into memory, that takes a time. It builds in-memory representation of model from edmx file, or from source code if you are using code first. Actually EF is build at the top of ADO.NET, so it can't be faster. But it makes development much faster. And improves maintainability of your code.
2. See 1

Take a look on msdn article Performance Considerations (Entity Framework)

Solution 2

• 1) EF makes a lot of things more comfortable when working with databases. There is a lot going on under the hood that you otherwise would have to code manually.

For example, one of my first bigger projects was dealing a lot with data and I implemented the access layer with ADO.NET. This made up for something between a quarter or even a third of the whole project.

With my experience of the EF today I could get rid of nearly all of that! I just makes lots of the complicated code I wrote by hand completely unneccessary. We are talking about thousands of lines here.

• 2) Two main reasons here. First, EF is built on top of using ADO.NET. This means that everthing EF does, add more overhead to whatever ADO would do. Second (very) simply put, the JIT compiler compiles the code for the first time just when it is executed. This includes memory allocation and all sorts of initializations.

This means that code you run multiple times runs much faster from the second time on. If you execute your EF queries only once, on the other hand, you will have no gains from those initializations.

In a real world application you might try to do some optimizations like using Compiled Queries. Performance wise this will help you a lot because now your queries do not need to be prepared and compiled each time you run them but only once.

Solution 3

While working at Microsoft, I wrote a blog post comparing the performance of both. Seems it is now in the process of being migrated, so you may need to go to the Internet archive to find it...

We had focused a lot on making sure the performance cost of using EF was not terrible, not perfect in V1 but quite usable.

While almost 10 years later the EF team has done a good work improving performance, particularly reducing the bad case scenarios, by design the Entity Framework sits over ADO.Net. So if your primary criteria is raw performance, you should go for ADO.Net, with hand-optimized SQL.

That being said, many otherwise good developers, don't craft the best SQL; Entity Framework isolates them from writing the queries, and uses good practices to produce reasonably good queries.

The major advantage of the Entity Framework is providing a higher level of abstraction to work with data, isolating the app developer from the underlying data model. So you would use the EF to be more productive, writing less data access code; you still can fine tune specific queries or data operations, without losing the abstraction that makes programming easier on the non-performance-critical code, which is the largest part of any business aplication, for example.

Solution 4

I think EF gives very worst performance in first time execution Then why we use EF?

1. Auto generated codes for data access layer
2. Reduces development time and cost
3. Allows LINQ queries as well.

Why EF second time execution was faster than first time execution?

Yes. One of the most important features of EF is caching.

View more solutions