SQL Connect - SQLite client cache block the main isolate

[wer-mathurin]

[REQUIRED] Environment info

Firebase-cli 15.21.x
MacOS

[REQUIRED] Expected behavior

Do not block the Dart main isolate

[REQUIRED] Actual behavior

connector.yaml enabl the Data Connect SDK's persistent client cache (clientCache: maxAge: 5s). In firebase_data_connect 0.3.0, every query on a cache-enabled connector — even serverOnly — synchronously dehydrates the entire response and writes one SQLite transaction per entity on the main isolate before returning. With the 1000 payloads that meant thousands of main-thread SQLite writes (measured: 1.7–3.3 s hard freeze, growing with cache size). The cache served nothing: 5 s maxAge, all other reads serverOnly, no cacheOnly/subscribe usage.

[aashishpatil-g]

Thanks for bringing this up!

The code will need some refactoring -

• First, I'll work on moving most of the caching logic into its own isolate, which will stop main thread blocking.
• Regarding multiple writes - let me look into this after the first one is complete. Usually, sqlite locally is quite good at large number of small writes but if some change is required here, I'll investigate that later. I think just the first would solve your primary issue.

[aashishpatil-g]

In case you are interested in testing, there is a PR open for this - firebase/flutterfire#18430

[aashishpatil-g]

This fix is focussed on native platforms (excluding web)