After using computeWithDatabase method also UI got freezed when bulk insert runs

[msarkrish]

Hi @simolus3 ,
We are using an encrypted database. And we are inserting 50K data to the table. Whenever we are inserting the data to table using batchInsert, UI is getting freezed. Even we tried to use computeWithDatabase as mentioned in the document but still UI is getting freezed.

GetIt.I.registerSingletonWithDependencies<AppDatabase>(
      () => AppDatabase(),
    );


class AppDatabase extends _$AppDatabase {
  /// we tell the database where to store the data with this constructor
  AppDatabase(
      {QueryExecutor? executor})
      : super(executor ?? _openConnection());

  @override
  int get schemaVersion => 1;

Future<void> insertBulkData(AppDatabase database) async {
  // computeWithDatabase is an extension provided by package:drift/isolate.dart
  await database.[computeWithDatabase](https://drift.simonbinder.eu/api/isolate/computewithdriftisolate/computewithdatabase)(
    computation: (database) async {
      // Expensive computation that runs on its own isolate but talks to the
      // main database.
      final rows = await apiCallWithDataParse();
      await database.[batch](https://drift.simonbinder.eu/api/drift/databaseconnectionuser/batch)((batch) {
        batch.[insertAll](https://drift.simonbinder.eu/api/drift/batch/insertall)(database.someTable, rows);
      });
    },
    connect: (connection) {
      // This function is responsible for creating a second instance of your
      // database class with a short-lived [connection].
      // For this to work, your database class needs to have a constructor that
      // allows taking a connection as described above.
      return MyDatabase(connection);
    },
  );
}


}

QueryExecutor _openConnection() {
  // the LazyDatabase util lets us find the right location for the file async.
  return LazyDatabase(() async {
    final path = await getApplicationDocumentsDirectory();
    return NativeDatabase(
      File(p.join(path.path, 'app.db.enc')),
      logStatements: true,
      setup: (db) {
        // Check that we're actually running with SQLCipher by quering the
        // cipher_version pragma.
        final result = db.select('pragma cipher_version');
        if (result.isEmpty) {
          throw UnsupportedError(
            'This database needs to run with SQLCipher, but that library is '
            'not available!',
          );
        }

        // Encrypt database only in Android
        if (Platform.isAndroid) {
          // Then, apply the key to encrypt the database. Unfortunately, this
          // pragma doesn't seem to support prepared statements so we inline the
          // key.
          final escapedKey = password.replaceAll("'", "''");
          db.execute("pragma key = '$escapedKey'");
        }
      },
    );
  });
}

In our application we are creating database instance using GetIt. While calling this insertBulkData method, I'm passing that database instance to this method.

final appDatabase=GetIt.I.get<AppDatabase>();
await appDatabase.insertBulkData(appDatabase,);

If I hide that batchInsert and check then UI is not getting freezed. But If i run with batchInsert then UI is getting freezed.

Can you help me to fix this issue, what I'm missing here?

[simolus3]

There are two sources for UI freezes to be aware of when using drift: Creating the SQL statements from the Dart API and actually executing that SQL. For most queries, executing the SQL on a background isolate is enough. But for 50k inserts, just the cost of creating the SQL statements alone might cause short UI freezes.

computeWithDatabase only solves the problem of creating the SQL statement on a background isolate. NativeDatabase.createInBackground only solves the problem of executing SQL on a background isolate. In most workloads, this is much more important than computeWithDatabase.

I assume the UI freezes happen because you're not executing SQL on a background isolate. Could you try replacing the NativeDatabase with a NativeDatabase.createInBackground?
You can keep the computeWithDatabase setup, it's a good additional optimization for large batch inserts. But without createInBackground, the SQL queries would be created on a background isolate only to then be executed on the main isolate, blocking everything else.

While calling this insertBulkData method, I'm passing that database instance to this method.

Uh, couldn't the method just use this instead of the parameter?

[msarkrish]

Hi @simolus3,
Now we are creating a database using NativeDatabase.createInBackground . In our project, we have two encrypted databases. Is it possible to write a join query by combining the two databases?

We tried to attach database using below attach query. We are running the below query in our main encrypted database class file. It is not throwing any error.

await customStatement("ATTACH '/data/user/0/com.example.app/app_flutter/master.db.enc' as 'master'");

But it is not attaching the database. I have confirmed this by running the below statment

customSelect('PRAGMA database_list');

Do you know any other way to combine two encrypted database to write join query?

[simolus3]

Wouldn't you have to supply a key to ATTACH?

[msarkrish]

You are a genius, @simolus3. I don't know how I missed this.