Entity Framework 6 and Async() Performance

Question

i have a question about Entity Framework 6 and ToListAsync()/ToList().

if i have a little amount of data (around 1000 rows) i have noticed that ToListAsync() is faster than ToList().

But if i have a huge amount of data (around 600.000 rows) ToList() is faster than ToListAsync().

Here you can fine the code, someone can explain me why?.

//SharedAnagra contains around 1000 rows
static List<SharedAnagra> GetSharedAnagraList()
{
    using (Intranet2k3Entities intranet2K3Entities = new Intranet2k3Entities())
    {
        List<SharedAnagra> listaSharedAnagras = (from a in intranet2K3Entities.sharedAnagra
                                                 select new SharedAnagra
                                                 {
                                                     AnagraActive = a.AnagraActive,
                                                     AnagraId = a.AnagraID,
                                                     AnagraName = a.AnagraName,
                                                     OfficeId = a.OfficeID
                                                 }).ToList();

        return listaSharedAnagras;
    }
}

static async Task<List<SharedAnagra>> GetSharedAnagraListAsync()
{
    using (Intranet2k3Entities intranet2K3Entities = new Intranet2k3Entities())
    {
        List<SharedAnagra> listaSharedAnagras = await (from a in intranet2K3Entities.sharedAnagra
                                                       select new SharedAnagra
                                                       {
                                                           AnagraActive = a.AnagraActive,
                                                           AnagraId = a.AnagraID,
                                                           AnagraName = a.AnagraName,
                                                           OfficeId = a.OfficeID
                                                       }).ToListAsync();

        return listaSharedAnagras;
    }
} 


//sharedAnagraNDGClienti contains 950000 rows
static List<SharedAnagraNdgClienti> ListaSharedNdgClienti()
{
    using (Intranet2k3Entities intranet2K3Entities = new Intranet2k3Entities())
    {
        List<SharedAnagraNdgClienti> lista = (from a in intranet2K3Entities.sharedAnagraNDGClienti
                                              select new SharedAnagraNdgClienti
                                              {
                                                  Cm = a.CM,
                                                  CodFisc = a.CodFisc,
                                                  Cognome = a.Cognome,
                                                  Descrizione = a.Descrizione,
                                                  FilialePrevalente = a.Filiale_Prevalente,
                                                  Ndg = a.NDG,
                                                  Nome = a.Nome,
                                                  Telefono = a.Telefono,
                                                  Tipologia = a.Tipologia,
                                                  Trae = a.TRAE,
                                                  Tsae = a.TSAE
                                              }).AsNoTracking().ToList();

        return lista;
    }
}

static async Task<List<SharedAnagraNdgClienti>> GetIssueTypeByIdAsync()
{
    List<SharedAnagraNdgClienti> listaSharedAnagraNdgClienti;

    using (Intranet2k3Entities intranet2K3Entities = new Intranet2k3Entities())
    {
        listaSharedAnagraNdgClienti = await (from a in intranet2K3Entities.sharedAnagraNDGClienti
                                             select new SharedAnagraNdgClienti
                                             {
                                                 Cm = a.CM,
                                                 CodFisc = a.CodFisc,
                                                 Cognome = a.Cognome,
                                                 Descrizione = a.Descrizione,
                                                 FilialePrevalente = a.Filiale_Prevalente,
                                                 Ndg = a.NDG,
                                                 Nome = a.Nome,
                                                 Telefono = a.Telefono,
                                                 Tipologia = a.Tipologia,
                                                 Trae = a.TRAE,
                                                 Tsae = a.TSAE
                                             }).AsNoTracking().ToListAsync();
    }

    return listaSharedAnagraNdgClienti;
}

Answer 1

I think this is not a problem of your query.This is an issue of EF 6+ framework itself.I personally didn't do any R&D about Asyn vc Sync.But I have found out some useful posts about that.Hope that will help to you.

When to really use async ?

In most applications using async will have no noticeable benefits and even could be detrimental. Use tests, profiling and common sense to measure the impact of async in your particular scenario before committing to it.

Here is that article : Async Query & Save (EF6 onwards)

And here is the Great research done by one of the Stackoveflow user.You can read that too.Great one.

Entity Framework async operation takes ten times as long to complete