Slow read after write, as if database is stuck

[GP4cK]

I've followed the same setup as in the encrypted sample: https://github.com/simolus3/drift/blob/develop/examples/encryption/lib/database.dart

Here's the simplified flow of my app

1. Go to a screen showing a list of items
2. Add an item (write in transaction)
3. Get redirected to the edit page of that item (SELECT + watch)

When the database starts to get big (40k items ~ 200MB), loading the data on the edit screen is very slow.

On this timeline, the first why is the transaction that takes <200ms
Then the long stretch is the SELECT query that takes 3.2s
And the last tiny bit is the exact same SELECT query which only takes 45ms

Since the second SELECT query is much faster, I don't think it's because of the query itself.

Now if I profile the CPU, here is the flame chart

And the bottom up

This was running on a Galaxy S24 / Android 14.
I also tried using await customStatement('PRAGMA journal_mode = WAL'); but it doesn't seem to make much of a difference.
I'm a bit stuck here... How else could I troubleshoot this issue?

[simolus3]

I don't see an obvious cause for this right away. If you run the same statement before the write transaction, is it equally slow? Or is the transaction responsible for increasing the database size? I'm wondering if there may be an issue with slow reads and the second select is faster due to an in-memory cache, but it's still weird.

One thing worth noticing is that this uses a single database connection, so WAL wouldn't really make a difference. You can pass e.g. readPool: 4 to redirect reads to separate threads (in that case, you absolutely need WAL). But I think that just complicates this example, it's still surprising that the initial select is so slow and I wouldn't expect more threads to change that.

[GP4cK]

Thanks for taking the time.
I can't really run the same statement before the write transaction because it's selecting the item that the transaction creates.
However, I've tried this few things which leads me to think that the issue is not coming from the read statement:

1. Open an existing item from the list -> This will run the same read statement (just not with the same ID obviously) and that is still fast
2. I introduced a fake delay of 5 seconds just before the read statement and the read statement is also fast.

We have a couple of other places that .watch() the items list. So I'm wondering if that could be the cause... Tomorrow I'm going to try to replace all the .watch() with .get() and see if that changes anything.

[dickermoshe]

@simolus3 From personal experience it's really hard to track down watched queries which are affecting performance.
I wonder if the devtools could help for this.

[GP4cK]

I've done some more testing and the issue was definitely coming from the watch queries.
I'll check how to improve them. Thanks for your help!