У меня есть файл из 30000 строк данных, которые я хочу загрузить в базу данных sqlite3. Есть ли более быстрый способ, чем создание операторов вставки для каждой строки данных?
Данные разделяются пробелами и отображаются непосредственно в таблицу sqlite3. Есть ли какой-либо метод объемной вставки для добавления данных объема в базу данных?
Кто-нибудь придумал какой-нибудь хитроумный способ сделать это, если он не встроен?
Я должен предисловие к этому, спросив, есть ли способ С++ сделать это из API?
Вы также можете попробовать настроить несколько параметров, чтобы получить дополнительную скорость. В частности, вы, вероятно, хотите PRAGMA synchronous = OFF;
.
Вы хотите использовать команду .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
.
Увеличить PRAGMA default_cache_size
к значительно большему числу. Это будет
увеличить количество кэшированных страниц
в памяти.
Оберните все вставки в одну транзакцию, а не одну транзакцию в строку.
PRAGMA synchronous = OFF;
.PRAGMA default_cache_size
теперь устарела
RE: "Есть ли более быстрый способ создания операторов вставки для каждой строки данных?"
Сначала: сократите это до двух операторов SQL, используя Sqlite3 Virtual table API, например.
create virtual table vtYourDataset using yourModule;
-- Bulk insert
insert into yourTargetTable (x, y, z)
select x, y, z from vtYourDataset;
Идея здесь заключается в том, что вы реализуете интерфейс C, который читает ваш исходный набор данных и представляет его в SQlite как виртуальную таблицу, а затем вы копируете SQL из источника в целевую таблицу за один раз. Это звучит сложнее, чем есть на самом деле, и я измерил огромные улучшения скорости таким образом.
Второе: используйте другие рекомендации, представленные здесь, то есть настройки прагмы и использование транзакции.
В-третьих: Возможно, посмотрите, можете ли вы покончить с некоторыми индексами в целевой таблице. Таким образом, sqlite будет иметь меньше индексов для обновления для каждой вставленной строки.
Нет способа вставки вставки, но есть способ написать большие куски в память, а затем передать их база данных. Для C/С++ API просто выполните:
sqlite3_exec (db, "НАЧАТЬ ПЕРЕДАЧУ", NULL, NULL, NULL);
... (инструкции INSERT)
sqlite3_exec (db, "COMMIT TRANSACTION", NULL, NULL, NULL);
Предполагая, что db является вашим указателем базы данных.
Хорошим компромиссом является обернуть ваши ВСТАВКИ между BEGIN; и конец; ключевое слово i.e:
BEGIN;
INSERT INTO table VALUES ();
INSERT INTO table VALUES ();
...
END;
INSERT INTO table VALUES (),(),();
В зависимости от размера данных и объема доступной ОЗУ, один из лучших достижений производительности будет происходить путем установки sqlite для использования базы данных "все в памяти", а не записи на диск.
Для баз данных в памяти передайте NULL в качестве аргумента имени файла sqlite3_open
и убедитесь, что TEMP_STORE определен соответствующим образом
(Весь вышеприведенный текст выдается из моего собственного ответа на отдельный вопрос, связанный с sqlite)
Я нашел это хорошим сочетанием для импорта с одним выстрелом.
.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/
Если вы просто вставляете один раз, у меня может быть трюк для вас.
Идея проста, сначала вставляемая в базу данных памяти, затем резервное копирование и, наконец, восстановление в исходный файл базы данных.
Я написал подробные шаги в мой блог.:)