Пишу на Java модуль импорта данных с простого источника в SQLite базу данных. Модуль по сути занимается тем, что расбирает CSV и переливает его в базу. Количество записей в тестовом источнике порядка 700К и каждую из этих записей нужно сохранить в чистую базу данных. Количество таблиц — 13. Каждая из таблиц может иметь от 3 до 12 столбцов. Количество же строк для каждой таблицы может варьироваться от нуля к нескольким ста тысячам (в особенности для таблиц пошире).
Есть прототипы под JRE и под Android. Поковырявшись с JRE и JDBC, довольно просто добился весьма приемлемых результатов в несколько секунд с помощью пакетной обработки (addBatch + executeBatch). В Android альтернативная реализация занимает очень много времени (где-то 60 с) и пока, надеюсь не окончательный, использует следующий подход:
* исполнении всех INSERT-операторов в рамках одной транзакции (я так понял, это вообще стандартная идиома в Android: beginTransaction(), n x executeInsert(), setForeignKeyConstraintsEnabled(false), setTransactionSuccessful(), endTransaction());
* "пакетировании" записей в динамически формируемом INSERT-операторе (например, 83 записи в одном INSERT для самой широкой таблицы, потому что SQLite ограничивает количество переменных 999-ью (999 div 12 = 83));
* кеширование скомпилированных динамических операторов в локальном кеше (compileStatement(...) + Guava loading cache с ключом (table; bulk row count)).
Интересует следующее:
1) Что нужно изменить, чтобы хоть как-то сократить время импорта?
2) Может, есть способ заливать данные в базу напрямую в обход API? Что-то типа как LOAD DATA INFILE в MySQL. .import в консоли является, я так понимаю, просто обёрткой над множетсвенными INSERT-ами.
3) Оказаться от штатных средств Android или вообще попробовать другую (Р)СУБД?
Здравствуйте, halo, Вы писали:
H>Интересует следующее:
H>1) Что нужно изменить, чтобы хоть как-то сократить время импорта? H>2) Может, есть способ заливать данные в базу напрямую в обход API? Что-то типа как LOAD DATA INFILE в MySQL. .import в консоли является, я так понимаю, просто обёрткой над множетсвенными INSERT-ами. H>3) Оказаться от штатных средств Android или вообще попробовать другую (Р)СУБД?
Вряд ли это всё имеет отношение к андройду, скорее надо уметь пользоваться sqlite.
Открывать базу (sqlite3_open_v2) каким-нибудь магическим образом «file:data.db?nolock=1» для работы в режиме использования лишь одним процессом.
И настраивая работу (sqlite3_exec) по части журналирования/транзакций:
"PRAGMA synchronous = OFF; " \
"PRAGMA journal_mode = OFF; " \
"PRAGMA cache_size = 10000; " \
"PRAGMA page_size = 4096; " ...
А потом уже пихать данные в таблицу большими одиночными INSERT'ами с кучей VALUES через запятую. Настолько большими? Когда текст каждого запроса под два мегабайта.
Насколько это всё ускоряет работу? Порою в десятки раз.
Здравствуйте, a7d3, Вы писали:
A>Вряд ли это всё имеет отношение к андройду, скорее надо уметь пользоваться sqlite.
Именно Android и даже Java интересуют, к слову.
A>Открывать базу (sqlite3_open_v2) каким-нибудь магическим образом «file:data.db?nolock=1» для работы в режиме использования лишь одним процессом. A>И настраивая работу (sqlite3_exec) по части журналирования/транзакций: A>"PRAGMA synchronous = OFF; " \ A>"PRAGMA journal_mode = OFF; " \ A>"PRAGMA cache_size = 10000; " \ A>"PRAGMA page_size = 4096; " ...
A>А потом уже пихать данные в таблицу большими одиночными INSERT'ами с кучей VALUES через запятую. Настолько большими? Когда текст каждого запроса под два мегабайта. A>Насколько это всё ускоряет работу? Порою в десятки раз.
Круто-круто. Оно-то спасибо, но, если вас не затруднит: как это всё добро использовать с Android-коде? Я имею в виду прагмы: execSQL просто падает; rawQuery не приносит никакого результата вообще. Я за всё время выжал 60 секунд из 150, и мне кажется, что физически упёрся в потолок, который попытался описать в вопросе, и который пробить, как вижу, уже не получится. Кстати, отключение журналирования очень даже благоприятно сказывается на записи: https://shunix.com/android-sqlite-insertion-optimization/ , только у меня оно не даёт никакого результата. Там чувак добился результата в 17К записей в секунду. У меня же в среднем получается около 10,5К. У меня в прототипе есть узкие места: вычитка из CSV, пока-что де+сериализация для приведения данных к общему формату, и даже RxJava (так нужно). Потому вижу следующие пути дальше:
* если отбросить де+сериализацию, должно получиться быстрее;
* я так понимаю, что Android не полностью закрывает доступ к базе, предоставляя только API, так что можно поэкспериментировать c файлом БД, например, с помощью org.xerial.sqlite-jdbc.
Здравствуйте, halo, Вы писали:
H>Круто-круто. Оно-то спасибо, но, если вас не затруднит: как это всё добро использовать с Android-коде? Я имею в виду прагмы: execSQL просто падает; rawQuery не приносит никакого результата вообще.
Прагмы можно скармливать в самом начале первого же запросе вида: «CREATE TABLE IF NOT EXISTS», сразу после открытия конкретной базы.
Однако, основном прирост производительности при набивке таблиц базы даст запихивание данных большими одиночными INSERT'ами с кучей VALUES через запятую, когда текст каждого запроса под два мегабайта.
Здравствуйте, a7d3, Вы писали:
A>Однако, основном прирост производительности при набивке таблиц базы даст запихивание данных большими одиночными INSERT'ами с кучей VALUES через запятую, когда текст каждого запроса под два мегабайта.
Упустил этот момент выше. Если я вас правильно понимаю, у меня таких больших запросов точно никогда не будет (сами поля в CSV и в самой схеме сильно ограничены по длине + лимит на количество в 999 параметров в самом запросе). Таким образом, для самой широкой таблицы в один INSERT могу прописать 83 записи; для самой узкой- 333. Если я правильно представляю себе механизм запросов "на физическом уровне", в каждом из случаев размер запросов никогда не будет превышать нескольких сот кБ.
Здравствуйте, halo, Вы писали:
H>Здравствуйте, a7d3, Вы писали:
A>>Однако, основном прирост производительности при набивке таблиц базы даст запихивание данных большими одиночными INSERT'ами с кучей VALUES через запятую, когда текст каждого запроса под два мегабайта.
H>Упустил этот момент выше. Если я вас правильно понимаю, у меня таких больших запросов точно никогда не будет (сами поля в CSV и в самой схеме сильно ограничены по длине + лимит на количество в 999 параметров в самом запросе). Таким образом, для самой широкой таблицы в один INSERT могу прописать 83 записи; для самой узкой- 333. Если я правильно представляю себе механизм запросов "на физическом уровне", в каждом из случаев размер запросов никогда не будет превышать нескольких сот кБ.
Итоговая производительность обработки данных аффектится в тех случаях, когда запихивание данных в базу становится узким местом во всей цепочке?
Значит надо копить данные, складывая в некий быстрой кэш, а по мере его наполнения уже набивать таблицы СУБД данными с этого кэша. Используя при работе с конкретной СУБД тот подход, что даёт наибольшую производительность данной операции.
Параметры в SQL-запросе и количество VALUES в случае INSERT это далеко не одно и тоже. У SQLite может быть сколько угодно записей/строк/кортежей в запросе на вставку, в этом вопросе нет ограничения вида «999 делить на количество столбцов в таблице». Есть другой ограничение — лишь бы текст получившегося INSERT-а был менее двух мегабайт.
Здравствуйте, a7d3, Вы писали:
A>Итоговая производительность обработки данных аффектится в тех случаях, когда запихивание данных в базу становится узким местом во всей цепочке?
Да, именно так.
A>Значит надо копить данные, складывая в некий быстрой кэш, а по мере его наполнения уже набивать таблицы СУБД данными с этого кэша. Используя при работе с конкретной СУБД тот подход, что даёт наибольшую производительность данной операции.
Я это и попытался сделать: переливание записей не делается напрямую построчно, а сначала пишутся во внутренний буфер, и только по мере его заполнености записи сбрасываются в базу одним оператором. Тупая запись каждой строки без буферизации составляла примерно в два разы больше по общему времени. Поэтому:
A>Параметры в SQL-запросе и количество VALUES в случае INSERT это далеко не одно и тоже. У SQLite может быть сколько угодно записей/строк/кортежей в запросе на вставку, в этом вопросе нет ограничения вида «999 делить на количество столбцов в таблице». Есть другой ограничение — лишь бы текст получившегося INSERT-а был менее двух мегабайт.
Мне всё же упирается в 999 переменных. При попытке превысить лимит бросается исключение. Android снять лимит, насколько мне известно, штатными средствами не даст.
Здравствуйте, halo, Вы писали:
A>>Параметры в SQL-запросе и количество VALUES в случае INSERT это далеко не одно и тоже. У SQLite может быть сколько угодно записей/строк/кортежей в запросе на вставку, в этом вопросе нет ограничения вида «999 делить на количество столбцов в таблице». Есть другой ограничение — лишь бы текст получившегося INSERT-а был менее двух мегабайт.
H>Мне всё же упирается в 999 переменных. При попытке превысить лимит бросается исключение. Android снять лимит, насколько мне известно, штатными средствами не даст.
А что мешает самостоятельно подставить значения в текст запроса?
Здравствуйте, a7d3, Вы писали:
A>А что мешает самостоятельно подставить значения в текст запроса? A>
A>INSERT INTO 'table_name' (col1, col2, col3, col4, col5)
A>VALUES (1, '1', '1', '1', '1'),
A> (2, '12', '12', '12', '12'),
A> (3, '13', '13', '13', '13'),
A> (4, '14', '14', '14', '14'),
A> (5, '15', '15', '15', '15'),
A> ...
A> ;
A>
A>Т.е. создать в памяти строку с текстом SQL-запроса с подставленными конкретными значениями и передать её СУБД на исполнение.
A>Не уж то не царское это дело, такими глупостями заниматься?
Мне почти смешно стало, да. Что мешает:
* мне пока не кажется, что ad-hoc запросы лучше скомпилированных, особенно если я могу контроллировать их создание и выдачу из кеша (зачем тогда, например, существует возможность компиляции запросов для их переиспользования?), а сериализацией значений занимается сам драйвер;
* ручное экранирование строковых значений и ещё более тесная привязка к SQL и его особенностям, а также вопрос безопасности от неправильного или случайно забытого экранирования во избежание SQL-инъекций;
* возня с ручным созданием запросов и аллокацией больших строк для них:
** всё-равно нужно в конечном счёте формировать java.lang.String, а не java.lang.CharSequence (что странно в виду повального использования его в android.*-пакетах), и даже передача ему массива символов создаст копию массива для работы самого java.lang.String -- увы, но это Java;
** нужно заниматься конвертацией чисел в строковое представление, что для меня чревато дополнительной вознёй с конверторами и поиском таких, что умеют писать в массив символов, не создавая промежутночых экземпляров java.lang.String.
** стараться как-то наиболее эффективно укладываться в отведённый буфер (что, в принципе, в какой-то мере возможно, зная заранее тип таблицы, а также пытаться оценить размер выходного INSERT, включая размеры экранированных строк -- или создавать огромный буфер, даже посчитав самый худший случай?);
** быть уверенным, что большие строки будут адекватно работать на всех устройствах + насколько большими они могут быть на том или другом устройстве?
** может, ещё что-то забыл упомянуть.
Ничего этого не нужно в JDBC вообще, и оно прекрасно работает в "настольной" Java без таких извратов. Конечно, я тоже молодец: сравниваю неплохой лептоп и неплохое мобильное устройство, но такая огромная разница (3с против изначальных 120-150с и пока 60с) первым делом заставляет задуматься том, правильно ли используется сам API (android.database.* != JDBC), без погружения в детали реализации (ведь API для того по большому счёту и существуют, верно?). Отнюдь, ничего царского + возни для, казалось бы, простой задачи пока видится куда больше, и не понятно, стоит ли даже реализация такого подхода переписывания части прототипа ввиду неэффективности на Java и сопутствующих API. Но в копилку вариантов, конечно, такое сгодится. Спасибо.
Здравствуйте, halo, Вы писали:
H>Здравствуйте, a7d3, Вы писали:
A>>Не уж то не царское это дело, такими глупостями заниматься?
H> Отнюдь, ничего царского + возни для, казалось бы, простой задачи пока видится куда больше, и не понятно, стоит ли даже реализация такого подхода переписывания части прототипа ввиду неэффективности на Java и сопутствующих API. Но в копилку вариантов, конечно, такое сгодится. Спасибо.
Нормальная реакция, почти у всех такая, когда первый раз с подобной задачей сталкиваются.
После чего делается несколько тестов на производительность, а когда надоело играться, то происходит уже переосмысление.
Здравствуйте, halo, Вы писали:
H>Здравствуйте.
H>Пишу на Java модуль импорта данных с простого источника в SQLite базу данных. Модуль по сути занимается тем, что расбирает CSV и переливает его в базу. Количество записей в тестовом источнике порядка 700К и каждую из этих записей нужно сохранить в чистую базу данных. Количество таблиц — 13. Каждая из таблиц может иметь от 3 до 12 столбцов. Количество же строк для каждой таблицы может варьироваться от нуля к нескольким ста тысячам (в особенности для таблиц пошире).
H>Есть прототипы под JRE и под Android. Поковырявшись с JRE и JDBC, довольно просто добился весьма приемлемых результатов в несколько секунд с помощью пакетной обработки (addBatch + executeBatch). В Android альтернативная реализация занимает очень много времени (где-то 60 с) и пока, надеюсь не окончательный, использует следующий подход:
Не совсем понятно,
а) Зачем какая-то альтернативная реализация по Android, ведь не обязательно
пользоваться sqlite доступным через Android API, большинство серьезных приложений используют
свою сборку sqlite и работают с ней через JNI, есть уже готовые opensource проекты с такими
возможностями
б) Под addBatch/executeBatch, имеется ввиду ".import path/to/csv"
как здесь http://www.sqlitetutorial.net/sqlite-import-csv/
Здравствуйте, Zhendos, Вы писали:
Z>а) Зачем какая-то альтернативная реализация по Android, ведь не обязательно Z> пользоваться sqlite доступным через Android API, большинство серьезных приложений используют Z> свою сборку sqlite и работают с ней через JNI, есть уже готовые opensource проекты с такими Z> возможностями
Имелись в виду реализации слоя доступа з базам данных под JRE+JDBC и Android+Android API. Использование любого нестандартного доступа к базе пока не рассматриваю.
Z>б) Под addBatch/executeBatch, имеется ввиду ".import path/to/csv" Z> как здесь http://www.sqlitetutorial.net/sqlite-import-csv/
Нет. Я говорил о java.sql.PreparedStatement. Кстати, .import разве не реализуется самой оболочкой?
