Performance question?

[ebelevics]

I'll make it short. I had one project which I migrated from sqflite to drift, while it did work I noticed that there were slight performance drops (around 0.1s). It might have been because my queries were not optimized... don't know.

Anyways I'll soon will have serious project and last two weeks am trying to find best DB solution. And one the main clients requests was performance. So I have analyzed multiple DB solutions: sqflite, sqlite3, drift, sqflite_common_ffi, realm, objectbox and isar.

While NoSQL solutions were more performant in speed, I have stumbled with some quirks, that was a no-go. In general I though SQL itself are significantly slower than NoSQL, until I tried sqlite3. To be honest I was surprised - it was on par in speed with NoSQL solution, where even querying 50000 table record it took 0.17 seconds, which was similar to NoSQL solutions.

Then I tried sqflite, sqlite3, drift and sqflite_common_ffi and I couldn't wrap my head around why for example I get these duration results:
On Empty table select all statement:
sqlite3 - 0:00:00.005121
sqflite_common_ffi - 0:00:00.380625
drift - 0:00:00.260545
sqflite - file is deleted, but I can return it for result inspection

Of course sqlite3 as I noticed method are not async compared to other solutions. So does it mean that isolates are the main cause of this performance drop? I did tried to create my own isolate in sqlite3 and got "0:00:00.119083" duration instead for same statement. It is useful of course to not block UI if DB operation is too heavy. Or maybe I'm missing something?

I you would like, I can create result with 50000 table records and show comparison, but at this moment I'll try to create sync and async (isolate) wrappers around sqlite3. Sync for light queries and statement, async for heavier queries. Similar how it's done in ObjectBox or Isar. But it's just a bummer that I have to then create a whole new API for this, while drift or sqflite has already fantastic years spent Sql wrapper API.

Tested devices were: Google Pixel 6a, LG G7 and iPhone SE, all had similar results.
Flutter 3.10.0

[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.

[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.

[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.