Got UI stuck when writting data to SQL

[lucasjinreal]

I have using used

LazyDatabase _openConnection() {
  // the LazyDatabase util lets us find the right location for the file async.
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'chat2.sqlite'));
    if (!await file.exists()) {
      dbFolder.create();
    }
    // return NativeDatabase(file);
    return NativeDatabase.createInBackground(file, setup: (rawDb) {
      rawDb.execute('PRAGMA journal_mode=WAL;');
    });
  });
}

NativeDatabase.createInBackground created the database, but still, when inserting data to DB, the UI will stuck.

Is there any else reason could lead to this?

[simolus3]

Does this happen for any query or only for large batches/queries with lots of rows?

Building SQL and interpreting results still happens on the main isolate, and in some cases that can be costly. If you have more information about the kind of queries that you're running which might slow things down, I can suggest some mechanisms to move these onto other isolates as well.

[lucasjinreal]

Hello, I merely have a stream that listens to all data. Every time new data arrives, the stream would update and subsequently update the UI. As of now, when I insert a single piece of data, the UI becomes stuck. I am not certain if this occurs due to my listening to the stream. However, if I do not listen to it, the UI will not update when data is inserted.

[dickermoshe]

Does it jank for a frame or 2, or does the app freeze.

A stream which has no listeners doesn't issue events, try adding adding a listener to the stream manually and see what happends

stream.addListener((event){
  print(event);
});

A code sample and a screen recording are needed to further diagnose this

[lucasjinreal]

Hello, I believe it is caused by my listening to the stream all at once (without applying a limit), which implies that it queries all at once for each instance, approximately 1000 records every single time.

Do you think this will result in a significant amount of compute overhead? What is the most optimal approach to handle this? Is the application of a limit necessary?

[dickermoshe]

If you are performing multiple mutations rapidly, use a transaction so that the query only reruns once all the actions are done

[lucasjinreal]

@dickermoshe I just inserted a single record, and the listened stream got update, stuck UI. is a single record also compute densely

[dickermoshe]

Nah, we’re gonna need more code. Could you post the code for the database class? Also, can you profile the app and upload the CPU flame chart

On Tue, Aug 13, 2024 at 11:30 PM MagicSource @.***> wrote:

@dickermoshe https://github.com/dickermoshe I just inserted a single record, and the listened stream got update, stuck UI. is a single record also compute densely

— Reply to this email directly, view it on GitHub https://github.com/simolus3/drift/issues/3138#issuecomment-2287768439, or unsubscribe https://github.com/notifications/unsubscribe-auth/ASDJ6224O3NWGC4Z4PKBAUDZRLFMTAVCNFSM6AAAAABMJPFIW2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEOBXG43DQNBTHE . You are receiving this because you were mentioned.Message ID: @.***>