Realm-java: Performance regression in query (nativeSize and nativeGetRow)

[Zhuinden]

Goal

What do you want to achieve?

Upgrade Realm to latest and retain performance

Expected Results

No performance drop

Actual Results

My query which looks like this became slow when updating from 1.2.0 -> 4.0.0-BETA3-SNAPSHOT.

        Date startDate = DateUtils.setToMidnight(date);
        Date endDate = DateUtils.setToEndOfDay(startDate);
        RealmResults<Schedule> schedules = query(realm)
                .beginGroup()
                .beginGroup()
                .lessThan(ScheduleFields.START_TIME, endDate)
                .greaterThanOrEqualTo(ScheduleFields.START_TIME, startDate)
                .endGroup()
                .or()
                .beginGroup()
                .greaterThanOrEqualTo(ScheduleFields.END_TIME, startDate)
                .lessThan(ScheduleFields.START_TIME, endDate)
                .endGroup()
                .endGroup()
                .lessThanOrEqualTo(ScheduleFields.START_TIME, date)
                .equalTo(ScheduleFields.CHANNEL_ID, channel.getId())
                .findAllSorted(ScheduleFields.START_TIME);
        if(!schedules.isEmpty()) {
            return schedules.get(schedules.size() - 1);
        } else {
            return null;
        }

With a schema that looks like this

public class Schedule
        extends RealmObject {
    @PrimaryKey
    private long id;

    private String title;

    @Index
    private String titleLowerCase;

    private long categoryId;

    private String category;

    @Index
    private Date startTime;

    @Index
    private Date endTime;

    private int duration;

    private String ageLimit;

    private long programId;

    private String episodeId;

    @Index
    private long channelId;

    private Channel channel;

    private RealmList<TVGroup> tvGroups;

The query was then reduced to

        Schedule schedule = query(realm)
                .lessThanOrEqualTo(ScheduleFields.START_TIME, date)
                .equalTo(ScheduleFields.CHANNEL_ID, channel.getId())
                .findAllSorted(ScheduleFields.START_TIME, Sort.DESCENDING)
                .first(null); // <-- added this
        if(schedule == null) {
            return null;
        }
        long scheduleStartTime = schedule.getStartTime().getTime();
        long scheduleEndTime = schedule.getEndTime().getTime();

        if((scheduleStartTime < endDate.getTime() && scheduleStartTime >= startDate.getTime())
            || (scheduleEndTime >= startDate.getTime() && scheduleStartTime < endDate.getTime()) ){
            return schedule;
        }
        return null;

But it is still slower in first() than in Realm 2.3.0 (and then slow in Realm 3.0.0)

Version of Realm and tooling

Realm version(s): 1.2.0 -> 4.0.0-BETA3-SNAPSHOT

Realm sync feature enabled: no

Android Studio version: 2.3.3

Which Android version and device: LG Nexus 5X, 8.0.0

[cmelchior]

Do you have any idea about how much slower it got? Also, do you have a sample Realm we perhaps can try out? (So it contain data).

[Zhuinden]

Well like, previously it was not lagging super-badly while scrolling, and now it does.

But I've been doing some method tracing magic and some query optimization

    @Override
    public Schedule getScheduleForChannelWithLastStartTimeBeforeDate(Realm realm, Channel channel, Date date) {
        Date startDate = MiDateUtils.setToMidnight(date);
        Date endDate = MiDateUtils.setToEndOfDay(startDate);
        RealmResults<Schedule> schedules = query(realm)
                .lessThanOrEqualTo(ScheduleFields.START_TIME, date)
                .equalTo(ScheduleFields.CHANNEL_ID, channel.getId())
                .findAllSorted(ScheduleFields.START_TIME);
        if(!schedules.isEmpty()) {
            Schedule schedule = schedules.get(schedules.size() - 1);
            long scheduleStartTime = schedule.getStartTime().getTime();
            long scheduleEndTime = schedule.getEndTime().getTime();
//                .beginGroup()
//                .lessThan(Schedule.Fields.START_TIME.getField(), endDate)
//                .greaterThanOrEqualTo(Schedule.Fields.START_TIME.getField(), startDate)
//                .endGroup()
//                .or()
//                .beginGroup()
//                .greaterThanOrEqualTo(Schedule.Fields.END_TIME.getField(), startDate)
//                .lessThan(Schedule.Fields.START_TIME.getField(), endDate)
//                .endGroup()
            if((scheduleStartTime < endDate.getTime() && scheduleStartTime >= startDate.getTime())
                    || (scheduleEndTime >= startDate.getTime() && scheduleStartTime < endDate.getTime()) ){
                return schedule;
            }
        }
        return null;
    }

Which is now faster, but apparently the real culprit is

io.realm.internal.Collection.nativeSize()
io.realm.internal.Collection.nativeGetRow()

because I'm getting the last element in the RealmResults.

Apparently this is what's slower now, but I'm not sure why yet. I'll get the Realm file out.

[beeender]

actually Collection.nativeSize() and Collection.nativeGetRow() should be faster compared to 1.2.0 ...

[Zhuinden]

Okay, so

1.a) this is a sorted result set ascending, where I'm getting the last element

1.b) phenomenon still happens with sorted result set descending where I'm getting the first element

2.) there are about 3000*7 so 21000 elements in the Realm.

3.) If I'm doing the search for date to be the last day shown in the application, then the performance drop is not visible. If I select a date before the last day, then the scroll becomes slow!

4.) in 1.2.0, this doesn't happen.

5.) After doing following optimization

        RealmResults<Schedule> schedules = query(realm)
                .lessThanOrEqualTo(ScheduleFields.START_TIME, date)
                .equalTo(ScheduleFields.CHANNEL_ID, channel.getId())
                .findAllSorted(ScheduleFields.START_TIME, Sort.DESCENDING); // <-- changed
        try { // avoid .isEmpty() call
            Schedule schedule = schedules.get(0); // <-- changed
            long scheduleStartTime = schedule.getStartTime().getTime();
            long scheduleEndTime = schedule.getEndTime().getTime();
            if((scheduleStartTime < endDate.getTime() && scheduleStartTime >= startDate.getTime())
                    || (scheduleEndTime >= startDate.getTime() && scheduleStartTime < endDate.getTime()) ){
                return schedule;
            }
        } catch(Exception e) {
            // NO-OP
        }
        return null;
    }

The difference in query speed depending on the day selected is still there.

Second day:

Last day:

---

I don't really have conclusive data on why this is the case. I've sent you the Realm file that contains data.

[Zhuinden]

The last iteration of this method seems to be faster than its predecessor.

Although I'm still a bit surprised that there was a performance drop here.

[beeender]

See https://secure.helpscout.net/conversation/438540978/11384/?folderId=366141 for the relevant realm file.

[Zhuinden]

I've actually realized I'm kind of an idiot because I don't need to catch all exceptions if I can just change the query like this:

        Schedule schedule = query(realm)
                .lessThanOrEqualTo(ScheduleFields.START_TIME, date)
                .equalTo(ScheduleFields.CHANNEL_ID, channel.getId())
                .findAllSorted(ScheduleFields.START_TIME, Sort.DESCENDING)
                .first(null); // <-- added this
        if(schedule == null) {
            return null;
        }
        long scheduleStartTime = schedule.getStartTime().getTime();
        long scheduleEndTime = schedule.getEndTime().getTime();

        if((scheduleStartTime < endDate.getTime() && scheduleStartTime >= startDate.getTime())
            || (scheduleEndTime >= startDate.getTime() && scheduleStartTime < endDate.getTime()) ){
            return schedule;
        }
        return null;

This uses nativeFirstRow() instead of nativeGetRow() and is much faster for some reason.

[Zhuinden]

But in the end, nativeSize() and nativeGetRow() are slower than before. 🙁

[Zhuinden]

I've actually just thought about one additional change I did apart from updating the version, which is that previously channelId wasn't indexed, but I added an index just as I was updating the version of Realm.

I'll verify when I can that this isn't the actual cause for the introduced slowness.

[Zhuinden]

I have removed the index that I added along with the version update, but the performance regression is not related, aka it did not affect the query speed.

nativeGet() and nativeSize() for get(0) in this particular query is still slow.

I have sent email with relevant APKs.

[Zhuinden]

@beeender @cmelchior I have verified that

the performance degradation is with the change from 2.3.2 => 3.0.0

So I guess the culprit is OsResults itself? What mode is a RealmResults built by the query, TableView?

[beeender]

Actually it might be the same problem with point 3 here https://github.com/realm/realm-java/issues/5387#issuecomment-335759194

[Zhuinden]

unfortunately I'm not well-versed in native debugging and stuff so all I know is that this overhead for scrolling when calling isEmpty() (size) and get() only start to happen after Realm 3.0.0

I manually tested 2.3.2 and 3.6.1 and all that but it was the 2.3.2 -> 3.0.0 change that did it

In the end, even the source code of results says that first()/last() avoids calling get and size twice so it'll help for my usecase

[cmelchior]

Awesome. Thanks for that info @Zhuinden 👍

[Zhuinden]

@cmelchior @beeender i have this weird feeling that each time get() is called, then if there is a sort applied to the results, then get() ends up calling update_tableview() which calls sync_if_needed() each time I get an item from the results

[Zhuinden]

I'll test this asap