CODEWITHSUNDEEP
c#ado.netentity-framework-6
Ketan
Ketan

Reputation: 413

Why is C# Ado.net slower than Entity Framework 6.1.3?

I wrote some code to compare performance of C# Ado.Net and Entity Framework 6.1.3. I am calling a stored procedure which returns around 20,000 employee records and then map this data into List of "Person" objects. Then I ran 1000 iterations of this code and calculated the average time.

Here are the timings:

To my surprise, Ado Net is around 100 ms slower than Entity Framework.

Ado.Net code: 

    //GetAllPersons is a stored proc hosted in Local DB instance
    var adapter = new SqlDataAdapter("GetAllPersons", conn);
    adapter.Fill(dt);
    //Using Fast member library
    var accessor = TypeAccessor.Create(typeof(Person));
    MemberSet members = accessor.GetMembers();
    var list = new List<Person>();
    foreach(DataRow row in dt.Rows)
    {
        var person = new Person();
        foreach (var member in members)
        {
            if (row[member.Name] != DBNull.Value)
            {
                accessor[person, member.Name] = row[member.Name];
            }
        }
        list.Add(person);
    }

Entity Framework:

        var context = new AdventureWorks2012Entities1();
        List<Person> list = context.GetAllPersons().ToList();

The part of the code which uses SQL Adatper to load Datatable is the one taking most of the time. I tried using SQL Datareader instead but it was even worse. Am I missing something here because supposedly plain Ado.Net should be faster than Entity Framework?

Upvotes: 2

Views: 816

Answers (2)

Quality Catalyst
Quality Catalyst

Reputation: 6795

The Entity Framework (EF) is based on Ado.Net; see What is Entity Framework? by the Entity Framework Tutorial. Consequently, EF cannot be faster than a pure Ado.Net implementation.

However, you implemented a less ideal solution with a loop in a loop and other overhead such as using reflection. I suppose the internal EF implementation is smarter than that and possibly makes use of the context initialization instead of having an inner loop.

Try this: Have only one single employee in your table and compare the results. EF is likely to be slower.

Upvotes: 0

zivkan
zivkan

Reputation: 15011

You wrote that you tried SqlDataReader, but it was even slower. It should be the fastest, but since you didn't show your code, we can't offer suggestions. But here are some generic tips:

1. Get column values by ordinal, not name.

Rather than using reader["column_name"], instead you should get the column ordinal, then use that. For example:

using (var reader = command.ExecuteReader())
{
  int col1Ordinal = reader.GetOrdinal("Column1");
  int col2Ordinal = reader.GetOrdinal("Column2");
  while (reader.Read())
  {
    int col1 = (int)reader[col1Ordinal];
    string col2 = (string)reader[col2Ordinal];
    // do something with col1 and col2's values
  }
}

2. Avoid multiple gets

The index operator is a method call, so avoid doing it multiple times for the same value. In your SqlDataAdapter code you wrote

if (row[member.Name] != DBNull.Value)
{
  accessor[person, member.Name] = row[member.Name];
}

As you can see, you're calling row[member.Name] twice. Instead, you should get it once and re-use the value

object value = row[member.Name];
if (value != DBNull.Value)
{
  accessor[person, member.Name] = value;
}

3. Avoid reflection

I've never heard of TypeAccessor or MemberSet before. From a quick search, it seems to be from a library called fast-member. Even if it's faster than .NET's built-in reflection, I'm sceptical about how fast it is. I know it's nice that it can significantly reduce the amount of code you have to write, particularly if your query has many columns. But, if you're trying to optimise for performance, particularly if you're unhappy with your code's performance compared to Entity Framework, you should remove that dependency and test what the performance difference is.

Upvotes: 3

Related Questions