Issue with Database Operations in Android Application

[ukrrrish]

Hi Team,
We are encountering a challenge with database operations in our Android application.

In our application, we utilize the following pseudocode logic for performing database queries:
val queryString = "SELECT {Example__c:_soup} …….”
store.query(QuerySpec.buildSmartQuerySpec(queryString, 10_000), 0)


We have observed that when executing complex queries on one screen and subsequently navigating to another screen where a similar query for the same database table Example__c is triggered, there is a noticeable delay in executing the latest query.
Our suspicion is that the database table Example__c may be locked, causing the new operation to be executed only after the completion of the previous one.
In light of this, we are seeking your expertise to provide suggestions or guidance on how we can potentially address this issue. Specifically, we would like to explore the possibility of canceling the existing table query operation before initiating a new one.
We would greatly appreciate any insights or recommendations you can provide to help us resolve this matter efficiently.

Also we have observed that, in comparison to iOS, SyncDown operations in our Android application are taking longer to complete. Our implementation follows the architecture outlined in the SalesforceMobileSDK-Templates repository: https://github.com/forcedotcom/SalesforceMobileSDK-Templates/tree/dev/MobileSyncExplorerKotlinTemplate

Could you suggest if there is a more efficient approach to implement SyncDown operations in Android?

[wmathurin]

What version of the Mobile SDK are you using?

[nzsrini]

SDK v11.1.0
@wmathurin we got two challenges over there.

1. Local Query SmartSoup SOQL is in process of the fetching the data on one Screen (Screen1), the user switches to a different screen (Screen2) and the new screen(Screen2) also tries to access the same table ,we are observing the performance issue .we think either the issue is with Local Query waiting complete first Query in Screen1 . Is there any way we can cancel the Execution in Screen1?

2. Data download from Salesforce--- we observed for example IOS : take 50 seconds to download data and on Android it is taking around 1.40 - 2 mins.

Any suggestions or recommendations would be helpful.
**Same team. **
Thanks,

[wmathurin]

Until SQLCipher 4.5.5 , SQLCipher for Android did not support concurrent database access. We have recently upgraded to that version in dev (it will be in Mobile SDK 12.0) but we have not yet relaxed concurrency in the SmartStore code itself. We plan to get to it but it will probably not be part of MSDK 12.0. When we do that, it should address the kind of challenges you have observed.

[nzsrini]

Hi @wmathurin any update on concurrency relaxation SmartStore code please. Any suggestions we can implement as we have an application where we are struggling with performance.

[wmathurin]

No concurrency relaxation SmartStore work was done for 12.0 and there is no target release for that work yet.
One solution that our internal apps are using is setting up multiple stores. Of course that only works if you don't need to join across soups (i.e tables) that end up in different stores (i.e. databases).

[nzsrini]

@wmathurin
let us assume we can partition soups into multiple stores. How does that help in achieving concurrency. Any help or providing some sample or documentation or help would be greatly appreciated for implementation.
we are struggling with performance where it is taking time in downloading and querying locally.

[wmathurin]

Concurrency restrictions are preventing two threads from interacting with the same store at the same time, but there are no cross-stores restrictions. So you could read or write from one while reading or writing to another. Of course that only helps, if the concurrent access you require goes to different tables.
Here is the part of the documentation mentioning multiple stores and its uses: https://developer.salesforce.com/docs/platform/mobile-sdk/guide/offline-store-types.html

[Techfhil]

Proposed Solutions:

Concurrent Database Access: Given that SQLCipher for Android now supports concurrent access, I’m eager to know when we can expect the SmartStore code to support this.

Multiple Stores: If partitioning soups into multiple stores can enhance concurrency, could you provide guidance or examples on implementing this effectively?
Any insights or recommendations would be greatly appreciated. Thank you!