Search method takes more time when load 7k household list

[mahmud6390]

Uploaded 7k household to FHIR server with basic info like name and id. After initial syncing done it's take more time to load at pagination or search by name. It take 2 minute to load all household at SDK.
Like a query below
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 = 'Group' AND a.resourceUuid IN ( SELECT resourceUuid FROM TokenIndexEntity WHERE resourceType = 'Group' AND index_name = 'active' AND index_value = 'true' ) AND a.resourceUuid IN ( SELECT resourceUuid FROM TokenIndexEntity WHERE resourceType = 'Group' AND index_name = 'type' AND (index_value = 'person' AND IFNULL(index_system,'') = 'http://hl7.org/fhir/group-type') ) AND a.resourceUuid IN ( SELECT resourceUuid FROM TokenIndexEntity WHERE resourceType = 'Group' AND index_name = 'code' AND (index_value = '35359004' AND IFNULL(index_system,'') = 'https://www.snomed.org') ) ORDER BY b.index_from DESC, c.index_from DESC LIMIT 10 OFFSET 30
when using ORDER BY it's take 50 sec per page or getting search result after search by name.

Device configuration: samsung galaxy tab A 8 enc(SM-T295), ram 2 GB, rom 16 GB.

Would you like to work on the issue?
Yes, we want to work in this issue .We'll need to add order by with household added datetimewise, household serial number,lastvisited timewise. so how can we implement this any tips for this

[jingtang10]

can you please add your search api call here as well?

[ellykits]

@mahmud6390 Is this based on the FHIRCore code? Refer to RegisterRepository#L91 to access the Search API code responsible for generating the query.

[mahmud6390]

@jingtang10 time consuming whenever we have added sort(DateClientParam("_lastUpdated"), Order.DESCENDING) at fhirEngine .search method
testing code as below to filter household

fhirEngine
     .search<Group> {
       if (nameQuery.isNotEmpty()) {
         filter(
           StringClientParam(SP_NAME),
           {
             modifier = StringFilterModifier.CONTAINS
             value = nameQuery
           }
         )
       }
       filterFamily(this)
       sort(DateClientParam("_lastUpdated"), Order.DESCENDING)
       //count = 100
       from = 0
     }
     .mapIndexed { index, fhirGroup -> fhirGroup.toFamilyItem(index + 1) }
     .let { families.addAll(it) }

[mahmud6390]

` private fun filterFamily(search: Search) {
search.filter(
TokenClientParam("type"),
init = arrayOf(
{
val coding = Coding("http://hl7.org/fhir/group-type", "person", "")
value = of(coding)
},
),
)

search.filter(
  TokenClientParam("code"),
  init = arrayOf(
    {
      val coding = Coding("https://www.snomed.org", "35359004", "")
      value = of(coding)
    },
  ),
)

}`

[ellykits]

@jingtang10 We also shared some feedback on some of the query performance issues on FHIRCore. Refer to the latest comments on this issue for more info opensrp/fhircore#2066

[omarismail94]

@mahmud6390 I deleted your comment as it contained sensitive info. Just so we don't loose what was said, the DB size was 21MB