C语言 SQLite 中的批量插入性能

Question

作为一种练习，我看到了将批量记录插入SQLite的速度。数据集约为 50MB，包含 1M 行。这是我目前拥有的：

sqlite3 *db;
int rc = sqlite3_open("MyDB.db", &db);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
char* sql_buffer = malloc(200 * sizeof(char));
for (int i=0; item=row[i]; i ++) {
snprintf(sql_buffer, 200, "insert into myTable (id, format, size) VALUES (%d, '%s', %d)", item.id, item.format, item.size);
rc = sqlite3_exec(db, sql_buffer, NULL, NULL, NULL);
}
sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL, NULL);

做上述1M插入，需要3.39s.大约 90% 的时间是 SQLite 插入，10% 是snprintf函数。我尝试了以下内容，看看它是否会提高速度：

每 10K

• 、50K、100K 后进行插入，而不是在最后 (1M(
• 写入内存而不是文件。
• 更改各种编译指示，例如：PRAGMA cache_size = 400000; PRAGMA synchronous = OFF; PRAGMA journal_mode = OFF;...

这些似乎都没有超过0.1s差异的影响。

还有其他方法可以提高这里的插入速度吗？如果我们假设文件是"解析"的，不能直接从csv文件之类的东西加载，理论上是否可以在1秒内插入1M行？如果没有，做这样的事情有什么限制？

Answer 1

理解使用您当前的方法，插入 100 万行将需要对 SQLite 执行 100 万次单独的往返插入。 相反，您可以尝试使用以下两种方法之一。 对于较新版本的 SQLite：

INSERT INTO myTable (id, format, size)
VALUES
(%d, '%s', %d),
(%d, '%s', %d),
(%d, '%s', %d),
... (more rows)

对于早期版本的 SQLite，您可以使用INSERT INTO ... SELECT构造：

INSERT INTO myTable (id, format, size)
SELECT %d, '%s', %d UNION ALL
SELECT %d, '%s', %d UNION ALL
... (more rows)

这里的基本思想是，您可以尝试仅对所有数据对 SQLite 进行一次插入调用，而不是一次插入一行。

不是 C 人，但以下是从 C 代码构建插入字符串的方法：

const int MAX_BUF = 1000;  // make this as large as is needed
char* sql_buffer = malloc(MAX_BUF * sizeof(char));
int length = 0;
length += snprintf(sql_buffer+length, MAX_BUF-length, "INSERT INTO myTable (id, format, size) VALUES");
for (int i=0; item=row[i]; i++) {
length += snprintf(sql_buffer+length, MAX_BUF-length, " (%d, '%s', %d)", item.id, item.format, item.size);
}
rc = sqlite3_exec(db, sql_buffer, NULL, NULL, NULL);