Linq to Entities–Performance during search operation

Introduction

There are a lot of posts on net regarding this subject and to be honest you can conclude that the performance of Linq to Entities is poor.

I did perform some tests my self and here are some interesting facts. Hopefully this will help you to make a choice while you are designing your application.

Machine details (on which the tests were performed)

Test Preparation

I chose to have simple entity structure; Person and Address.

You can see the relationship clearly that a Person entity has a collection of Addresses. The test was performed in two stages;

Stage #1: Person without the collection of Addresses.

Stage #2: Person with empty collection of Addresses.

For both the stages, the collection of Person entity was loaded with about 10,000,000 records.

Code – to perform search using 3 techniques

Using For Loop (Traditional Run)

/// <summary>
/// Gets the list of person matching to first name and last name.
/// </summary>
/// <param name="firstName">First name of the person.</param>
/// <param name="lastName">Last name of the person.</param>
/// <returns>Person list that matches the criteria.</returns>
public List<Person> GetPersonByFirstNameUsingTraditionalMethod(string firstName, string lastName)
{
     List<Person> personList = new List<Person>();

     foreach (var person in _personList)
     {
          if (person.FirstName == firstName && person.LastName == lastName)
          {
              personList.Add(person);
          }
      }

      return personList;
}

Using Linq

/// <summary>
/// Gets the list of person matching to first name and last name.
/// </summary>
/// <param name="firstName">First name of the person.</param>
/// <param name="lastName">Last name of the person.</param>
/// <returns>Person list that matches the criteria.</returns>
public List<Person> GetPersonByFirstNameUsingLinq(string firstName, string lastName)
{
    var person = _personList.Where(p => p.FirstName == firstName && p.LastName == lastName);
    return person.ToList();
}

Using PLinq

/// <summary>
/// Gets the list of person matching to first name and last name.
/// </summary>
/// <param name="firstName">First name of the person.</param>
/// <param name="lastName">Last name of the person.</param>
/// <returns>Person list that matches the criteria.</returns>
public List<Person> GetPersonByFirstNameUsingPLinq(string firstName, string lastName)
{
    var person = _personList.AsParallel().Where(p => p.FirstName == firstName && p.LastName == lastName);
    return person.ToList();
}

Stage #1: Person without the collection of Addresses

From the Person entity class the property for collection of Addresses was commented out to ensure that the Person entity class was without the collection. The purpose of this test was to see how search performed (performance wise) with normal data-types in the collection.

public class Person { public int Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public int Age { get; set; } //public List<Address> Addresses { get; set; } }

20 runs were performed with the same data. This was just to eliminate any abnormal anomalies.

As you can see from the graphs, few interesting things;

1. The Linq search took by far the longest and averaged about 503.2 ms. It was nowhere near the traditional For Loop and Parallel Linq (PLinq).

2. For Loop gave pretty much steady runs at an average of 357.75 ms.

3. PLinq showed interesting characteristics. Although being the fastest of 3 techniques, the initial run took the same time as a for loop and gradually the time taken reduced. The average was about 294.55 ms.

Stage #2: Person with empty collection of Addresses

For this run the code that was commented in Stage # 1 was uncommented. But the collection was not loaded with any data. The purpose of this run was to see how the search operation performed in presence of an unconventional data type (which is always the case).

public class Person { public int Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public int Age { get; set; } public List<Address> Addresses { get; set; } }

20 runs were performed with the same data.

Again the graph showed quite a few interesting characteristics;

1. The Linq search again took the longest and averaged about 555.1 ms adding 51.9 ms on an average to Stage #1 runs.

2. For Loop again gave a steady run averaging 406.5 ms. It added 48.75 ms on an average to Stage #1 runs.

3. PLinq showed very interesting characteristics. The very first run took very long time as compared to any other (926 ms adding 557 ms to the first run in Stage #1). But the later runs settle, bringing the average down to 337.4 ms. The difference is about 42.85 ms on an average between the two Stage runs.

Note this is not even loading the Address List in Person collection. I am sure the numbers will be even higher when data will be loaded into Address Collection.

Conclusions

1. Linq to entities give a consistent poor performance. Hope this will improve when Microsoft introduces new versions of this technology.

2. PLinq is the fastest of all the techniques, but is influenced by the machine on which it is running (i.e. processor, RAM).

3. For Loop (traditional method) gives a consistent performance