Starting a blocking GC Explicit When Make Bulk Insert In Xamarin

Question

I´m developing my first app in Xamarin, and got one problem, I´m trying to make bulk insert to insert more faster and decrease time synchronization with my rest api.

So I tried so many ways to avoid it, but they didn´t work.

Using singleton connection, adding parameters when create connection and use using keyword to dispose the connection.

I have 9000 registers, and got and then paginated around 1000 registers for each time.

When it comes at the last pagination page, it stop and console gave a message like this.

11-19 11:52:17.453 I/art     (32524): Starting a blocking GC Explicit
11-19 11:52:17.463 I/art     (32524): Explicit concurrent mark sweep GC freed 97(4KB) AllocSpace objects, 0(0B) LOS objects, 27% free, 10MB/14MB, paused 380us total 15.150ms
11-19 11:52:17.463 D/Mono    (32524): GC_TAR_BRIDGE bridges 120 objects 126 opaque 9 colors 120 colors-bridged 120 colors-visible 120 xref 0 cache-hit 0 cache-semihit 0 cache-miss 0 setup 0.09ms tarjan 0.11ms scc-setup 0.16ms gather-xref 0.01ms xref-setup 0.00ms cleanup 0.04ms
11-19 11:52:17.463 D/Mono    (32524): GC_BRIDGE: Complete, was running for 18.02ms
11-19 11:52:17.463 D/Mono    (32524): GC_MAJOR: (LOS overflow) time 50.76ms, stw 60.27ms los size: 4096K in use: 1369K
11-19 11:52:17.463 D/Mono    (32524): GC_MAJOR_SWEEP: major size: 8208K in use: 6622K
11-19 11:52:17.693 D/Mono    (32524): GC_TAR_BRIDGE bridges 0 objects 0 opaque 0 colors 0 colors-bridged 0 colors-visible 120 xref 0 cache-hit 0 cache-semihit 0 cache-miss 0 setup 0.09ms tarjan 0.11ms scc-setup 0.16ms gather-xref 0.01ms xref-setup 0.00ms cleanup 0.00ms
11-19 11:52:17.693 D/Mono    (32524): GC_BRIDGE: Complete, was running for 0.10ms
11-19 11:52:17.693 D/Mono    (32524): GC_MINOR: (Nursery full) time 2.45ms, stw 3.05ms promoted 10K major size: 8240K in use: 6634K los size: 4096K in use: 1650K
11-19 11:52:17.933 D/Mono    (32524): GC_TAR_BRIDGE bridges 0 objects 0 opaque 0 colors 0 colors-bridged 0 colors-visible 120 xref 0 cache-hit 0 cache-semihit 0 cache-miss 0 setup 0.09ms tarjan 0.11ms scc-setup 0.16ms gather-xref 0.01ms xref-setup 0.00ms cleanup 0.00ms
11-19 11:52:17.933 D/Mono    (32524): GC_BRIDGE: Complete, was running for 0.28ms
11-19 11:52:17.933 D/Mono    (32524): GC_MINOR: (Nursery full) time 2.42ms, stw 3.03ms promoted 2K major size: 8240K in use: 6636K los size: 4096K in use: 1916K

Bulk insert method

   public void BulkInsert(JArray array, string tableName = "")
    {
        try
        {
            if (string.IsNullOrEmpty(tableName))
            {
                Type typeParameterType = typeof(T);
                tableName = typeParameterType.Name;
            }

            StringBuilder stringBuilder = new StringBuilder();

            array.ToList().ForEach(register =>
            {
                stringBuilder.AppendLine(DataBaseUtil.GenerateInsertStatement(register, tableName));
            });

            SQLiteCommand command = new SQLiteCommand(ConnectionDataBase.Connection);
            command.CommandText = stringBuilder.ToString();
            command.ExecuteNonQuery();

        }
        catch (Exception e)
        {
            LogUtil.WriteLog(e);
        }


public static string GenerateInsertStatement(JToken register, string tableName)
{
    var data = JsonConvert.DeserializeObject<Dictionary<string, string>>(register.ToString());
    string columns = string.Join(",", data.Keys.ToList());
    string values = string.Join(",", data.Values.Select(v => string.Format(@"'{0}'", v.Trim())));
    return string.Format("INSERT INTO {0} ({1}) VALUES ({2}); ", tableName, columns, values);
}

The method which works with so many records

private async Task GenerateOrdensServico()
{
    try
    {
        _logs.Add("ORDENS DE SERVIÇO");

        double increment = ((1 - Progresso) / 2);
        int records = await _ordemServicoRest.GetCount();
        int limit = _sistemaParametroRepository.GetTamanhoPaginaSincMobile();
        int pages = (records / limit);

        for (int i = 0; i <= pages; i++)
        {
            JArray ordensServico = await _ordemServicoRest.GetAllInJsonFormatPaginated(DataBaseUtil.GetPagination(i, limit));

            if (ordensServico == null)
            {
                _logs.Add("Não Contem O.S de Corte para importar!");
                await App.Current.MainPage.DisplayAlert("Atenção", "Não tem O.S para importar!", "OK");
                continue;
            }

            _ordemServicoRepository.BulkInsert(ordensServico);
            AlterProgress(Progresso += ((Progresso * 100) + (increment / pages)));
        }
    }
    catch (Exception e)
    {
        LogUtil.WriteLog(e);
    }
}

My http get method

protected async Task<Object> GetValue(string metodo = "", string parametros = "")
{
    try
    {
        _urlBase = new Uri(GetStringConnectionParameters(metodo) + parametros);

        using (HttpClient client = new HttpClient())
        using (HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Get, _urlBase))
        using (HttpResponseMessage response = await client.SendAsync(request))
        {
            Stream content = await response.Content.ReadAsStreamAsync();

            if (response.IsSuccessStatusCode)
            {
                return DeserializeJsonFromStream<Object>(content);
            }
        }
    }
    catch (Exception e)
    {
        LogUtil.WriteLog(e);
    }

    return null;
}

And the method who calls the list of entities that I need to insert in the database

    private async Task RegistrarDados()
    {
        try
        {
            _logs.Add("Realizando o Download: ");

            AlterProgress(12.5);
            await GenerateAtendimentoMotivosEncerramento();

            AlterProgress(15);
            await GenerateHidrometrosLocalInstalacao();

            AlterProgress(17.5);
            await GenerateHidrometrosProtecao();

            AlterProgress(20);
            await GenerateFuncionarios();

            AlterProgress(25);
            await GenerateGrupoFaturamento();

            AlterProgress(30);
            await GenerateLigacaoAguaSituacoes();

            AlterProgress(40);
            await GenerateLigacaoEsgotoSituacoes();

            AlterProgress(45);
            await GenerateServicosTipo();

            AlterProgress(50);
            await GenerateSistemParametros();

            AlterProgress(55);
            await GenerateOrdensServico();

            AlterProgress(55);
            await GenerateContas();

            int contador = _ordemServicoRepository.Count<OrdemServico>();

            AlterProgress(100);
            _logs.Add("Sincronização encerrada com sucesso!");
            await App.Current.MainPage.DisplayAlert("Atenção", "Foram importados " + contador + " Ordens de Serviços!", "OK");
            PodeSincronizar = true;
        }
        catch (Exception e)
        {
            LogUtil.WriteLog(e);
        }
    }

The methods GenerateSistemParametros(); GenerateOrdensServico(); GenerateContas(); needs to execute in this order because they have dependance.

Edit

I also tried first this method to save

public void BulkInsert(JArray array, string tableName = "")
{
    try
    {
        if (string.IsNullOrEmpty(tableName))
        {
            Type typeParameterType = typeof(T);
            tableName = typeParameterType.Name;
        }

        using (SQLiteConnection connection = new SQLiteConnection(DataBaseUtil.GetDataBasePath()))
        {
            connection.BeginTransaction();

            array.ToList().ForEach(register =>
            {
                string sql = DataBaseUtil.GenerateInsertStatement(register, tableName);
                System.Diagnostics.Debug.WriteLine(sql);
                var command = connection.CreateCommand(sql);
                command.ExecuteNonQuery();
            });

            connection.Commit();
            DataBaseUtil.CloseConnection(connection);
        }
    }
    catch (Exception e)
    {
        LogUtil.WriteLog(e);
    }
}

Based on message from console, your application is out of memory limit(OOM), android OS execute the GC for your application.I check your code, If you insert the record one by one, it will cause lots of in the IO of hard-disk, So I suggest your could try to use `Transaction` like this link.https://stackoverflow.com/a/8163179/10627299 — Leon, Nov 20 '19 at 06:29
@LeonLu-MSFT thanks for your reply, I also tried to use begin and commit methods but didn´t worked. — Diego Macario, Nov 20 '19 at 12:36
Did you try to reduce insert records, If you will have this issue? — Leon, Nov 21 '19 at 08:08
I'm not seeing anything remotely OOM in the console log. Are you sure the problem is with SQLite and not receiving the data? Try using Stopwatch and log the times before and after each external operation. — mostanes, Nov 21 '19 at 22:05
I receive the data yes, I printed the query in `console.log` — Diego Macario, Nov 21 '19 at 22:56
I's not clear what you mean by "it stops". Does the app crash? If so, you should see an exception or another error in the console. Does it just hang? If so, you should be able to pause execution and see where it gets stuck. It may also be worth trying on different emulators / real devices. — Zmaster, Nov 23 '19 at 17:40
@Zmaster i´m using real device, and the app didn´t stop, but the insert statement stops — Diego Macario, Nov 23 '19 at 22:52

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

An observation regarding the use of HttpClient

Reference You're using HttpClient wrong and it is destabilizing your software

private static Lazy<HttpClient> client = new Lazy<HttpClient>();

protected async Task<Object> GetValue(string metodo = "", string parametros = "") {
    try {
        _urlBase = new Uri(GetStringConnectionParameters(metodo) + parametros);                        
        using (HttpResponseMessage response = await client.Value.GetAsync( _urlBase)) {               
            if (response.IsSuccessStatusCode) {
                Stream content = await response.Content.ReadAsStreamAsync();
                return DeserializeJsonFromStream<Object>(content);
            }
        }
    } catch (Exception e) {
        LogUtil.WriteLog(e);
    }    
    return null;
}

@DiegoMacario are you making any blocking calls like `.Result` or `.Wait()` that can lead to deadlocks? — Nkosi, Nov 21 '19 at 15:53
I put your suggestion but didn´t work, still getting GC error — Diego Macario, Nov 21 '19 at 16:16

score 1 · Answer 2 · answered Nov 24 '19 at 10:30

1

I suggest to follow the indications in the section "Optimising SQL Queries" in the SQLite.NET Documentation.

Please read that document, it's well written. The key points it recommends:

Wrapping all the inserts in a transaction (as already suggested)
Using a parametrised query (this is always good practice, because it guarantees special characters will be escaped properly)
Using a single insert statement per query, instead of trying to stuff many of them in a single one.
Avoiding unnecessary memory allocations

answered Nov 24 '19 at 10:30

Zmaster

1,095
9
23

1 - `Yes, you are right` 2 - `Sqlite in Xamarin doesn´t offer suport to parameters, I didn´t found` 3 - `When I send the data to App, I remove null values` 4 - `I don´t know what you are talking, I use singleton in connection, in repository classes` – Diego Macario Nov 25 '19 at 12:06
Which SQLite package are you using exactly? – Zmaster Nov 25 '19 at 18:06
`sqlite-net-pcl` – Diego Macario Nov 26 '19 at 13:23
Ok, so that's a different library then I was expecting. Anyway, try do do a single insert per query. Also, you may try to enable debugging library code to better understand where execution gets stuck. In Visual Studio for Mac the option is in Preferences > Projects > Debugger > "Debug project code only; do not step into framework code", untick the box. – Zmaster Nov 26 '19 at 18:18
I looked at my querie, and the problem were there. – Diego Macario Nov 27 '19 at 16:45

Starting a blocking GC Explicit When Make Bulk Insert In Xamarin

2 Answers2

Reference You're using HttpClient wrong and it is destabilizing your software