Need suggestions for improving performance using large data on Offline mode

Mobile
1

Hi everyone!

I am kind of stuck on improving performance on one of development:
Here is scenario:

  • Around 60 users on Android devices scans barcodes and need to receive (SQL query at this point) information about specific item matching this barcode.
  • SQL query is searching from table with ~500k rows.
  • it has to be in offline mode
  • at this moment I got query done in 0,85 sec on iPhone 14 Pro Max, but on the Android barcode scanners around 6 sec. Of course barcode scanners are not as powerful as iPhone, but maybe there is something I can do.
  • there is alternative (custom native old app) which makes every scan in just around 0,5-0,7 sec. It uses SQLite DB on the device. But as I learned, Retool doesn't support SQLite. I believe, why it is possible to get such good performance is because of indexing. Retool Offline mode saves data in JSON? Old app is depreciated and I chose to use Retool for creating new app for this task. Overall app works very good, but cant find solution for offline performance issues.

Just wondering, maybe there is some other solutions what I haven't thought about?
Thank You for all advices!

2

Is the barcode scan itself slow or is the slowness because of the SQL query that fires after the scan?

3

It is query after barcode scan (after scan, fires up query trigger to find item by barcode).
At this point i have tried also Retool Database, stored database there. Basically it jut give marginal improvement. Issue is not query itself, but just how devices can handle computing this query if that make sense. As far I know, if query happens in local stored SQLite database (because of available indexing), it would work very fast, but cant find the way to store locally database on device and use it in Retool.

4

Maybe there is some possibility to use Retool Storage to store SQLite database and connect to that?
Or maybe there is way to implement in Retool SQLite feature in general with option to store it offline on each device. I think that would be appreciated feature in Retool.