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 同步 = 关闭； 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);