Subsequent queries/Select after performing a large number of watch() queries are only executed after all the watch() streams are started

[cal-g]

Hi,

I made a sample application where I have 2 tables Notes and SubNotes where the relationship is that a note can have one or many subnotes and created sample data around 9000 records in Notes and 2 records for each note in SubNote (18000 records).
After I create a stream to watch all Notes

select(notes).watch();

I click a button which then creates SubNote streams for each of all the 9000 notes

final query = select(subNotes);

///parentNoteId is the ID of the Note
query.where((tbl) => tbl.parentNoteId.equals(noteId));

return query.watch();

Immediately after this if I try to perform another query that is not cached it takes a few seconds to complete.
I think the SubNote watch stream queries are not all executed yet and only after that , the query that I made latest is executed.

I'm using Drift Isolate and in my main Application I switch between different features where I create many such watch streams and while switching I cancel the subscriptions so the queries are not cached.

Is there a way to make this quicker and not wait till all the watch queries are executed ?

Thank you.

In this clip, loading ToDos takes a few seconds to load after loading the subnotes.

Screen.Recording.2023-01-11.at.5.25.57.PM.mov

[simolus3]

Thanks for the report!

It looks like you're listening to the 9000 queries in parallel - why? Calling .watch() many times should not be a problem since drift does not do any work until the stream is listened to. However, if you're listening to 9000 query streams at the same time then yes, drift really needs to schedule these 9000 operations.

sqlite3 can only work one query at a time (it is synchronous), so drift will queue up pending queries and run them in order. So if you schedule many queries and then another one, it will have to wait for those initial streams to fetch their initial data first.

It's somewhat surprising that 9000 selects take a couple seconds to complete (if you can share the full repository for me to take a look at, I can profile this further), but queries being run in the order in which they were scheduled is intended. Do you have 9000 StreamBuilders active in your app? If you're using a ListView for instance, the invisible stream builders should not listen to the stream either. So I think the best course of action is to figure out why you are running that many queries simultaneously and perhaps optimize that :)