Slow database performance in AndroidAPS, especially on history screen

[Koeppchen]

Using the history screen in the current dev Version of AAPS is more or less impossible, due to the large number of database statements executed and the database statements being slow. Also, the tdd statistics calculation is slow. This happens in the dev at least for some month, if not longer (For example dev-m) I have traced the database statements with adb shell setprop log.tag.SQLiteTime VERBOSE and then looked at all the statements taking longer then a milliseond with adb shell logcat | grep "SQLiteTime" | grep "androidaps" | egrep "took [1-9]" Looking at the database classes i found many indices on columns that have a low number of different values (for tips see https://www.sqlite.org/queryplanner-ng.html#howtofix), for example there are indices on the Table temporary_basals on the fields isValid, pumpType, pumpSerial where in most cases it will be much faster for the database to scan the whole database than using the index. To check if this is really true i have changed some of the Select statements with hints for the query optimizer. And the sql statements got much faster, the statistices screen with those hints query hints is still slow, but it is usable. I have tried to change the database itself, but at this point of time i am missing the knowledge how to apply the changes and also for many fields in the database i am not 100% sure about their content. Edt: Appended dbSelect.txt the diff of my changes to this Issue.

[MilosKozak]

great. history and statistics is querying database but still doing lot of calculations. thus result will never be fast. I'll try to implement suggested changes

[MilosKozak]

https://github.com/nightscout/AndroidAPS/commit/53fb825ae35d46f5ddd34c37c1ca115bf3251902

[MilosKozak]

it's really significant improvement

[Koeppchen]

I am sorry, this was not my intention. The thing is. there are indices in the Database, for example isValid, that have a very low number of different values. The database uses them to perform the query. Iit would be better to remove those indices instead of using likely/unlikely because without the indices the query is faster in most cases. Examples of "bad" indices are isValid, pumpType, pumpId, pumpSerial, maybe also others. Until those indices are removed likely is just a (performant) hack.

[Koeppchen]

And thank you for making it consistent. Better than my diff!

[MilosKozak]

yes indexes need cleanup but it will mean increase version number and all consequences. i'm keeping this open to remember it. I don't want to do it in current RC