Faster way to save multiple entities with Entityframework

6

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

 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 dbContexto.SaveChanges() or dbContext.SaveChangesAsync() method, it takes more than 10 minutes to finish, causing timeout .

I looked it up and found some options such as this answer from StackOverflow in English , I left saving from 400 in 400 records and created a new instance of dbContext , but it did not help in the performace, it still took more than 10 minutes.

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

    
asked by anonymous 09.08.2017 / 00:53

2 answers

2

SqlBulkCopy - too fast!

I recommend using, see 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 should be declared as virtual :

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

Reference: Lazy Loading

    
09.08.2017 / 01:39
7

There are 4 options, I would choose the 4:

1 - Using Bulk Insert There is an extensions to Entity that promises a good performance improvement link

2 - Do not use EntityFramework for this operation Could use Dapper or StoredProcedure - (Indicates 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 everyone, when the user 'request' the notification would make the verification if he has already read the notification. Implementation Thinking of a relational database would create 3 tables.

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

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

4 - Using HUb or SignalR Notifications As apparently you are wanting to create PushNotification would be more indicated you implement with SignalR or using the Azure service link

    
09.08.2017 / 02:02