Denis Denis - 25 days ago 5
C# Question

WPF C# Access Insert Optimization

I have to insert about 25 thousand rows in MS Access and it´s taking too long (3 hours +-), is there any way to optimize it?

First I check if the register exists in the DB, if not I insert, else I update. Now I´m testing just insert (first time, table is empty). Before inserting I have to format some data.

conn = new OleDbConnection(Conexao.getConexao());
conn.Open();


foreach (Pendencia pendencia in listaPendeciaNassau)
{
if (!PendenciaNassauExisteVerificar(pendencia.PendenciaId))
{

if (!String.IsNullOrEmpty(pendencia.Contrato))
{
//Insert na tabela do BD
OleDbCommand cmd = new OleDbCommand("INSERT INTO tblImportacao( " +
" Contrato, " +
" Tipo, " +
" PendenciaNivel, " +
" PendenciaNassauId, " +
" PendenciaTipo, " +
" GarantiaDescricao, " +
" Observacao, " +
" AberturaData, " +
" VencimentoData, " +
" CarenciaInicio, " +
" CarenciaFim, " +
" DiasDecorridos, " +
" DiasPendentes, " +
" Produto, " +
" ClienteGrupo, " +
" ClienteNome, " +
" Rating, " +
" ClienteCnpj, " +
" Officer, " +
" CentroCusto, " +
" Moeda, " +
" OperacaoValor, " +
" LiquidacaoData, " +
" PendenciaIdComposto, " +
" Lastro, " +
" Corretora, " +
" AdAm, " +
" MnMe, " +
" PendenciaTipoMacro, " +
" Segmento, " +
" PendenciaOrigem, " +
" ImportacaoData, " +
" PorContrato" +
") " +
"VALUES (" + "'" + pendencia.Contrato + "'" +
"," + "'" + pendencia.Tipo + "'" +
"," + "'" + pendencia.PendenciaNivel + "'" +
"," + "'" + pendencia.PendenciaId + "'" +
"," + "'" + pendencia.PendenciaTipo + "'" +
"," + "'" + pendencia.GarantiaDescricao + "'" +
"," + "'" + pendencia.Observacao + "'" +
"," + (String.IsNullOrEmpty(pendencia.AberturaData.Trim()) ? "null" : "#" + pendencia.AberturaData + "#") +
"," + (String.IsNullOrEmpty(pendencia.VencimentoData.Trim()) ? "null" : "#" + pendencia.VencimentoData + "#") +
"," + (String.IsNullOrEmpty(pendencia.CarenciaInicioData.Trim()) ? "null" : "#" + pendencia.CarenciaInicioData + "#") +
"," + (String.IsNullOrEmpty(pendencia.CarenciaFimData.Trim()) ? "null" : "#" + pendencia.CarenciaFimData + "#") +
"," + "'" + pendencia.DiasDecorridos + "'" +
"," + "'" + pendencia.DiasPendentes + "'" +
"," + "'" + pendencia.Produto + "'" +
"," + "'" + pendencia.ClienteGrupo + "'" +
"," + "'" + pendencia.ClienteNome + "'" +
"," + "'" + pendencia.Rating + "'" +
"," + "'" + pendencia.ClienteCnpj + "'" +
"," + "'" + pendencia.Officer + "'" +
"," + "'" + pendencia.CentroCusto + "'" +
"," + "'" + pendencia.Moeda + "'" +
"," + "'" + (String.IsNullOrEmpty(pendencia.OperacaoValor) ? String.Empty : RetornarValorMonetarioFormatado(pendencia.OperacaoValor)) + "'" +
"," + (String.IsNullOrEmpty(pendencia.LiquidacaoData.Trim()) ? "null" : "#" + pendencia.LiquidacaoData + "#") +
"," + "'" + pendencia.PendenciaIdComposto + "'" +
"," + "'" + pendencia.Lastro + "'" +
"," + "'" + pendencia.Corretora + "'" +
"," + "'" + pendencia.AdAm + "'" +
"," + "'" + pendencia.MnMe + "'" +
"," + "'" + pendencia.PendenciaTipoMacro + "'" +
"," + "'" + pendencia.Segmento + "'" +
"," + "'PGPNassau'" +
",#" + DateTime.Now + "#" +
"," + "'" + pendencia.PorContratoDescricao + "'" +
");", conn);
cmd.ExecuteNonQuery();
}
}
}

private bool PendenciaNassauExisteVerificar(int pendenciaId)
{
bool existe = false;

OleDbConnection conn = null;


#region Select e conversão do DataSet
try
{
conn = new OleDbConnection(Conexao.getConexao());
conn.Open();

//Select da tabela tblPendencia
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * " +
"FROM tblImportacao I" +
" WHERE I.pendenciaNassauId = " + pendenciaId, conn);

DataSet ds = new DataSet();
da.Fill(ds, "tblPendencia");
DataTable dt = new DataTable();
dt = ds.Tables["tblPendencia"];


if (ds.Tables != null && ds.Tables[0].Rows.Count > 0)
{
existe = true;
}


}

catch (Exception ex)
{
EventLog.WriteEntry(APLICACAO, ex.Message, EventLogEntryType.Error, 234);
}

finally { conn.Close(); }

return existe;

#endregion
}

Answer

After sometime of thinking out the box, we decided that the best thing to do is to make a copy of the db in the local machine, work in it and then copy it back to the server. Now the whole process is taking about 15 minutes!

Sometimes just coding is not the solution.

Thanks for the everybodies effort!