UI stuck when selecting a lot of data with watch()

[vovaklh]

Hi!
I have this query which takes several second to run. But my UI lagging while query is running. Is there way to avoid it?
Here is connection to database.

LazyDatabase _openConnection() {
  // the LazyDatabase util lets us find the right location for the file async.
  return LazyDatabase(() async {
    // put the database file, called db.sqlite here, into the documents folder
    // for your app.
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'db.sqlite'));

    // Also work around limitations on old Android versions
    if (Platform.isAndroid) {
      await applyWorkaroundToOpenSqlite3OnOldAndroidVersions();
    }

    // Make sqlite3 pick a more suitable location for temporary files - the
    // one from the system may be inaccessible due to sandboxing.
    final cachebase = (await getTemporaryDirectory()).path;
    // We can't access /tmp on Android, which sqlite3 would try by default.
    // Explicitly tell it about the correct temporary directory.
    sqlite3.tempDirectory = cachebase;

    return NativeDatabase.createInBackground(
      file,
      readPool: 2,
      setup: (rawDb) {
        rawDb.execute('PRAGMA journal_mode=WAL;');
      },
    );
  });
}

And here is query. Even I just listen for Stream without data processing - it is still lagging. Also I tried to hide rows mapping and it didn't help.

Stream<List<PredictionData>> allPredictions() {
    final query = _buildPredictionsQuery();
    final stopwatch = Stopwatch()..start();

    return query.watch().map((results) {
      stopwatch.stop();
      logDebug("Time elapsed: ${stopwatch.elapsedMilliseconds} ms");
      logDebug("Length: ${results.length}");
      final predictionList = _getPredictionsFromResult(results);

      return predictionList;
    });
  }
  
  JoinedSelectStatement _buildPredictionsQuery() {
    return select(predictions).join([
      leftOuterJoin(predictionCategories, predictionCategories.predictionId.equalsExp(predictions.id)),
      leftOuterJoin(categories, categories.id.equalsExp(predictionCategories.categoryId)),
      leftOuterJoin(probabilities, probabilities.predictionId.equalsExp(predictions.id)),
      leftOuterJoin(probabilitiesMistakes, probabilitiesMistakes.probabilityId.equalsExp(probabilities.id)),
    ])
      ..orderBy([
        OrderingTerm(expression: predictions.date),
        OrderingTerm(expression: predictions.createdAt, mode: OrderingMode.desc),
        OrderingTerm(expression: probabilities.createdAt, mode: OrderingMode.desc),
      ]);
  }

[simolus3]

Do you know how many rows are returned by the query? The setup looks correct to me, but if it returns a large number of rows (say more than a couple thousand), or these rows have lots of columns, it's possible that there is a slowdown. We'll run the queries on background isolates, but results are interpreted on the main isolate.
If too many rows are the problem, a potential solution is pagination by applying a limit on the query.

By the way, the stopwatch is not to helpful here because it will also measure time spent on the background isolate (which should not cause UI lag). Using a profiler on the main isolate to measure the time spent in drift is a better indicator of where this issue is coming from.

[vovaklh]

Do you know how many rows are returned by the query? The setup looks correct to me, but if it returns a large number of rows (say more than a couple thousand), or these rows have lots of columns, it's possible that there is a slowdown. We'll run the queries on background isolates, but results are interpreted on the main isolate.

If too many rows are the problem, a potential solution is pagination by applying a limit on the query.

By the way, the stopwatch is not to helpful here because it will also measure time spent on the background isolate (which should not cause UI lag). Using a profiler on the main isolate to measure the time spent in drift is a better indicator of where this issue is coming from.

Hi!
Yes, I decided to test the database by adding 10,000 entities to the table. My query includes 4 join subqueries, and it takes 15 seconds to execute. During this time, the UI becomes unresponsive. I will try using a profiler to analyze what’s happening. Thank you!

[vovaklh]

@simolus3
Hi!
If I want to use watch() but the table contains a lot of rows, is it possible to fetch the data in batches instead of retrieving all the data at once?

[simolus3]

Yes, you can apply a limit() on the statement to only get a few rows at once. You can then run multiple variants of the query depending on which rows are currently visible to only fetch those.

Just running the query in batches wouldn't reduce the actual load since all rows would still be reloaded when any of them changes.

[vovaklh]

@simolus3
But for statistics, I want to fetch all rows at once and also observe changes to the entire table. Is there a way to achieve this without causing the UI to freeze?
And even I create database in separate isolate mannualy it still does result interpretting on main isolate?