query use orderDesc is very slower than order

[ffyliu]

android 8.0 huawei P9 objectVersion：2.4.0

I inserted 12w pieces of data into Message table

@Entity
public class Message implements Serializable {

   @Id
    public long id;

    @Index
    private long serverId; 

    @Index
    private String sid;
    .......

}

the query method：

private void queryTest() {
       queryTest(1);
        queryTest(10);
        queryTest(50);
        queryTest(100);
        queryTest(1000);
        queryTest(3000);
        queryTest(6000);
        queryTest(10000);
}

private void queryTest(int queryNum) {
        long time = SystemClock.uptimeMillis();
        List<Message> messageList = Message.getMessageListByServerID2("k-007-liushengyun", Long.MIN_VALUE, queryNum);
        Log.d(TAG, "queryTest queryNum =" + queryNum + ",size=" + messageList.size() + ",orderTime=" + (SystemClock.uptimeMillis() - time));
        time = SystemClock.uptimeMillis();
        messageList = Message.getMessageListByServerID3("k-007-liushengyun", Long.MIN_VALUE, queryNum);
        Log.d(TAG, "queryTest queryNum =" + queryNum + ",size=" + messageList.size() + ",orderDescTime=" + (SystemClock.uptimeMillis() - time));
        messageList.clear();
    }

public static List<Message> getMessageListByServerID2(String sid, long minServerID, int count) {
        try {
            Query<Message> query = StoreManager.getBoxStore().boxFor(Message.class).query()
                    .equal(Message_.sid, sid)
                    .greater(Message_.serverId, minServerID).or().equal(Message_.serverId, minServerID)
                    .order(Message_.serverId).build();
            return count > 0 ? query.find(0, count) : query.find();
        } catch (Exception e) {
            SogouPlus.onException(e);
            return new LinkedList<>();
        }
    }

public static List<Message> getMessageListByServerID3(String sid, long minServerID, int count) {
        try {
            Query<Message> query = StoreManager.getBoxStore().boxFor(Message.class).query()
                    .equal(Message_.sid, sid)
                    .greater(Message_.serverId, minServerID).or().equal(Message_.serverId, minServerID)
                    .orderDesc(Message_.serverId).build();
            return count > 0 ? query.find(0, count) : query.find();
        } catch (Exception e) {
            SogouPlus.onException(e);
            return new LinkedList<>();
        }
    }

the query result:(ms)

queryTest queryNum =1,size=1,orderTime=71
queryTest queryNum =1,size=1,orderDescTime=301
queryTest queryNum =10,size=10,orderTime=72
queryTest queryNum =10,size=10,orderDescTime=581
queryTest queryNum =50,size=50,orderTime=73
queryTest queryNum =50,size=50,orderDescTime=908
queryTest queryNum =100,size=100,orderTime=75
queryTest queryNum =100,size=100,orderDescTime=1079
queryTest queryNum =1000,size=1000,orderTime=114
queryTest queryNum =1000,size=1000,orderDescTime=1952
queryTest queryNum =3000,size=3000,orderTime=175
queryTest queryNum =3000,size=3000,orderDescTime=2504
queryTest queryNum =6000,size=6000,orderTime=293
queryTest queryNum =6000,size=6000,orderDescTime=2846
queryTest queryNum =10000,size=10000,orderTime=483
queryTest queryNum =10000,size=10000,orderDescTime=3123

[greenrobot]

Those are... surprising results, so I extended our internal performance tests for descending order: Query with order and limit: 54 ms 107983 ns (x86-64) Query with descending order and limit: 60 ms 282501 ns (x86-64) The results are consistently slower, but only by 0-10%. Will investigate further... Update: could not reproduce this today, will update you next week.

[greenrobot]

The reason why descending order was slower in our setup was that data was inserted in perfect order already. Thus the ascending order didn't change anything, while the descending order had to reverse the "inserted order". Maybe the same applies to your data?

[ffyliu]

@greenrobot thanks for your reply,in my testcase,the data is not orderly by serverID,so Is there any way to improve the efficiency of this unordered query?

[greenrobot]

We're discussing options internally at the moment...

[ffyliu]

OK, thank you and look forward to a good result.

[greenrobot]

FYI, we've found a way to optimize this. It's working internally and we have to smooth it out before releasing it.

Edit (ut): tracked internally as 418, not done, yet.

[ffyliu]

@greenrobot GoodNews,look forward to the new version very much.

[onecoders]

FYI, we've found a way to optimize this. It's working internally and we have to smooth it out before releasing it.

Edit (ut): tracked internally as 418, not done, yet.

Hello.any solution to that?