Good morning, I have a slowness problem when performing a process that is performed 1 Insert and 2 Updates for each record. In other words, 2500 records, 7500 access will be carried out in the database. Here is my doubt of how I can improve this.
Follow the 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);
}
1st 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 go through Foreach: