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

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

String sql = "SELECT s.team_id, s.team_name, s.gp, sw, st, sl, s.go, s.ga, s.score, sp 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 sp 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(); 

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

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

Solutions Collecting From Web of "Производительность Android SQLite в сложных запросах"

Если бы я был вами, я бы скопировал вашу базу данных 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.

Во-первых, я мало что знаю о 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 необходимо выполнить несколько полных сканирований таблицы.

Лично я бы рекомендовал, чтобы ваши запросы и структура базы данных были максимально простыми на 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 для генерации этого кода, он расширяет триггерные функции, и на самом деле он работает очень хорошо, но он неуправляем, и я бы не рекомендовал его.

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

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