Entity Framework 6 Performance of Async methods vs Sync methods

Question

I've modified a Github project to test for some more Entity Framework related ways of doing queries.

Models:

[Table("Player")]
public partial class Player
{
    public int Id { get; set; }

    [Required]
    [StringLength(200)]
    public string FirstName { get; set; }

    [Required]
    [StringLength(200)]
    public string LastName { get; set; }

    public DateTime DateOfBirth { get; set; }

    public int TeamId { get; set; }

    public virtual Team Team { get; set; }
}

[Table("Team")]
public partial class Team
{
    public Team()
    {
        Players = new HashSet<Player>();
    }

    public int Id { get; set; }

    [Required]
    [StringLength(200)]
    public string Name { get; set; }

    public DateTime FoundingDate { get; set; }

    public int SportId { get; set; }

    public virtual ICollection<Player> Players { get; set; }

    public virtual Sport Sport { get; set; }
}

[Table("Sport")]
public partial class Sport
{
    public Sport()
    {
        Teams = new HashSet<Team>();
    }

    public int Id { get; set; }

    [StringLength(100)]
    public string Name { get; set; }

    public virtual ICollection<Team> Teams { get; set; }
}

What i am testing for is these things:

EF "Sync" - FirstOrDefault() and ToList()

context.Players.FirstOrDefault(x => x.Id == id);

context.Players.AsNoTracking().Where(x => x.TeamId == teamId).ToList();

context.Teams.AsNoTracking().Include(x => x.Players).Where(x => x.SportId == sportId).ToList();

EF "Async" - FirstOrDefaultAsync() and ToListAsync()

await context.Players.FirstOrDefaultAsync(x => x.Id == id);

await context.Players.AsNoTracking().Where(x => x.TeamId == teamId).ToListAsync();

await context.Teams.AsNoTracking().Include(x => x.Players).Where(x => x.SportId == sportId).ToListAsync();

EF "Sync" with Select into DTO objects

context.Players.Select(p => new PlayerDTO()
                {
                    Id = p.Id,
                    FirstName = p.FirstName,
                    DateOfBirth = p.DateOfBirth,
                    LastName = p.LastName,
                    TeamId = p.TeamId

                }).FirstOrDefault(x => x.Id == id);

context.Players.Where(x => x.TeamId == teamId).Select(p => new PlayerDTO()
                {
                    Id = p.Id,
                    FirstName = p.FirstName,
                    DateOfBirth = p.DateOfBirth,
                    LastName = p.LastName,
                    TeamId = p.TeamId
                }).ToList();

context.Teams.Where(x => x.SportId == sportId).Select(t => new TeamDTO()
                    {
                        Id = t.Id,
                        FoundingDate = t.FoundingDate,
                        Name = t.Name,
                        SportId = t.SportId,
                        Players = t.Players.Select(p => new PlayerDTO()
                        {
                            Id = p.Id,
                            FirstName = p.FirstName,
                            DateOfBirth = p.DateOfBirth,
                            LastName = p.LastName,
                            TeamId = p.TeamId
                        }).ToList()
                    }).ToList();

EF "Async" with Select into DTO objects

await context.Players.Select(p => new PlayerDTO()
                {
                    Id = p.Id,
                    FirstName = p.FirstName,
                    DateOfBirth = p.DateOfBirth,
                    LastName = p.LastName,
                    TeamId = p.TeamId

                }).FirstOrDefaultAsync(x => x.Id == id);

 await context.Players.Where(x => x.TeamId == teamId).Select(p => new PlayerDTO()
                {
                    Id = p.Id,
                    FirstName = p.FirstName,
                    DateOfBirth = p.DateOfBirth,
                    LastName = p.LastName,
                    TeamId = p.TeamId
                }).ToListAsync();

await context.Teams.Where(x => x.SportId == sportId).Select(t => new TeamDTO()
                {
                    Id = t.Id,
                    FoundingDate = t.FoundingDate,
                    Name = t.Name,
                    SportId = t.SportId,
                    Players = t.Players.Select(p => new PlayerDTO()
                    {
                        Id = p.Id,
                        FirstName = p.FirstName,
                        DateOfBirth = p.DateOfBirth,
                        LastName = p.LastName,
                        TeamId = p.TeamId
                    }).ToList()
                }).ToListAsync();

my result are quite troublesome i think. mostly because MS is promoting the Async part of EF alot.

My results: (in miliseconds)

Number of Sports: 8, Number of Teams: 30, Number of Players: 100

EntityFrameworkAsyncDTO Results
Run #   Player by ID            Players per Team                Teams per Sport
0               1,46                    3,47                    35,88
1               1,04                    3                       33
2               1,02                    3,3                     33,75
3               1,03                    3                       31,75
4               1,1                     3,27                    31,38
EntityFrameworkAsync Results
Run #   Player by ID            Players per Team                Teams per Sport
0               1,17                    3,53                    57
1               1,01                    3                       48,62
2               0,99                    3,03                    47,88
3               1,02                    3,07                    51
4               1                       3,03                    48,88
EntityFrameworkDTO Results
Run #   Player by ID            Players per Team                Teams per Sport
0               0,02                    3                       13,25
1               0,01                    2,8                     13,12
2               0,15                    2,97                    13,25
3               0,02                    2,9                     13,25
4               0,05                    2,8                     13,12
EntityFramework Results
Run #   Player by ID            Players per Team                Teams per Sport
0               0,01                    2,27                    28,38
1               0,01                    2,4                     28,25
2               0                       2,13                    28,5
3               0,01                    2,17                    27,5
4               0,01                    2,13                    29
ADONET Results
Run #   Player by ID            Players per Team                Teams per Sport
0               0                       2,03                    11,75
1               0                       2                       12,62
2               0                       2                       11,38
3               0                       2                       12,38
4               0                       2                       11,25

/EDIT I've added the ADO.NET times to the result list as a reference.

My question is: Am i doing something wrong with my queries since the load times are so different ?

In a real world system i have running, i have changed out ALL my Async DB calls to Sync calls, and for what i can see in my statistics the system are seeing a ~50% speed increase of DB calls. This is a system with 100-120 cuncurrent users connected via SignalR, it is very DB heavy.

You want to compare First vs FirstAsync and Find vs FindAsync. First and Find doing quite some different amount of work. — vittore, Nov 04 '16 at 13:36
Also, `Async` is not "faster". It adds a lot of overhead. But it allows to use time when you are waiting for say network communication. — vittore, Nov 04 '16 at 13:37
I do know that the Async/Await has an overhead, but i would not think that my SQL calls via EF would be 2-4 times slower. — Paw Ormstrup Madsen, Nov 04 '16 at 13:52
i know Find is not the same as First. I've changed the code to use First instead of Find — Paw Ormstrup Madsen, Nov 04 '16 at 13:53
Async can also become way worse when using large column such as (varbinary(MAX), varchar(MAX), nvarchar(MAX) or XML) see: https://stackoverflow.com/questions/28543293/entity-framework-async-operation-takes-ten-times-as-long-to-complete/28619983#28619983 — Jonathan Magnan, Jan 11 '19 at 13:56

score 2 · Answer 1 · answered Nov 04 '16 at 13:41

I think this article explains it in details.

Async is not faster and adds a lot of overhead on top of normal "sync" code. But it allows to use resources better in cases when you are wasting your time waiting (e g high latency on network connection etc)

score 1 · Answer 2 · answered Jan 10 '19 at 20:32

I agree with the other contributors, if you run your async tests as follows:

var tskPlayers = context.Players.FirstOrDefaultAsync(x => x.Id == id);

var tskPlayersNt = context.Players.AsNoTracking().Where(x => x.TeamId == teamId).ToListAsync();

var tskTeamsNt = context.Teams.AsNoTracking().Include(x => x.Players).Where(x => x.SportId == sportId).ToListAsync();

Task.WhenAll(tskPlayers, tskPlayersNt, tskTeamsNt);

You will see better results.

score 0 · Answer 3 · answered Jan 02 '19 at 15:38

0

Async methods imply more IL code, thus explaining those differences for one-shot calls.

You could gain benefits of async methods if you make other async calls in your method.

Another interesting article : https://www.thereformedprogrammer.net/analysis-of-entity-framework-6-async-performance/

answered Jan 02 '19 at 15:38

Kevin Dimey

709
6
15

Tests are run multiple times. – Paw Ormstrup Madsen Jan 07 '19 at 12:12

Entity Framework 6 Performance of Async methods vs Sync methods

3 Answers3