Performance question?

[ebelevics]

Performance question?

[simolus3]

Thanks for starting the benchmark! Indeed, package:sqlite3 is a pretty direct wrapper around sqlite3 (the C library). It has the smallest overhead, but - since it's synchronous - it could block the main isolate for IO. This can cause frame drops in Flutter apps, which is why packages like drift and sqflite_common_ffi offload the work to another isolate. The sqflite wrapper does that by default, drift spawns an internal isolate if you use NativeDatabase.createInBackground.

The only way to implement this "running sqlite3 in a background thread" strategy is to use an internal RPC protocol built with send and receive ports across isolates. So if you call runSelect('SELECT * FROM empty_table'), drift will

1. Post a message like RunSelect('SELECT * FROM empty_table', []) over the send port to the internal database isolate.
2. That isolate needs to wake up and handle the message in a new iteration of its event loop.
3. It runs the actual query.
4. It posts the results like SelectResult([]) back to the main isolate.
5. The main isolate completes the future started in step 1 upon receiving the message.

In the synchronous variant, you're measuring step 3 only. All of the other steps are async overhead introduced by drift's (or sqflite_common_ffi's) background isolate mechanism.

The good news is that you're not actively blocking the main isolate - the operation takes more time, but since it's asynchronous other work can happen while we're waiting for results.

I did tried to create my own isolate in sqlite3 and got "0:00:00.119083" duration instead for same statement.

Is that implementation similar to the approach I've described for drift? Maybe I'm missing some performance optimizations as well, having a solid comparison would sure help to optimize the isolate implementation in drift.

Sync for light queries and statement, async for heavier queries

It's pretty hidden since it's hard to set up, but drift can sort of do this with a MultiExecutor - you could run selects synchronously and writes (which are much more likely to run IO) asynchronously.

[kuhnroyal]

Drift can use sqlite3, depending on your development targets you can use https://pub.dev/packages/sqlite3_flutter_libs

Here are the docs https://drift.simonbinder.eu/docs/platforms/ they also explain why certain things are faster.

[ebelevics]

Ok, I'll take a look if there will be difference in performance

[kuhnroyal]

My bad, actually drift always uses sqlite3 under the hood these days. So drift is the wrapper that you are trying to build.
sqflite_common_ffi also just wraps sqlite3.
sqlite3 should in theory be the fastest option because it binds directly to the native C API via dart:ffi.
drift will add a small overhead due to the async API and possible isolate serialization but you will have this with any wrapper.

[ebelevics]

Yeah, this is my observation on current situation. And most wrapper solutions offer async API, not both (sync or async) like ObjectBox or Isar does. Meaning developer has a choice, get sync instant data, but potentially could freeze UI, or async that doesnt freeze UI, but is slower cuz of isolates.
If the speed difference was insignificant that would not concern me, but 0.2s is a lot. Especially when I can reduce SELECT time with LIMIT or OFFSET, but I'll try to test sqlite3 with JOIN statements, maybe performance drop is too large on sync.

[ebelevics]

Oh interesting didn't even know there is MultiExecutor, will take a look.

This is the code I'm using to run Isolates, well it's straightforward and I think it does differ much to your implementation. Also you mentioned "2. That isolate needs to wake up and handle the message in a new iteration of its event loop." which is probably why screen freezes at my very first query on my implementation.

Same behaviour from drift also.
First query: "0:00:00.337465",
and all subsequent queries "0:00:00.003621".
I did remember this behavior after migrating in project from sqflite to drift, and I had no idea why was this a behaviour.
Maybe there are some methods to wake up isolate separately at app start?

Below is my Isolate code.

class DatabaseService {
  static const _databaseName = "test.db";
  static const _databaseVersion = 1;
  late final Database db;

  late final personDao = PersonDao(db);
  late final carDao = CarDao(db);

  Future<DatabaseService> init() async {
    final documentsDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentsDirectory.path, _databaseName);
    db = sqlite3.open(path);
    _onCreate(db);
    return this;
  }

  Future _onCreate(Database db, {int version = _databaseVersion}) async {
    db.execute(PersonTable.createTable);
    db.execute(CarTable.createTable);
  }

  Future _onUpgrade(Database db, int oldVersion, int newVersion) async {
    // TODO: onUpgrade
  }
}

  Future<List<CarLocal>> getCarsInIsolate() async {
    timer.reset();
    final receivePort = ReceivePort();
    await Isolate.spawn(_getCars, (RootIsolateToken.instance!, receivePort.sendPort));
    final cars = await receivePort.first as List<CarLocal>;
    print(cars.length);
    print(timer.elapsed);
    this.cars = cars.sublist(cars.length ~/ 2);
    setState(() {});
    return cars;
  }

  static void _getCars((RootIsolateToken, SendPort) args) async {
    BackgroundIsolateBinaryMessenger.ensureInitialized(args.$1);
    final SendPort sendPort = args.$2;

    await dbService.init();
    final cars = dbService.carDao.getAll();
    sendPort.send(cars);
  }

But yeah I'm intrigued to inspect MultiExecutor, because mostly I need sync queries for select statement, if they are light enough.