Быстрее массовых вставок в sqlite3?

У меня есть файл около 30000 строк данных, которые я хочу загрузить в базу данных sqlite3. Есть ли более быстрый способ генерации операторов вставки для каждой строки данных?

Данные разделены пробелами и отображаются непосредственно в таблицу sqlite3. Есть ли какой-нибудь метод массовой вставки для добавления тома в базу данных?

Кто-нибудь придумал какой-то изумительно замечательный способ сделать это, если он не встроен?

Я должен предварять это, спрашивая, есть ли способ C ++ сделать это из API?

12.12.2008 20:23:04
11 ОТВЕТОВ
РЕШЕНИЕ

Вы также можете попробовать настроить несколько параметров, чтобы получить дополнительную скорость. В частности, вы, вероятно, хотите PRAGMA synchronous = OFF;.

21
12.12.2008 21:11:12
Прагма синхронная = ВЫКЛ - плохая идея - она ​​вряд ли повлияет на производительность для массовых вставок, и ваша БД будет повреждена при сбое питания. Гораздо лучшая идея - обернуть вставки в транзакцию.
Eamon Nerbonne 31.08.2009 12:39:49
Обертывание ВСТАВКИ в СДЕЛКУ и использование PRAGMA journal_mode = MEMORY; Предотвратит попадание INSERT на диск до конца транзакции.
Ted 17.03.2010 23:04:29
Остерегайтесь того, что ПАМЯТЬ испортит дБ при сбое питания
Anders Rune Jensen 6.06.2011 22:38:14
PRAGMA journal_mode = WAL; позволит одновременно писать много писателей, и вы сможете использовать потоки для записи данных. Обратите внимание, что при активированной записи в журнал записи об ошибке база данных не повреждена после сбоя питания.
Amine Zaine 15.05.2016 02:06:05
  • Обернуть все INSERT в транзакции, даже если есть один пользователь, это гораздо быстрее.
  • использовать подготовленные заявления.
57
12.12.2008 20:54:50
Правда для большинства (всех?) Баз данных SQL.
stesch 12.12.2008 21:22:18
PRAGMA journal_mode = MEMORY; Может быть полезным для некоторых людей
Ted 17.03.2010 23:05:36
stackoverflow.com/questions/43511725/… пример транзакции для Nodejs dev
Prashant Tapase 28.06.2019 10:14:33
  • Увеличение PRAGMA default_cache_size до гораздо большего числа. Это увеличит количество страниц, кэшируемых в памяти.

  • Оберните все вставки в одну транзакцию, а не одну транзакцию в строке.

  • Используйте скомпилированные операторы SQL для вставки.
  • Наконец, как уже упоминалось, если вы хотите отказаться от полного соответствия ACID, установите PRAGMA synchronous = OFF;.
18
13.12.2008 00:25:00
PRAGMA default_cache_sizeсейчас устарел
david 19.03.2019 19:39:30

В зависимости от размера данных и объема доступной оперативной памяти один из лучших приростов производительности будет достигнут при настройке sqlite для использования базы данных «все в памяти» вместо записи на диск.

Для баз данных в памяти передайте NULL в качестве аргумента имени файла sqlite3_openи убедитесь, что TEMP_STORE определен соответствующим образом

(Весь приведенный выше текст взят из моего собственного ответа на отдельный вопрос, связанный с sqlite )

3
23.05.2017 12:08:35
Ссылка указывает на неполный документ. Информации меньше, чем можно было бы надеяться,
Richard 9.10.2009 22:23:26

Вы хотите использовать .importкоманду. Например:

$ cat demotab.txt
44      92
35      94
43      94
195     49
66      28
135     93
135     91
67      84
135     94

$ echo "create table mytable (col1 int, col2 int);" | sqlite3 foo.sqlite
$ echo ".import demotab.txt mytable"  | sqlite3 foo.sqlite

$ sqlite3 foo.sqlite
-- Loading resources from /Users/ramanujan/.sqliterc
SQLite version 3.6.6.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from mytable;
col1    col2
44      92
35      94
43      94
195     49
66      28
135     93
135     91
67      84
135     94

Обратите внимание, что эта команда массовой загрузки не SQL, а пользовательская функция SQLite. Как таковой он имеет странный синтаксис, потому что мы передаем его через echoинтерактивный интерпретатор командной строки sqlite3.

В PostgreSQL это эквивалентно COPY FROM: http://www.postgresql.org/docs/8.1/static/sql-copy.html.

В MySQL это LOAD DATA LOCAL INFILE: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

И последнее: не забывайте быть осторожным со значением .separator. Это очень распространенная ошибка при массовых вставках.

sqlite> .show .separator
     echo: off
  explain: off
  headers: on
     mode: list
nullvalue: ""
   output: stdout
separator: "\t"
    width:

Вы должны явно установить разделитель в качестве пробела, табуляции или запятой перед выполнением .import.

35
24.09.2012 16:46:11
Это здорово и очень быстро. 20 минут уменьшено до 3 секунд.
Gazzer 12.03.2011 14:42:19
это работает для таблиц с автоинкрементными первичными ключами? Я попытался использовать NULL в файле для столбца с автоинкрементом, но он выдает ошибку.
Aditya Naidu 30.01.2012 19:05:38
Рассматривая код для shell.c SQLite, .import просто использует подготовленный оператор внутри транзакции.
dlanod 9.08.2012 01:53:06
sqlite имеет странное поведение с \ t в командной строке, вы должны дать РЕАЛЬНУЮ вкладку опции -separator. В командной строке используйте Control-v <TAB>, чтобы вставить настоящий TAB.
The Demz 2.12.2013 13:03:49

Если вы просто вставляете один раз, у меня для вас может быть подвох.

Идея проста: сначала вставить в базу данных памяти, затем сделать резервную копию и, наконец, восстановить в исходный файл базы данных.

Я написал подробные шаги в моем блоге . :)

0
28.12.2009 15:03:17

Невозможно выполнить массовую вставку, но есть способ записать большие куски в память, а затем зафиксировать их в базе данных. Для API C / C ++ просто выполните:

sqlite3_exec (дБ, «НАЧАЛО СДЕЛКИ», NULL, NULL, NULL);

... (Вставить заявления)

sqlite3_exec (дБ, «СОВЕРШЕНИЕ СООТВЕТСТВИЯ», ​​NULL, NULL, NULL);

Предполагая, что db - это указатель вашей базы данных.

5
20.01.2010 19:21:53

RE: «Есть ли более быстрый способ генерации операторов вставки для каждой строки данных?»

Во-первых: сократите его до 2 операторов SQL, используя API виртуальной таблицы Sqlite3, например:

create virtual table vtYourDataset using yourModule;
-- Bulk insert
insert into yourTargetTable (x, y, z)
select x, y, z from vtYourDataset;

Идея заключается в том, что вы реализуете интерфейс C, который читает ваш исходный набор данных и представляет его в SQlite в виде виртуальной таблицы, а затем вы за один раз делаете копию SQL из исходной таблицы в целевую таблицу. Звучит сложнее, чем есть на самом деле, и я измерил огромные улучшения скорости таким образом.

Второе: воспользуйтесь другим советом, представленным здесь, то есть настройками прагмы и использованием транзакции.

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

10
6.08.2010 08:19:09
+1 на самом деле это способ "c" сделать это из API (как было запрошено), хороший
AlexD 29.06.2011 14:15:06

Хороший компромисс - обернуть ваши ВСТАВКИ между НАЧАЛАМИ; и конец; ключевое слово т.е.

BEGIN;
INSERT INTO table VALUES ();
INSERT INTO table VALUES ();
...
END;
4
8.09.2011 09:53:46
Там такжеINSERT INTO table VALUES (),(),();
ZN13 8.12.2017 21:39:58

Я обнаружил, что это хороший микс для импорта одним выстрелом.

.echo ON

.read create_table_without_pk.sql

PRAGMA cache_size = 400000; PRAGMA synchronous = OFF; PRAGMA journal_mode = OFF; PRAGMA locking_mode = EXCLUSIVE; PRAGMA count_changes = OFF; PRAGMA temp_store = MEMORY; PRAGMA auto_vacuum = NONE;

.separator "\t" .import a_tab_seprated_table.txt mytable

BEGIN; .read add_indexes.sql COMMIT;

.exit

источник: http://erictheturtle.blogspot.be/2009/05/fastest-bulk-import-into-sqlite.html

дополнительная информация: http://blog.quibb.org/2010/08/fast-bulk-inserts-into-sqlite/

1
18.11.2015 09:16:51

Я проверил несколько прагм, предложенных в ответах здесь:

  • synchronous = OFF
  • journal_mode = WAL
  • journal_mode = OFF
  • locking_mode = EXCLUSIVE
  • synchronous = OFF+ locking_mode = EXCLUSIVE+journal_mode = OFF

Вот мои номера для различного количества вставок в транзакции:

Увеличение размера пакета может дать вам реальное повышение производительности, а отключение журнала, синхронизация, получение эксклюзивной блокировки даст незначительный выигрыш. Точки около ~ 110k показывают, как случайная фоновая загрузка может повлиять на производительность вашей базы данных.

Также стоит отметить, что journal_mode=WALэто хорошая альтернатива настройкам по умолчанию. Это дает некоторый выигрыш, но не снижает надежность.

Код C #.

4
24.10.2019 18:59:37