WPF C# Access Insert Optimization

Question

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
}

Is there an index on `PendenciaNassauId`? If not, by the time you're inserting row number 25000, the database has to check 24999 other rows if they happen to be this row. And when you inserted row 24999, it had to check 24998 rows... and so on. There are other things to optimize (like, don't use a complete `DataAdapter` if a simple `Command.ExecuteScalar` will suffice) but that would be the first thing to check. — Jeroen Mostert, Nov 08 '16 at 19:42
If you're always inserting into an empty table, you could also simply eliminate duplicates in your source data before you even start inserting, which would certainly speed things up even more. If your source data has no duplicates, but there might be conflicting rows in the destination, you can select all existing IDs from the destination, then remove all matching rows in source. Bottom line, minimize the things you do to actually insert. — Jeroen Mostert, Nov 08 '16 at 19:45
Doing that big string concatenation every time through is killing your performance as well. You'd be better off using a parameterized query in the loop. — Kevin, Nov 08 '16 at 20:00
~3 hours for 25,000 rows is an average rate of less than 3 rows per second, which is *much* slower than one would expect. Have a look at the approach in the answer [here](http://stackoverflow.com/a/19728132/2144390) for ideas on how to speed things up, specifically, (1) using `Prepare` and `Parameters`, and, (2) using a Transaction. Note that it was inserting rows at a rate of ~4,000 rows per second (with a local database file). — Gord Thompson, Nov 09 '16 at 01:09
use an ORM (e.g. EntityFramework, NHibernate, etc.) this will save you time and a lot of grey hairs ;-) — Mat, Nov 09 '16 at 07:14
One more time I got downvoted without why. It´s f... annoying.... — User, Nov 09 '16 at 19:52
@Denis - Someone may have downvoted your question because you have not responded to any of the comments made so far. You have asked for help, a few people have offered suggestions, and you have not shown any inclination to "help them help you". Some Stack Overflow users might find *that* "f... annoying". — Gord Thompson, Nov 09 '16 at 22:16
@GordThompson I didn´t respond cause I´m trying to implement what they suggested. As soon as I have tried all the options I will post a feedback. It seems that many people here just want reputation score and are not really interested in helping each other. — User, Nov 09 '16 at 22:35
@JeroenMostert I have an Id on PendenciaNassauId, the problem is that I user other sheets in which I don´t have index, so that I can´t use it. I´m not inserting always into an empty table. — User, Nov 10 '16 at 00:20
@Kevin I´m trying to implement parameterized query but I´m in troubles with null values, like in the case below. new OleDbParameter("@AberturaData", "#" + (String.IsNullOrEmpty(pendencia.AberturaData.Trim()) ? "null" : pendencia.AberturaData) + "#"), — User, Nov 10 '16 at 00:23
@GordThompson I couldn´t try to implement what you suggested yet. — User, Nov 10 '16 at 00:24
@Mat I´d like to use EntityFramework, but as far as I know I can´t use it with Microsoft Access. — User, Nov 10 '16 at 00:25
http://stackoverflow.com/questions/11432488/how-to-use-entity-framework-for-ms-access-database — Mat, Nov 10 '16 at 08:18
BTW nobody gets reputation by posting a comment. Believe it or not, people just wanted to help you. If you really like to be so offending, please don't wonder if you get downvotes or no help. Further more you don't provide a [mcve]. You just post your code and scream help. Also for a enterprise programmer it's very unbelievable why you stick with MS Access as a database (refer also to @bubi answer in the link I've posted). This is just an educated guess why you get downvoted. And downvoting costs reputation!!! — Mat, Nov 10 '16 at 08:26
Voting as usefull as I did in your above comments didn´t give you reputation points? It´s incridible as for many people everything don´t provide a minimal bla bla bla. I´ve already read the MCVE and for me what I´ve posted has the minimum requirements. Using MS Access or not is not up to me. Sometimes you have to work with what you have in your hands, unfortunaly. By the way, thanks for Entity + Access example, as soon as possibile I will implement it. — User, Nov 10 '16 at 12:13
@Mat seriously bro, is this [question](https://stackoverflow.com/questions/2657212/the-calling-thread-must-be-sta-because-many-ui-components-require-this-error) more complete than mine? — User, Nov 10 '16 at 13:15
@Denis 1. I'm not your bro! 2. I don't think you will get any help if you are just offending people instead of providing the asked information. Good luck by finding out by yourself [ask]. I'm off — Mat, Nov 10 '16 at 13:31
@Mat no worries, I´m not your bro... and I´m not offending anybody here. — User, Nov 10 '16 at 13:38
Feel free to downvote! I´ve created an account in stackoverflow pt and in less than one day I got 38 reputations points! There the people really try to help you and don´t go everywhere downvoting and complaining about your questions! Nothing better than speaking to not ignorant people! =) — User, Nov 11 '16 at 15:01

User · Accepted Answer · 2016-11-11T13:22:14.617

0

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 everybody effort!

edited Nov 11 '16 at 13:22

answered Nov 11 '16 at 13:11

User

87
10

WPF C# Access Insert Optimization

1 Answers1