Faster way to save multiple entities with Entityframework

Question

I have a performance problem in one method, in addition to causing the full use of the CPU server that method takes a long time to run.

 internal async void NotificacaoIosTodos(string titulo, int posicao, int unidadeId)
    {
        try
        {
            _contexto.Configuration.AutoDetectChangesEnabled = false;
            _contexto.Configuration.ValidateOnSaveEnabled = false;

            var unidade =  _contexto.Unidade.FirstOrDefault(l => l.UnidadeId == unidadeId);
           var pessoas =  _contexto.PessoaUnidade.Include(l => l.Pessoa)
                .Where(l => l.Pessoa.Dispositivo == (int)enumDispositivo.Ios && l.UnidadeId == unidadeId)
                .ToList();
            var quantidadeAdicionada = 0;

            Notificacao notificacao = new Notificacao { Posicao = posicao };
            _contexto.Notificacao.Add(notificacao);

            foreach (PessoaUnidade pessoa in pessoas)
            {

                PessoaNotificacao pessoaNotificacao = new PessoaNotificacao
                {
                    Visualizado = false,
                    PessoaUnidade = pessoa,
                    Notificacao = notificacao
                };
                _contexto.PessoaNotificacao.Add(pessoaNotificacao);



            }


        await _contexto.SaveChangesAsync();

        }
        catch (Exception e)
        {
             throw;
        }

    }

The problem is that I have to insert approximately 1700 records at once, when I run the method dbContexto.SaveChanges() or dbContext.SaveChangesAsync(), takes more than 10 minutes to finish, causing timeout.

I searched the subject and found some options as for example this response from Stackoverflow in English, I left saving 400 in 400 records and created a new instance of dbContext , but it did not help the performace, it still took more than 10 minutes.

I would like to know how to make this method run faster, I have no idea where to go, would be happy with any hint or explanations.

Answer 1

There are 4 options, I would choose the 4:

1 - Using Bulk Insert There is an Extensions for Entity that promises a good performance improvement http://entityframework-extensions.net/overview

2 - Do not use Entityframework for this operation You could use Dapper or Storedprocedure - (I refer to Dapper)

3 - Change the implementation Instead of adding notification to all users you could add a notification with a FLAG indicating that it is for all, when the user 'request' the notification would check if he already read the notification. Implementation Thinking of relational database would be created 3 tables.

Notifications to store information of notifications and users who must receive the notification would put notification types, such as: ALL (notify all users), GROUP(specific group defined in business rules), USER (a single user).

Notified users - store which notifications were delivered. When the user receives the notification it would generate a record in this table stating that the notification X was delivered to the user Y. Thus it would be able to know what notification a particular user should receive through a query in all notifications that are in groups that the user participates and that do not exist in the table of notified users.

4 - Using Notifications Hub or Signalr As it seems you are wanting to create Pushnotification it would be more appropriate if you implement with Signalr or using the Azure service https://azure.microsoft.com/en-us/services/notification-hubs/

Answer 2

Sqlbulkcopy - very fast!

I recommend using, see here an example: Sql Bulk Insert for Entity Framework 6

Resource reference: Sqlbulkcopy Class

To improve overall performance

Lazy Load

_contexto.Configuration.ProxyCreationEnabled = true;
_contexto.Configuration.LazyLoadingEnabled = true;

Properties that refer to another object must be declared as virtual:

public partial class Pessoa
{
   public virtual ICollection<Contato> Contatos { get; set; }
}

Reference: Lazy Loading