Search query takes more time while loading service data

[mahmud6390]

Describe the Issue
After integrating latest SDK version 4 with The performance like as below:

with 5000 household, 20100 patient and 19900 services |   FHIR core |  SDK
login time | 120 - 140 s |  
household load time (from side menu) | 5-7 s | 2 s
patient load time (from side menu) | 5-7 s | 2 s
patient pagination | 3-4 s |  3s
patient search time (7 loaded page) | 8-15 s* |  7s
service load time (from side menu) | 60-65 s | 27 s

The search query for service load time for example we want to search ANC status patient
SELECT a.serializedResource FROM ResourceEntity a LEFT JOIN DateIndexEntity b ON a.resourceType = b.resourceType AND a.resourceUuid = b.resourceUuid AND b.index_name = '_lastUpdated' LEFT JOIN DateTimeIndexEntity c ON a.resourceType = c.resourceType AND a.resourceUuid = c.resourceUuid AND c.index_name = '_lastUpdated' WHERE a.resourceType = 'Patient' AND a.resourceUuid IN ( SELECT resourceUuid FROM TokenIndexEntity WHERE resourceType = 'Patient' AND index_name = 'active' AND index_value = 'true' ) AND a.resourceUuid IN ( SELECT resourceUuid FROM ResourceEntity a WHERE a.resourceId IN ( SELECT substr(a.index_value, 9) FROM ReferenceIndexEntity a WHERE a.resourceType = 'Condition' AND a.index_name = 'subject' AND a.resourceUuid IN ( SELECT resourceUuid FROM TokenIndexEntity WHERE resourceType = 'Condition' AND index_name = 'code' AND ( index_value = '77386006' AND IFNULL(index_system, '') = 'http:https://snomed.info/sct' ) ) AND a.resourceUuid IN ( SELECT resourceUuid FROM TokenIndexEntity WHERE resourceType = 'Condition' AND index_name = 'clinical-status' AND ( index_value = 'active' AND IFNULL(index_system, '') = 'http:https://terminology.hl7.org/CodeSystem/condition-clinical' ) ) ) ) ORDER BY b.index_from DESC, c.index_from DESC LIMIT 10 OFFSET 0

Would you like to work on the issue?

[mahmud6390]

As the DB size seems 1GB can't attached in here. We will share the credential if further RnD will required.

[MJ1998]

Thanks @mahmud6390 for this issue.
Could you reformat the description. I think some lines have interchanged. It seems SDK takes 27s where FHIR Core is taking 60-65s. A little unclear on what's intended.

I have some questions:-
How crucial is this issue?
Which sdk version are you talking about: "com.google.android.fhir:engine:0.1.0-beta04"?
What do you mean by service data here ?
For the example posted, what is the kotlin dsl being used ?

[mahmud6390]

@MJ1998 First column FHIR core takes times from screen navigation time plus after query execution and UI update. And Second column SDK times means only SDK taking times after calling the query and return the result.
How crucial is this issue?
- This issue will affected after field execution(performance). Not needed immediately fix this
Which sdk version are you talking about: "com.google.android.fhir:engine:0.1.0-beta04"?
- yes, it's using "com.google.android.fhir:engine:0.1.0-beta04" version
What do you mean by service data here ?
- service data means patient filter based on ANC,PNC,Eligible couple service stage
For the example posted, what is the kotlin dsl being used ?
- Not clear the question. we are using navigation-fragment-ktx = "2.5.3", Android Gradle Plugin Version: 7.1.3,
Gradle Version: 7.4.2 it's the answer of this question

[MJ1998]

1. As I understand the FHIR Core time is the total time including navigation, query execution and UI update. Whereas SDK time is just the query execution.
2. Can you try "com.google.android.fhir:engine:0.1.0-beta05" which is the latest version ?
3. By kotlin dsl I meant what is the kotlin code in your application to get your service data. It looks similar to this.

[mahmud6390]

1. As I understand the FHIR Core time is the total time including navigation, query execution and UI update. Whereas SDK time is just the query execution.

   2. Can you try "com.google.android.fhir:engine:0.1.0-beta05" which is the latest version ?

   3. By kotlin dsl I meant what is the kotlin code in your application to get your service data. It looks similar to this.

1. Yes, SDK time meaning query execution time
2. Ok will try to update this version as we are using on FHIR core code base will need to merge from that code base also.
3. Now I understand, will share the kotlin code.

[asad-zaman]

@MJ1998 , Here is the Kotlin DSL, please take a look.

fhirEngine.search<Patient> {
  filter(TokenClientParam("active"), { value = of(true) })
  has(resourceType = ResourceType.Condition, referenceParam = Condition.SUBJECT) {
    filter(
      Condition.CODE,
      { value = of(Coding().apply { system = "http:https://snomed.info/sct"; code = "77386006" }) },
    )
    filter(
      Condition.CLINICAL_STATUS,
      { value = of(Coding().apply { system = "http:https://terminology.hl7.org/CodeSystem/condition-clinical"; code = "active" }) },
    )
  }
  sort(DateClientParam("_lastUpdated"), Order.DESCENDING)
  count = 10
  from = 0
}

We have checked above DSL in latest android-fhir with demo project and the query takes almost 34 seconds to complete.

As the database size is above 1.5G, The number of resources currently in the ResourceEntity table is listed below.

[aditya-07]

@asad-zaman @mahmud6390 Is there a way for you to provide the test data for us to debug the issue and changes on our side?

[asad-zaman]

Hello @aditya-07,
I have added the test data in Google Drive. Please have a look. Thanks

[mahmud6390]

@aditya-07 you can see the performance issue if you tested this in real physical device rather than emulator.

[aditya-07]

@mahmud6390 @asad-zaman I am able to use the provided data and replicate the service load time issue on my end.
I think I understand the reason for the long search execution time and should have a PR for the fix shortly.