Performance issues on Android?

[ced1check]

I'm using sqflite in my project, but it causes a 2 seconds UI freezing when initial DB load is performed on a table for about 4000 items. I got to understand sqflite uses a separate isolate, so I was expecting it not to freeze the UI.

On iOS, there is no perceived freezing, but DB load takes less than 100ms for the same dataset.

So I switch data load to use sqlite3 in hope it will be faster, but I get similar results and UI freezing which I expected given calls are synchroneous.

Moving that to a separate isolate works, but performance is no better.

Why is that Android offers such poor performances?

Is there specific settings iOS uses that could account for such difference?

Can I switch to another sqlite library (currently using sqlite3_flutter_libs) that will perform better on Android?

[simolus3]

On which Android device did you check this? It might be that there is a problem on both platforms, but we're just not realizing it because some Android devices have slower CPUs. Or at least I can't think of an OS-specific issue at the moment.

Moving that to a separate isolate works, but performance is no better.

Just to be clear, that did fix the UI freeze but it still takes 2s to insert the rows? Did you wrap these inserts in a single transaction or are you using (the implicit default) one transaction per write?

[ced1check]

I've tested on Pixel 6a, OnePlus 13 Pro, Z Flip 6. Tested also on BV 4900 which takes 5+ seconds to load the same data.

Yes, using a separate isolate prevents UI freezing, but insert still takes 2 seconds.

Inserts are performed on bulk of 500 rows in sqlite3:

db.execute("INSERT OR REPLACE INTO (fields) VALUES (row1) (row2) ... (row500);")

Whether inside a BEGIN TRANSACTION / COMMIT or not makes no differences.

In sqflite, I do the same inside a transaction like this db.transaction(txn) => txn.execute();

[ced1check]

Here are exact comparison results on 2 tables:

iOS iPhone 12, using sqflite no isolate, no perceived UI freezes: table1 - 2508 elements - 29ms table1 - 463 elements - 12ms table2 - 4378 elements - 668ms

Android Pixel 6a, using sqflite no isolate, noticable UI freezes: table1 - 2507 - 319ms table1 - 464 - 41ms table2 - 4378 - 1398ms

Android Pixel 6a, using sqlite3 in isolate, no freezing: table1 - 2508 - 37ms table1 - 463 - 15ms table2 - 4378 - 1270ms

Android BV4900, using sqlite3 in isolate: table1 - 2507 - 238ms table1 - 464 - 40ms table2 - 4378 - 5260ms

Inserts are queued one at a time (in one isolate when using sqlite3).

FWIW, table2 includes 8MB of json data (not sure about the size of the converted data).

I read Pixel 6a is 29% faster (raw CPU power) here: https://versus.com/en/apple-iphone-12-vs-google-pixel-6a

Stilll inserts are between 2 to 10 times slower?

[ced1check]

And on top of performance issues, using sqlite3 in isolate, I have an issue on 2 tables that quite often remain empty, though I check db.updatedRows == check.length after every INSERT.

By the way, how can I enable sqlite3 logging, couldn't find any info about that?

[simolus3]

how can I enable sqlite3 logging

This package doesn't provide a builtin logger, so you would have to log statements yourself.

db.execute("INSERT OR REPLACE INTO (fields) VALUES (row1) (row2) ... (row500);")

If it's possible at all to restructure this, I would expect the following to be faster:

db.execute('BEGIN');
final stmt = db.prepare('INSERT OR REPLACE INTO (fields) VALUES (?);');

for (final row in batch) {
  stmt.execute([row]);
}

db.execute('COMMIT');

Still worth hunting down the difference of course.

[simolus3]

One thing that is different is that we compile sqlite3 with SQLITE_TEMP_STORE=2 on Android but not on iOS.

Could you try getting getTemporaryDirectory() with path_provider and setting sqlite3.tempDirectory to that path? And then run db.execute('pragma temp_store = 1;') once before anything else? That should make it use the same caching behavior as on iOS.

[ced1check]

Thanks @simolus3 , while temporary directory didn't make much of a difference:

table1 - 2508 - 37ms -> 48ms table1 - 463 - 15ms -> 12ms table2 - 4378 - 1270ms -> 1155ms

Using prepared statement (with temp directory) did the trick, big time:

table1 - 2508 - 37ms -> 30ms table1 - 463 - 15ms -> 9ms table2 - 4378 - 1270ms -> 75ms

Ran it multiple times to be sure data is actually loaded LOL. Feeling wrong to be reduced that much.

Not sure using a separate isolate is even necessary ;)

Glad I asked the question here.

[simolus3]

Just for my own interest, could you try calling db.prepare with the original large statements without ever executing them? If there's the same timing difference there between Android and iOS, it looks like SQLite might be much faster on iOS when compiling statements.

[ced1check]

I'll see if I can try that as I just finished migrating the whole code.

What about timings comparing iOS/Android with the new code?

[simolus3]

I'll see if I can try that as I just finished migrating the whole code.

Yeah don't worry about it if it takes much effort.

What about timings comparing iOS/Android with the new code?

That would be interesting to see too, but given the small total runtime it's probably not easy to filter out results from noise here.

[ced1check]

FWIW, I was not able to use SQLite3 and sqflite on iOS, it became unreliable, missing data or even stuck.

That said even with sqflite, iOS remains faster, 40ms to store data using sqflite on iOS, while it takes 70ms using sqlite3 on Android, for the same data set. With an supposedly equivalent CPU.

So I tried to switch all to sqlite3, but loading times on Android are extremely bad (from 2 to 18 seconds!) and on iOS not so good from 300ms to 2 seconds.

The main differences are that I build the SELECT manually and transform the ResultSet into a List<Map<String, Objects?>> doing this:

      ResultSet rs = db!.select(sql, whereArgs ?? []);

      List<Map<String, Object?>> list = [];

      for(List<Object?> row in rs.rows) {
        int idx = 0;
        Map<String, Object?> object = {};
        for(Object? obj in row) {
          object[ rs.columnNames[idx++] ] = obj;
        }
        list.add(object);
      }

Is there a better way to do that?

EDIT: Changed to this for better results:

list = rs.map((row) => Map<String, Object?>.fromEntries(row.entries)).toList();

EDIT: Using sqlite3 only, I ran into out-of-memory conditions 3 times already, never before have I seen this.

[ced1check]

By the way, I did run the old code with db!.prepare(bulkSql), without execute, same timing above 1 second.

[simolus3]

EDIT: Changed to this for better results:

ResultSet is a subtype of List<Map<String, Object?>> already so you should be able to just use that directly without any transformation.

[ced1check]

EDIT: Changed to this for better results:

ResultSet is a subtype of List<Map<String, Object?>> already so you should be able to just use that directly without any transformation.

LOL, it works indeed.

Migrated fully to sqlite3, and complete load gained 35% on iOS, only 15% on Android BV4900 and no gain on Pixel 6a. Very odd.