Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Entity Framework performance degradation

I have a ridiculously simple edmx model, with one database and one entity for one database table. Testing sequential retrieval of all records from a local virtual database server.

using System;
using System.Linq;

namespace ConsoleApplication1
{
    using System.Diagnostics;

    private static readonly ConcurrentBag<long> Stats = new ConcurrentBag<long>();
    static void Main(string[] args)
    {
        for (int i = 0; i < 5; i++)
        {
            var sw = Stopwatch.StartNew();
            using (var ctx = new ScratchEntities())
            {
                foreach (var invoice in ctx.Invoices.Select(
                    s => new
                         {
                             s.Id,
                             s.Amount,
                             s.DueDate
                         }))
                {

                }
            }
            sw.Stop();
            Stats.Add(sw.ElapsedMilliseconds);
        }
        Console.WriteLine("Min {0}    Max {1}    Avg {2}", Stats.Min(), Stats.Max(), Stats.Average());

    }
}

Under EF 4 and 5, it took around 2-2.5s to execute. Upgraded to Nuget's EF6, almost 10s.

On a similar note, it also takes 4-5 times as long when I use foreach over ctx.Invoices, as compared to foreach over ctx.Invoices.Select(s=>new{all columns here}).

Puzzled.

UPDATE:

Iterating 5 times

EF 6.0.1 Min 3082 Max 9231 Avg 4462.8

EF 5.0.0 Min 1502 Max 2016 Avg 1665.4

All times in milliseconds for 1M rows.

.Net 4.5

UPDATE 2:

Discarding first five tests out of a 105

EF 6.0.1 Min 2698 Max 4383 Avg 3136

EF 5.0.0 Min 1426 Max 2922 Avg 1628

UPDATE 3:

After recreating TT templates per advise here

EF 6.0.1 Min 2589 Max 4279 Avg 3027

After compiling EF6 with NGEN described here

EF 6.0.1 Min 2644 Max 4322 Avg 3045.7

UPDATE 4:

After changing to AsEnumerable

EF 6.0.1 Min 2661 Max 4658 Avg 3134.6

like image 320
Darek Avatar asked Oct 21 '22 21:10

Darek


1 Answers

In EF6 we changed the default data retrieval behavior from streaming to buffering (see here for why). Buffering adds additional overhead, which is particularly visible when executing no-tracking queries such as projections into unmapped CLR types or via the AsNoTracking extension method. To alleviate the issue, we added the AsStreaming extension method that turns off buffering on a per-query basis. Using AsStreaming should give you EF5 like performance.

like image 174
Andrew Peters Avatar answered Oct 24 '22 01:10

Andrew Peters