Оптимизируем скорость SQL вставки на Android устройствах

[zig1375]

А не рассматривали встроеный способ insert/update с использованием ContentValues?

final ContentValues values = new ContentValues(2);

values.put("id", 10);
values.put("title", "my title");
         
// db.update("Test", values, "id=10", null);
db.insert("Test", null, values);

[reJit]

Да. Рассматривался.
Он делает всё тоже самое что мы тут вместе с execSQL, готовит строку, подготавливает стейтмент, байндит к нему значения. В общем не быстро. Это можно посмотреть в исходниках андроида, что там внутри.
Посмотрите метод public long insertWithOnConflict из «Android SDK\sources\android-14\android\database\sqlite\SQLiteDatabase.java»

[gorynych_zmey]

Я бы предложил в приложении оставить только базовые вещи, а языковые настройки вынести в отдельные apk, которые можно будет подгружать по мере необходимости. Такой вариант не рассматривался?

[reJit]

Ну. Всё приложение строится вокруг этой базы с информацией о городах, и от того, сколько в том или ином языке в базе есть названий. Т.е. как раз названия на том или ином языке — и есть базовая вещь. Всё в приложении вертится вокруг них. Рассматривался вариант качать отдельную базу для каждого языка (базу только с переводами), но это большое дублирование информации, как минимум на уровне айдишников (ведь в каждой базе будет 40 000 одних и тех же айдишников для связки полей). Хотя наверно и такой вариант подошёл бы :)

Но я выбрал тот путь что выбрал, и в итоге получился интересный исследовательский труд, да и результат тоже был достигнут. Исходная АПК не слишком велика, а языков поддерживается много.

[palmut]

А вариант с методом ContentResolver.applyBatch и ContentProviderOperation не пробовали? Вполне прилично у меня прибавил производительности.

[reJit]

Не совсем понял как бы это помогло мне быстрее делать Update в базе данных?
Можете поподробнее расписать как это помогло вам?
Т.е. вы предлагает спрятать всю работу с SQLite базой за моделью ContentProvider-ов? Но как это сможет отразится на производительности?

[to_climb]

Я не очень хорошо знаком с SQLite, но, подозреваю, что она тоже поддерживает синтаксис:

update my_table
set col1 = 1, col2 = 2
where id = 3

Т.е. 3 запроса схлопываются в один.

[reJit]

Да. Всё верно. Это поддерживается.
Просто примеры кода с 3мя запросами.
Надо упомянуть об этом в статье. Я до этого тоже не сразу додумался, хотя очевиднейшее улучшение.
Спасибо.

[to_climb]

Ну тогда до кучи: обычно вставка сильно дешевле обновления.
Я бы попробовал сделать отдельную таблицу с языко-зависимыми данными (т.е. те 3 поля + язык + id в исходной таблице), а дальше — получение данных join'ами.
Ещё можно удалять данные (одной транзакцией) и вставлять заново, если схема данных приложения это позволяет.
(но это скорее идея для эксперимента)

[reJit]

Спасибо. Интересно. Поэксперементирую

[maxx_es]

а через ATTACH DATABASE разве нельзя было решить задачу?
в смысле, подкачать дополнительную базу с переводами, соединить с основной, а при выборке данных просто JOIN-ить с таблицей из присоединённой базы.
делал подобное в базе на 8-10К записей, и производительность (чтения) была очень приемлемой для мобильного устройства. подключение же дополнительных данных вообще было мгновенным.

[reJit]

Можно было, но как я уже писал в комментариях, я выбрал путь который выбрал.
Основной посыл статьи наверно не в том как решать такие задачи, а показать ошибки в работе с SQLite (ошибки который приводят к ухудшению производительности), и показать какую даёт прибавку переход на нативный уровень.

[reJit]

Не туда написал ответ

[Krovosos]

1. вставка намного быстрее update, как уже указали. Можно в отдельную таблицу. Можно в отдельную БД и attach.
2. возможно немного быстрее будет не полное оборачивание всего обновления в одну транзакцию, а commit каждые 1000-5000 операций
3. если данные сжаты gzip, то проще всего вообще скачивать SQL в виде одной большой строки: BEGIN; INSERT; INSERT;… COMMIT; BEGIN;… COMMIT и передать его весь сразу на выполнение. Отпадает нужда в переходе на C++, так как выполнение одного большого скрипта и там, и там будет примерно одинаково. И парсить ничего не нужно.
4. можно попробовать подшаманить SQLite, отключив журнал на время обновления, например. Есть риск потерять БД, если приложение будет «убито» во время обновления, но пользователь ведь может тупо переставить приложение, верно?
5. можно исполнить скачанный SQL на базе в памяти и приаттачить ее к БД на диске. приложение сразу начнет работать, а фоновым процессом будет перекачивать базу из памяти на диск.

[reJit]

Спасибо. Очень дельные советы. Буду впредь использовать.

[EndUser]

create virtual table using module со ссылкой на CSV, а затем insert () into select from не подошёл?

Извините, я sqlite сам не пользовал, но всегда подозреваю, что хорошие базы имеют собственные методы импорта, зачастую вполне шустрые. К слову, у Interbase и MSSQL это было получше велосипедов, хотя и не интерактивно — прогрессбар рисовать не с чего было.

[reJit]

Интересный вариант. Не знал даже про возможность открыть виртуальную таблицу на CSV

[EndUser]

Отпишите хронометраж, прошу.

[reJit]

Простите, хронометраж чего именно и в каком из 3х вариантов опмсанных в статье (Первый вариант Java кода, Второй вариант Java кода или Кода на С++)?

[EndUser]

Всех новых для вас методов в этих комментах. И вам выгодно найти быстрейший способ, и читателям мне статья даже ещё полезней станет.

[reJit]

Попробую найти время, и для пары новых методов отпишу (в ответах к комментариям, в которых тот или иной метод предлагали)

[reJit]

C PRAGMA ковырялся и в C++ варианте выставляю
PRAGMA synchronous = OFF
PRAGMA journal_mode = MEMORY

[reJit]

1 и 2 да, дали прирост процентов в 30 (всё на том же HTC Desire в варианте нс С++)
3 — нет, у меня нет форен кеёв.
4 — индексы по новым значениям строю после всех вставок, исследовали что это быстрее вот тут
Да и вообще там много интересных идей, как в самой статье, так и в ответах.
5 — с совсем выключенным журналированием тоже быстрее процентов на 15-20.
Итог, PRAGMA-ми можно добиться тоже не плохих результатов.
Спасибо.

[qw1]

Может, моя идея покажется бредовой, но я бы попробовал распространять локализации в виде готовой базы и подключать её в контекст SQL-запроса с помощью ATTACH DATABASE. В этом случае смена языка происходила бы мгновенно (аттачим другой файл на алиас базы локализации), но все запросы с именем пришлось бы переписать на join с таблицей этой базы.

Впрочем, есть ещё вариант: данные перелить из аттача в главную базу, но организацию цикла select-update возложить на сам движок SQLite:

insert or replace into CITIES (ID, NAME) select ID, NAME from ATTACHED_LOCALIZE_DB.CITIES