Flutter - SQflite : Performance issues while bulk insertion

user_7114880 0
Viewed 91

Using the below approach for bulk insertions, getting performance issues in flutter app

Future<List<Object?>> bulkInsert(String tableName,List<Map<String,dynamic>> rowList) async{
final db = await instance;
int dbSaveResult = 0;
if(db!=null){
  await db.transaction((txn)async {
    var batch = txn.batch();
    for (var rowData in rowList) {
      try {
        batch.insert(tableName, rowData,
            conflictAlgorithm: ConflictAlgorithm.replace);
      }
      catch(exception){
        throw "some error while insertion";
      }
    }
    await batch.commit(continueOnError: false);
  });
}
return [];}

List<Map<String,dynamic>> : Map contains 4 pairs of keys/value and list length around number of 13032, so total execution time of bulkInsert() method is 3.274 seconds in flutter.

while the same approach with same data, we are using in native [transaction support using sqlite c library] for insert/update purpose then it takes around 210 milliseconds only.

any reason, why flutter based solutions taking time? or anything wrong with given code??

Please help me with best approach if I miss something.

sqflite flutter bulkinsert database-performance
1 Answers

There is nothing wrong with your code.

Possible optimization:

  • Use the noResult: true option during commit. It avoid an extra query after each insertion. You will likely get a 50% gain
  • You can try to use sqflite_common_ffi along with flitter3_sqlite_libs (instructions). In my experiment it is about twice faster
  • You can run the solution above without running the sqlite statements in a separate isolate (but that could hang the UI)

A quick benchmark I tried (13000 records, 4 fields, Pixel 4a) gives this result:

sqflite io
insert: 0:00:04.074015
insert (noResult): 0:00:02.133279

sqflite_ffi io
insert: 0:00:01.915051
insert (noResult): 0:00:01.319466

sqflite_ffi (no isolate) io
insert: 0:00:01.420478
insert (noResult): 0:00:01.058417

Flutter services (for sqflite) and cross isolation communication (for sqlite ffi) are likely the main bottleneck. You could try to use sqflite3 package (i.e. without sqflite package) directly for even better performance.

user_5639790
Related