Improve Process Performance C#

Question

Good morning, I have a problem of slowness when performing a process that is performed 1 Insert and 2 Updates for each record. That is 2500 records, will be held 7500 access in the database. Here is my question of how I can improve this.

Follows code.

Foreah calling Insert and Update methods.

foreach (var item in result)
{
    fc.UpdateStatusB2B(item.IdMassiveExchange,

    Convert.ToInt32(this.ddlPosicao.SelectedValue),
                    this.USUARIO.DscEmail,
                    this.USUARIO.NomUsuario,
                    this.txtJustificativa.Text);

    fc.GeraHistoricoTrocaPerfil(item.IdMassiveExchange);
}

1° Update

    public int UpdateStatusB2B(Guid IdBME, int Status, String Email, String Name, String ReasonB2B)
    {
        this.CreateCommand(@"Update b2b_massive_exchange set user_email_b2b = @User,
                                                             user_name_b2b  = @Name,
                                                             flg_status     = @Status,
                                                             reason_b2b     = @ReasonB2B
                             where id_bme = @IdBME");

        this.AddInParameter("User", System.Data.DbType.String, Email);
        this.AddInParameter("Name", System.Data.DbType.String, Name);
        this.AddInParameter("Status", System.Data.DbType.Int32, Status);
        this.AddInParameter("IdBME", System.Data.DbType.Guid, IdBME);
        this.AddInParameter("ReasonB2B", System.Data.DbType.String, ReasonB2B);

        return this.ExecuteNonQuery();
    }

2° Insert/Update

    public int GeraHistoricoTrocaPerfil(Guid IdBME)
    {
        int retorno = 0;

        try
        {
            this.CreateCommand(@"Insert into history_change_profile ( id_hcp,
                                                                  id_cn,
                                                                  date,
                                                                  profile_old,
                                                                  profile_new,
                                                                  description,
                                                                  flg_type )
                             Select newid(),
                                    c.id_cn,
                                    bme.date,
                                    bme.profile_old,
                                    bme.profile_new,
                                    bme.reason,
                                    0
                             from b2b_massive_exchange bme
                                  join rel_massive_b2b_employee rmbe on ( rmbe.id_bme = bme.id_bme )
                                  join b2b_employee be on ( be.id_em = rmbe.id_em )
                                  join consumer c on (     c.msisdn      = be.msisdn
                                                       and c.national_id = be.cpf )
                             where bme.id_bme = @IdBME");

            this.AddInParameter("IdBME", System.Data.DbType.Guid, IdBME);

            retorno += this.ExecuteNonQuery();

            this.CreateCommand(@"Update consumer set flg_change_profile = 1, 
                                                     id_pa_new = (Select bme1.id_pa_new
                                                                    from b2b_massive_exchange bme1
                                                                    where bme1.id_bme = @IdBME ) 
                             where exists(  Select top 1 c.id_cn
                                            from b2b_massive_exchange bme
                                                 join rel_massive_b2b_employee rmbe on ( rmbe.id_bme = bme.id_bme )
                                                 join b2b_employee be on ( be.id_em = rmbe.id_em )
                                                 join consumer c on (     c.msisdn      = be.msisdn
                                                                      and c.national_id = be.cpf )
                                            where     bme.id_bme     = @IdBME
                                                  and consumer.id_cn = c.id_cn )");

            this.AddInParameter("IdBME", System.Data.DbType.Guid, IdBME);

            retorno += this.ExecuteNonQuery();
        }
        catch(Exception ex)
        {
            log.Error(ex);
        }

        return retorno;
    }

Data that will run through Foreach:

Answer 1

Every new Voce Insert uses an Entity Framework expression:

this Refreshcontext();

This will renew your context, causing the Entity’s memory to not accumulate.