Subquery performance is quite poor

[petmongrels]

How frequently does the bug occur?

Always

Description

SUBQUERY(observations, $observation, (concept.uuid = 'e19e68fd-97f1-4803-a1b2-bb850836ff54' and valueJSON contains '11111111') ).@count > 0
on Schema Individual.

This takes for ever, we waited for 5 minutes. We have 3.5 million observations, about 100k Individuals which is the parent of this Observation, and 1500 Concepts. valueJSON as less than 1 KB strings.

The data model.
{
name: "Individual",
primaryKey: "uuid",
properties: {
uuid: "string",
observations: {type: "list", objectType: "Observation"}
}
}

{
name: "Observation",
properties: {
concept: "Concept",
valueJSON: "string",
}
}

{
name: "Concept",
primaryKey: "uuid",
properties: {
uuid: "string",
},
}

observations.concept.uuid = 'e19e68fd-97f1-4803-a1b2-bb850836ff54' and observations.valueJSON contains '11111111'
on Schema Individual is quite fast but that not what we want.

We have run this in latest Realm Studio version - 13.0.2 and in android mobile emulators with realm js version as 11.0.0-rc.1

We have been using realm for 6 years on the same product and we are quite sure that in version 2.28.1 this quite fast. Also from release notes we didn't see any subquery related issues fixed since 11.0.0-rc1 and of course we see that it is slow in latest Realm Studio as well.

Stacktrace & log output

No response

Can you reproduce the bug?

Always

Reproduction Steps

No response

Version

v11.0.0-rc.1

What services are you using?

Local Database only

Are you using encryption?

No

Platform OS and version(s)

Android API version 9 and later

Build environment

Which debugger for React Native: ..

Cocoapods version

No response

[kneth]

@petmongrels Thank you for reporting. We need to investigate it order to understand what can cause the slower query performance.

[kneth]

@petmongrels If possible, we would appreciate a little example which can reproduce it.

[petmongrels]

By example, I am guessing you mean a realm database file with data in it. Give us some time to send you that.

[kneth]

If you can give us that - and a few problematic queries - we will appreciate it.

[1t5j0y]

@kneth we have created a (zipped) realm file to reproduce this.

Problematic queries:
On Individual schema
subjectType.uuid = '9f2af1f9-e150-4f8e-aad3-40bb7eb05aa3' AND SUBQUERY(observations, $observation, (concept.uuid = 'e19e68fd-97f1-4803-a1b2-bb850836ff54' and valueJSON contains '11111111') ).@count > 0
and
voided = false AND subjectType.uuid = '9f2af1f9-e150-4f8e-aad3-40bb7eb05aa3' AND SUBQUERY(enrolments, $enrolment, ( $enrolment.program.uuid = 'e93303fa-f465-4db7-a945-d4fd6295f2b9' ) AND ( $enrolment.encounters.encounterType.uuid = 'e99184ad-9082-43d0-9726-7644e37543dd' ) AND $enrolment.voided = false and $enrolment.programExitDateTime = null ).@count > 0
Please let us know if you need anything further.

[kneth]

@1t5j0y @petmongrels

I have downloaded the file, and we can take a closer look.

You mention it was fast in v2.28.1 but UUID was introduced in v10.5.0 so I imagine you had different queries earlier.

[petmongrels]

The uuids are string data types used as primary key.
You can find all our models in this repository: https://github.com/avniproject/avni-models

[kneth]

I have been using the following script:

const Realm = require("realm");

let realm = new Realm({ path: "anonymized.realm" });
console.log(new Date().toString());
let results = realm.objects("Individual").filtered("subjectType.uuid = '9f2af1f9-e150-4f8e-aad3-40bb7eb05aa3' AND SUBQUERY(observations, $observation, (concept.uuid = 'e19e68fd-97f1-4803-a1b2-bb850836ff54' and valueJSON contains '11111111') ).@count > 0");
console.log(`results.length = ${results.length}`);
console.log(new Date().toString());
realm.close();

It takes about 8 minutes on my computer (MacBook Pro M1) with v11.4.0, and it reports 0 objects in results. Unfortunately v2.28.1 doesn't support M1.

I have profiled the scripts, and getting backlinks give a heavy load. We probably need to profile some more to get a better understanding of the performance.

[kneth]

@petmongrels Unfortunately the Realm file you have created cannot be opened using older versions of Realm JS. If possible, can you create a file with v2.28.1 so we can see the difference in performance with different versions?

[1t5j0y]

@kneth file created using v2.28.1 available here. This has relatively fewer records but still in the same ballpark.

Ran the same nodeJS script you created on a 10th Gen Intel i7:
On v11.4 (~12 mins)

Fri Feb 10 2023 22:04:47 GMT+0530 (India Standard Time)
results.length = 0
Fri Feb 10 2023 22:16:26 GMT+0530 (India Standard Time)

On v2.8.1 (under a second)

Mon Feb 13 2023 11:44:14 GMT+0530 (India Standard Time)
results.length = 0
Mon Feb 13 2023 11:44:14 GMT+0530 (India Standard Time)

[kneth]

@1t5j0y @petmongrels

Thank you for the new file. After a number of runs, I believe the regression was introduced in v3.4.0. It includes #3432 and we need investigate if it is the root cause.

[jedelbo]

Yes, the "optimization" that @kneth points to has a really negative influence in this use case. We will have to investigate some more to figure out what the cure would be.

[kneth]

@1t5j0y @petmongrels

A fix has been merged, and we hope to release it soon. Please keep an eye of Realm JavaScript releases in the near future.