Insert sqlite flutter without freezing the interface

Question

I'm trying to insert a lot of rows (about 12k or more) in a sqlite memory database using flutter.

I get data from the API and use a compute function in order to process data from Json. Now I need to add these data to a database in memory, in order to do so I use a transaction with a batch.

batchInsertEventSong(List<EventSong> rows) async {
   Database db = await instance.database;
   db.transaction((txn) async {
      Batch batch = txn.batch();
      for (var song in rows) {
         Map<String, dynamic> row = {
           DatabaseHelper.columnIdSong: song.id,
           DatabaseHelper.columnTitle: song.title,
           DatabaseHelper.columnInterpreter: song.interpreter
         };
         batch.insert(table, row);
       }
      batch.commit();
   }
}

But this function is blocking my UI during insertions, I tried also with compute but I can't pass the class db or the class batch. I hadn't clear how to execute this process in another thread or (since I can't use isolates) executing without blocking my UI.

Any advice?

Weakminded

Sorry I forgot to say, like 12K more or less – Weakminded

The for is taking about 2 seconds, the commit about 4 seconds. With add to batch in smaller chunks what do you mean? executing batch.commit(); not at the end but after some cycles? – Weakminded

I tried adding a if(i % 1000 == 0) batch.commit(noResult: true); so it commit rows every 1000 items and at the end of the loop. But this keeps blocking my UI for now 12 seconds more or less... Is there in flutter a sleep or processEvents (like in Qt) to let the main thread a little air to execute? – Weakminded

Answer 1

Update 2020 - 05 - 15

this will not work see :

• Unable to call a platform channel method from another isolate · Issue #13937 · flutter/flutter

---

Note : full code available on the end

Step 1 : make your method static and make it void

  static batchInsertEventSong(List<EventSong> rows) {
    Database db = await instance.database;
    db.transaction((txn) async {
        Batch batch = txn.batch();
        for (var song in rows) {
            Map<String, dynamic> row = {
            DatabaseHelper.columnIdSong: song.id,
            DatabaseHelper.columnTitle: song.title,
            DatabaseHelper.columnInterpreter: song.interpreter
            };
            batch.insert(table, row);
        }
        batch.commit();
    }
  }

Step 2 : create new method (usually but not required add Async for the same name)

  Future batchInsertEventSongAsync(List<EventSong> rows) {

  }

Step 3 : call compute with your method static and return

  return compute(batchInsertEventSong, rows);

---

Step [1,2,3] code review

  Future batchInsertEventSongAsync(List<EventSong> rows) {
    return compute(_batchInsertEventSong, rows);
  }

  static _batchInsertEventSong(List<EventSong> rows) {
    Database db = await instance.database;
    db.transaction((txn) async {
        Batch batch = txn.batch();
        for (var song in rows) {
            Map<String, dynamic> row = {
            DatabaseHelper.columnIdSong: song.id,
            DatabaseHelper.columnTitle: song.title,
            DatabaseHelper.columnInterpreter: song.interpreter
            };
            batch.insert(table, row);
        }
        batch.commit();
    }
  }

---

Full Code

import 'dart:async';
import 'package:flutter/foundation.dart';
import 'package:flutter/material.dart';

void main() => runApp(MyApp());

class MyApp extends StatelessWidget {
  // This widget is the root of your application.
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter Demo',
      theme: ThemeData(
        primarySwatch: Colors.blue,
      ),
      home: Scaffold(body: MyHomePage(title: 'Flutter Demo Home Page')),
    );
  }
}

class MyHomePage extends StatefulWidget {
  MyHomePage({Key key, this.title}) : super(key: key);

  final String title;

  @override
  _MyHomePageState createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text(widget.title),
      ),
      body: Center(
        child: Column(
          mainAxisAlignment: MainAxisAlignment.center,
          children: <Widget>[
            FlatButton.icon(
              icon: Icon(Icons.backup),
              label: Text("Long Opreation"),
              onPressed: () async {
                var rows = await RsetApi.getRawsAsync();
                await Database._saveRaws(rows);
              },
            ),
            FlatButton.icon(
              icon: Icon(Icons.backup),
              label: Text("Short Opreation"),
              onPressed: () {
                Scaffold.of(context).hideCurrentSnackBar();
                Scaffold.of(context).showSnackBar(new SnackBar(
                  content: new Text(DateTime.now().toIso8601String()),
                ));
              },
            ),
          ],
        ),
      ),
    );
  }
}

class RsetApi {
  static Future<List<EventSong>> getRawsAsync() {
    return compute(_getRaws, null);
  }

  static List<EventSong> _getRaws(pram1) {
    var rows = List<EventSong>();
    for (var i = 1; i < 12000; i++) {
      rows.add(EventSong(i));
      print("fetching raws " + (i / 12000).toString());
    }
    return rows;
  }
}

class Database {
  static Future saveRawsAsync(List<EventSong> rows) {
    return compute(_saveRaws, rows);
  }

  static _saveRaws(List<EventSong> rows) {
    for (var i = 1; i < rows.length; i++) {
      print("saving raws " + (i / rows.length).toString());
    }
  }
}

class EventSong {
  int id;
  EventSong(this.id);
}

Ref:

• Parse JSON in the background - Flutter

non-important Ref:

• Flutter async : Beginner friendly guide for heavy lifting operations

• Dart/Flutter : Converting timestamp - Stack Overflow

• Display a snackbar - Flutter

• sdk/file.dart at 1c4ad14fa3a0a1d5ecea10e3b04befecee3ffbcb · dart-lang/sdk

• Search · openSync(){

• sdk/file_impl.dart at 1c4ad14fa3a0a1d5ecea10e3b04befecee3ffbcb · dart-lang/sdk

• Search · readAsLinesSync

Boatright

I thought that PlatformChannels (sqflite insert) don't work with isolates (compute). Does this example work? – Eno

@Eno do you have the a refrence for this fact (a url), so i can check it later 😊, it will help me alot 🤗 – Boatright

There are a couple issues on GH, this is the main one github.com/flutter/flutter/issues/13937 – Eno

@MohamedElrashid Thanks for the answer, I don't know why yours works, but I tried the same way and still get this error error: native function 'Window_sendPlatformMessage' (4 arguments) cannot be found. I think it is because compute still does not support flutter plugins – Aurelioaurelius

Can you post your full code with the batchInsertEventSong in compute call ? I can't get this example to work. – Camarena

@Camarena this will not work see : Unable to call a platform channel method from another isolate · Issue #13937 · flutter/flutter – Boatright

@jama-mohamed this will not work see : Unable to call a platform channel method from another isolate · Issue #13937 · flutter/flutter – Boatright

Answer 2

First you should run your code in the main thread, not using compute. Sqflite runs already in its own thread and isolate support is kind of complex (as you need to open the database in the isolate...)

Secondly you should await the commit (you should actually get a warning about that if you enable the lint unawaited_futures).

if your for a loop last 2 seconds, that will freeze the UI whatever you do so you should split in smaller batch (let say 100).

This should be a possible solution:

await db.transaction((txn) async {
  var batch = txn.batch();

  Future<void> commitBatch() async {
    if (batch.length > 0) {
      await batch.commit();
    }
  }

  // Commit existing and start a new batch
  Future<void> commitBatchAndRestart() async {
    await commitBatch();
    batch = txn.batch();
  }

  for (var song in rows) {
    var row = <String, Object?>{
      DatabaseHelper.columnIdSong: song.id,
      DatabaseHelper.columnTitle: song.title,
      DatabaseHelper.columnInterpreter: song.interpreter
    };
    batch.insert(table, row);
    // Commit every 100 items
    if (batch.length > 100) {
      await commitBatchAndRestart();
    }
  }
  // Commit the remaining items if needed
  await commitBatch();
});

Tracay