sqlite3中更快的批量插入?

• 将所有INSERT封装在一个事务中,即使只有一个用户,速度也要快得多。
• 使用事先准备好的陈述。

您想使用 .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 .

我测试过一些 pragmas 在这里的答案中提出:

• synchronous = OFF
• journal_mode = WAL
• journal_mode = OFF
• locking_mode = EXCLUSIVE
• 同步=关闭 + locking_mode=独占 + journal_mode=关闭

以下是我在交易中不同插入次数的数字:

增加批处理大小可以真正提高性能,而关闭日志、同步、获取独占锁只会带来微不足道的收益。约110k的点显示了随机背景负载如何影响数据库性能。

此外,值得一提的是 journal_mode=WAL 是违约的良好替代方案。它提供了一些增益,但不会降低可靠性。

C# Code.

你也可以试试 tweaking a few parameters 以获得额外的速度。具体来说,你可能想要 PRAGMA synchronous = OFF; .

• 增加 PRAGMA cache_size 数量要大得多。这将 增加缓存的页面数量 在记忆中。注: cache_size 是每个连接的设置。

• 将所有插入包裹到一个事务中,而不是每行一个事务。

• 使用编译的SQL语句进行插入。
• 最后,如前所述,如果您愿意放弃完全ACID合规性,请设置 PRAGMA synchronous = OFF; .

RE:“是否有更快的方法为每行数据生成插入语句?”

首先:利用Sqlite3将其减少到2个SQL语句 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复制。这听起来比实际更难,我用这种方式衡量了巨大的速度提升。

第二:利用这里提供的其他建议,即pragma设置和使用交易。

第三:也许可以看看是否可以删除目标表上的一些索引。这样,sqlite将为插入的每一行更新更少的索引

无法批量插入,但是 有一种方法可以写大块 记住,然后将它们提交给 数据库。对于C/C++API,只需执行以下操作:

sqlite3exec(db,“开始事务”, NULL,NULL,NULL);

…(插入语句)

sqlite3exec(db,“COMMIT事务”,NULL,NULL,空);

假设db是您的数据库指针。

一个好的折衷方案是在开始和结束之间包装好你的插件;结束;关键字即:

BEGIN;
INSERT INTO table VALUES ();
INSERT INTO table VALUES ();
...
END;

根据数据的大小和可用RAM的数量,将sqlite设置为使用全内存数据库而不是写入磁盘将获得最佳性能收益之一。

对于内存数据库,将NULL作为文件名参数传递给 sqlite3_open 和 make sure that TEMP_STORE is defined appropriately

(以上所有内容均摘自我对某问题的回答 separate sqlite-related question )

我发现这是一个很好的组合,适合一次性导入。

.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/