Производительность Android SQLite в сложных запросах

Предположим, что у меня такой запрос

String sql = "SELECT s.team_id, s.team_name, s.gp, s.w, s.t, s.l, s.go, s.ga, s.score, s.p FROM "
           + "(SELECT team_id, team_name, SUM (gp) gp, SUM (w) w, SUM (t) t, SUM (l) l, SUM (GO) go, SUM (GA) ga, SUM (GO)- SUM (GA) score, SUM (2*w+t) p FROM "
           + "(SELECT t._id team_id, t.name team_name, COUNT(CASE WHEN score_home IS NOT NULL THEN 1 END) gp, COUNT (CASE WHEN score_home > score_away THEN 1 END) w,"
           + " COUNT (CASE WHEN score_home = score_away THEN 1 END) t, COUNT (CASE WHEN score_home < score_away THEN 1 END) l,"
           + " SUM (score_home) go, SUM (score_away) ga"
           + " FROM team_table t LEFT OUTER JOIN match_table m ON m.team_home = t._id"
           + " WHERE t.tournament_id = ? GROUP BY t._id, t.name"
           + " UNION ALL"
           + " SELECT t._id team_id, t.name team_name, COUNT(CASE WHEN score_away IS NOT NULL THEN 1 END) gp, COUNT (CASE WHEN score_home < score_away THEN 1 END) w,"
           + " COUNT (CASE WHEN score_home = score_away THEN 1 END) t, COUNT (CASE WHEN score_home > score_away THEN 1 END) l,"
           + " SUM (score_away) go, SUM (score_home) ga"
           + " FROM team_table t LEFT OUTER JOIN match_table m ON m.team_away = t._id"
           + " WHERE t.tournament_id = ? GROUP BY t._id, t.name)"
           + " GROUP BY team_id, team_name) s"
           + " ORDER BY s.p DESC, s.score DESC, s.go ASC";

который затем используется как этот

Cursor cursor = database.rawQuery(sql, args);

cursor.moveToFirst();
while (!cursor.isAfterLast()) {
    TeamStats stat = new TeamStats();

    stat.setTeamId(cursor.getLong(0));
    stat.setTeamName(cursor.getString(1));
    stat.setGamesPlayed(cursor.getInt(2));
    stat.setWins(cursor.getInt(3));
    stat.setTies(cursor.getInt(4));
    stat.setLoses(cursor.getInt(5));
    stat.setGoalsOwn(cursor.getInt(6));
    stat.setGoalsAgaist(cursor.getInt(7));
    stat.setScore(cursor.getInt(8));
    stat.setPoints(cursor.getInt(9));

    stats.add(stat);
    cursor.moveToNext();
}
cursor.close();

Таким образом, он выбирает значения из многих таблиц, выполняет некоторые операции и т.д. Поскольку вы можете видеть, что запрос ужасно сложный (очень сложный для отладки), и производительность не кажется такой же хорошей, как я ожидал. Мои вопросы:

Могу ли я улучшить performace, используя какой-то подготовленный оператор?
Было бы быстрее выполнять более простые запросы и обрабатывать их вручную с помощью какого-то пользовательского кода?

Ответы

Ответ 1

Если бы я был вами, я бы скопировал вашу базу данных sqlite на хост, а затем попытался выполнить ее вручную в каком-то графическом интерфейсе SQLite, заменив связанные переменные (?) фактическими значениями переменных, которые у вас есть. Для графического интерфейса пользователя в Windows мне очень нравится SQLite Expert Personal, а в Linux sqliteman довольно хорошо.

При отладке вашего SQL (в командной строке или графическом интерфейсе) обязательно проанализируйте свои SQL-запросы, запустив их под EXPLAIN и/или EXPLAIN QUERY PLAN. Следите за сканированием таблиц. Вы должны попытаться устранить дорогостоящие проверки, добавив индексы. Но не индексируйте все - это может ухудшить ситуацию. Часто вы можете добиться больших приростов производительности за счет использования составных (многоколоночных) индексов. Обратите внимание, что в любой заданной таблице SQLite не может использовать больше, чем один индекс (при выполнении заданного SQL-оператора) - так, выберите ваши индексы с умом. (См. Также основное объяснение в Планирование запросов.)

И чтобы решить ваши проблемы с обработкой данных в Java и SQLite, я думаю, что полностью оптимизированный (с соответствующими индексами и т.д.) SQLite-запрос с реляционными данными будет (почти) всегда быстрее, чем ручная обработка этих данных в Java. Это должно быть особенно актуально в вашем случае - все ваши данные в основном реляционные.

Одно небольшое примечание: ваш Android APK с использованием Java может иметь доступ к большему количеству памяти, чем по умолчанию SQLite - вы можете увеличить размер кэша SQLite для своей базы данных, используя setMaxSqlCacheSize() (эквивалент PRAGMA cache_size). Android по умолчанию - 10 (максимум 100), попробуйте увеличить его и посмотреть, не имеет значения для вашего запроса. Обратите внимание, что рабочий стол SQLite по умолчанию для этого параметра намного выше - 2000.

Ответ 2

Во-первых, я мало знаю о SQLite, но я полагаю, что он будет более или менее похож на MS SQL-Server.

Чаще всего проблема с производительностью для простого запроса, подобного этому, обычно относится к случаю отсутствия индекса, приводящего к полному сканированию таблицы, вместо частичного сканирования таблицы или поиска таблицы. Если у вас нет индекса в team_table.tournament_id, то SQLite придется сканировать всю таблицу, чтобы выполнить "t.tournament_id =?" операция. То же самое произойдет с match_table.team_home и match_table.team_away: отсутствующий индекс приведет к полному сканированию таблицы для операций объединения на m.team_home и m.team_away.

В остальном вы можете упростить свой запрос двумя способами. Первым будет сброс внешнего подзапроса и использование выражений или столбцов в вашем Заказе; то есть вы можете заменить "ORDER BY sp DESC, s.score DESC, s.go ASC" на "ORDER BY SUM (2 * w + t) DESC, SUM (GO) - SUM (GA) DESC, SUM ( GO) ASC" и избавиться от подзапроса s.

Второй способ - заменить UNION одним запросом, выполнив левую операцию соединения как на m.team_home, так и на m.team_away в то же время:

... FROM team_table t LEFT OUTER JOIN match_table m ON (m.team_home = t._id или m.team_away = t._id)...

После этого очень легко изменить ваши аргументы Case, чтобы правильно рассчитать различные оценки по tither. t._id равно m.team_home или m.team_away. Таким образом, вы можете не только удалить UNION, но также можете отбросить второй подзапрос.

Наконец, вы должны взглянуть на использование Left Join; так как я не уверен, действительно ли это необходимо для использования регулярного Inner Join.

После этого вы должны получить простое соединение Query с группой By и Order By и без подзапроса или объединения и, возможно, без левого соединения. Однако на данном этапе выражения в Order By могли бы стать немного сложными, поэтому вам придется принять решение о сохранении их таким образом, возвращая подзапрос или используя порядок столбцов (мой последний любимый выбор).

Без Союза запрос должен выполняться как минимум в два раза быстрее, но в конечном итоге, чтобы иметь хорошую производительность, конечным требованием будет иметь все надлежащие индексы; в противном случае производительность никогда не будет хорошей, если серверу sql необходимо выполнить несколько полных сканирований таблицы.

Ответ 3

Лично я бы рекомендовал, чтобы ваши запросы и структура базы данных были максимально простыми на Android и выполняли большую обработку через код.

Одна из причин, потому что сложная структура базы данных, смешанная с необходимостью обработки обновлений и понижений в версиях приложения без потери данных, может быстро выйти из-под контроля. Теперь я склонен настраивать и обрабатывать данные в виде NoSQL.

Другая причина заключается в том, что SQLite не хватает многих функций, которые необходимы в задачах реального мира, и в конечном итоге вы будете обрабатывать данные с помощью кода. Например, нет триггерных функций, поэтому поиск ближайших элементов может усложниться;)

private String getRelitiveDistanceQuery( double lng, double lat, int max){
    return "SELECT *, " +
    // NOTE: this long query was done because there are no trig functions in SQLite so this is an series expansion of some of the functions
    "((3.14159265358979/2-( ((("+Double.toString(lat)+"*0.0174532925199433)-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/6+("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/120-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/5040)*((`lat`*0.0174532925199433)-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/6+(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/120-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/5040)+(1-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/2+("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/24-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/720)*(1-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/2+(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/24-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/720)*(1-(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)/2+(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)/24-(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)/720))+1/6*((("+Double.toString(lat)+"*0.0174532925199433)-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/6+("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/120-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/5040)*((`lat`*0.0174532925199433)-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/6+(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/120-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/5040)+(1-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/2+("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/24-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/720)*(1-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/2+(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/24-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/720)*(1-(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)/2+(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)/24-(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)/720))*((("+Double.toString(lat)+"*0.0174532925199433)-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/6+("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/120-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/5040)*((`lat`*0.0174532925199433)-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/6+(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/120-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/5040)+(1-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/2+("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/24-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/720)*(1-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/2+(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/24-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/720)*(1-(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)/2+(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)/24-(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)/720))*((("+Double.toString(lat)+"*0.0174532925199433)-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/6+("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/120-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/5040)*((`lat`*0.0174532925199433)-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/6+(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/120-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/5040)+(1-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/2+("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/24-("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)*("+Double.toString(lat)+"*0.0174532925199433)/720)*(1-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/2+(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/24-(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)*(`lat`*0.0174532925199433)/720)*(1-(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)/2+(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)/24-(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)*(("+Double.toString(lng)+" -`lng`)*0.0174532925199433)/720)) ))) AS relDistance " +
    "FROM `"+TABLE_ITEMS+"` ORDER BY relDistance ASC LIMIT "+Integer.toString(max);
}

Я написал perl script, чтобы сгенерировать этот код, он расширяет триггерные функции, и он действительно работает очень хорошо, но он неуправляем, и я бы не рекомендовал его.

Ответ 4

Не совсем ответ на быстрые запросы, но: вы можете попробовать использовать дополнительные вспомогательные таблицы и заполнить их, указав триггеры в реальных таблицах данных. Таким образом, у вас будет подготовлено большинство обобщенных данных, и запросы будут проще.

Ответ 5

если вы используете подготовленный оператор, тогда это полезно для вас, потому что 1. подготовленное выражение намного безопаснее 2. Инъекция sql сложна 3. они не так сложны 4. Техническое обслуживание легко