Indexes are not used efficiently

[tzachi-dar]

xDrip is using ActiveAndroid to handle it's data (accessed via content provider). The API of ActiveAndroid does not allow xDrip to pass the index to the query. Adding that would make queries execute much faster.

We probably need to create our own version of ActiveAndroid and add that ability.

[tolot27]

@tzachi-dar Do you mean indices instead of keys? Please can you elaborate which keys/indices are used wrongly?

As far as I know it is the responsibility of the underlying database query planer/optimizer to use the appropriate indices. But this requires good index statistics. Maybe your database has crown and your index stats are outdated. In such case ANALYZE could help which should be set up to run automatically before closing a database connection.

[tzachi-dar]

I have changed the bug title to indexes. As far as I can tell, we never run the ANALYZE command, so maybe running it is a good thing. A grep in the activeandroid code shows that ANALYZE is never used there. I thought of passing the index as part of the query.

I have seen this on the libre blocks db, but I have seen many times that as DB gets bigger, xDrip works more slowly, which is a good sign of not using the correct index. I'll try to do more measurements if/when I'll have time.

[tolot27]

I thought of passing the index as part of the query.

That's not the correct way because an application usually does not know the index statistics.

I have seen this on the libre blocks db, but I have seen many times that as DB gets bigger, xDrip works more slowly, which is a good sign of not using the correct index. I'll try to do more measurements if/when I'll have time.

We should also revise the ORM layer and the queries we are using. Maybe we have not the correct indices created. We should also avoid overindexing. I've also noticed that xDrip gets slower over time and it is reported here #626 as well.

[tzachi-dar]

Using a new CRM might be a good idea, but is probably a big change in code, and in testing matrix. Right now, most of our queries are timed based (that is give me data from date a to date b). The sqllite3 does not understand our model because we never call ANALYZE. So, our options are either call analyze or pass the index. I think that the second is simpler, although I might look at the first one as well, and see if this is possible.

Practically speaking, do you think that you will have time to look at a new CRM?

[tolot27]

Using ANALYZE is the simplest thing to implement. I can also have a look at query/index optimization in general but need a big database for tests. Maybe, I can concatenate my backups.

Maybe, implementing a new Object–relational mapping (ORM) is not necessary at all. I'll have a look at the database schema.

[tolot27]

You should also think about storing the UUIDs of LibreBlock as a blob rather than a text. This is much more efficient in terms of space (much smaller field size and smaller clustered index) and query speed is about the same.

[tzachi-dar]

I must say, that I don't see how UUIDs are used at all in libreblock at all. I believe that they have been added to match what we have in all other tables. But this is probably another issue.

[tolot27]

Many things are commented at https://github.com/NightscoutFoundation/xDrip/pull/1743#discussion_r649013574. I still believe that the indices are used as they were defined. Improving this avoids looking for another ORM or change of ActiveAndroid. But we should enable ANALYZE on db connection close, anyway.

[tzachi-dar]

More info and a solution is at: https://github.com/NightscoutFoundation/xDrip/pull/2052

[Navid200]

What is the status of this?