CODEWITHSUNDEEP
c#xamarin.formssqlite
Mario
Mario

Reputation: 103

Faster insert sqlite3 processing

I have this function where it opens a .TXT file with some products and insert line by line on the sqlitedb. The process is working fine, the problem is. This file contains 2000+ lines, because of that, the process is taking several hours to finish. I wonder if there is a way to make the process a little bit faster.

here is the function:

   private void carrega_produtos()
    {
        var assembly = typeof(sincroniza_page).GetTypeInfo().Assembly;
        foreach (var res in assembly.GetManifestResourceNames())
        {
            if (res.Contains("produtos.txt"))
            {
                Stream stream = assembly.GetManifestResourceStream(res);
                var st = res.Count();
                using (var reader = new StreamReader(stream))
                {
                    string linha;
                    acesso_banco_produtos banco = new acesso_banco_produtos();
                    while ((linha = reader.ReadLine()) != null)
                    {
                        List<string> lista = linha.Split(new char[] { '§' }).ToList();
                        var cod = int.Parse(lista.ElementAt(0));
                        var nome_prod = lista.ElementAt(1);
                        var cod_grupo = lista.ElementAt(2);
                        var nm_grupo = lista.ElementAt(3);
                        var ind_ativo = lista.ElementAt(4);
                        var val_custo_unit = lista.ElementAt(5);
                        var val_custo = lista.ElementAt(6);
                        var perc_imposto = lista.ElementAt(7);
                        var unidade_med = lista.ElementAt(8);
                        var qtd_mes_1 = lista.ElementAt(9);
                        var qtd_mes_2 = lista.ElementAt(10);
                        var qtd_mes_3 = lista.ElementAt(11);
                        var qtd_mes_6 = lista.ElementAt(12);
                        var qtd_mes_12 = lista.ElementAt(13);
                        var data = lista.ElementAt(14);

                        var bd = new banco_produtos()
                        {
                            cod_produto = cod,
                            nm_produto = nome_prod,
                            cod_grupo = cod_grupo,
                            nm_grupo = nm_grupo,
                            ind_ativo = ind_ativo,
                            val_custo_unitario = Double.Parse(val_custo_unit),
                            val_lista_preco = val_custo,
                            perc_impostos = perc_imposto,
                            unidade_medida = unidade_med,
                            qtde_vendida_mes_1 = qtd_mes_1,
                            qtde_vendida_mes_2 = qtd_mes_2,
                            qtde_vendida_mes_3 = qtd_mes_3,
                            qtde_vendida_mes_6 = qtd_mes_6,
                            qtde_vendida_mes_12 = qtd_mes_12

                        };
                         //here i look in the DB if already exists the new product
                        var procura = banco.get_produto(cod);

                        if (procura == null)
                        {
                           // here is inserted to the db
                            banco.inserir_produto(bd);
                        }
                    }
                    valor += 1;
                }
            }
        }
    }

Upvotes: 1

Views: 67

Answers (2)

Mario
Mario

Reputation: 103

I did what @Dmytro said, I used the method "insertORIgnore". It improved a lot using that method.thank you for the help.

Upvotes: 0

Dmytro Mukalov
Dmytro Mukalov

Reputation: 1994

I'm not sure what is inside your method which inserts data into db but the most common issue with SQLite and massive inserts is the fact that SQLite by default wraps every insert with transaction which creates significant overhead. A good practice for such cases is to make signle transaction for all insterts which should singificantly improve the perfomance, see the example.

Upvotes: 2

Related Questions