问题

优化SQLite很棘手. C应用程序的Bulkinsert性能可以从每秒85个插入到每秒96,000多个插入!

背景:我们使用SQLite作为桌面应用程序的一部分.我们有大量的配置数据存储在XML文件中,当应用程序初始化时解析并加载到SQLite数据库进一步处理. SQLite对于这种情况是理想的,因为它很快,它不需要专门的配置,数据库存储在磁盘上作为单个文件.

理由:最初我对我看到的性能感到失望.它转出了SQLite的性能可以根据数据库的配置方式和使用API的方式大不相同(包括bulking和选择).弄清楚所有选项和技术是什么并不是一个微不足道的事情,所以我认为创建这个社区wiki条目来与Stack Overflow读者共享结果以保存其他人的调查麻烦是谨慎的.

实验:我认为最好编写一些C代码并实际测量各种选项的影响.我们将从一些简单的数据开始:

  • 一个 28 MB TAB 分隔的文本文件(约 865,000 条记录) 完整的多伦多 的过境时间表
  • 我的测试机是运行Windows XP的3.60 GHz P4.
  • 代码是用 Visual C++ 2005作为“Release”编译的,其中包括“完全优化”(/ Ox)和法惠或快速代码(/ Ot)。
  • 我正在使用SQLite“Amalgamation”,直接编译到我的测试应用程序中.我碰巧拥有的SQLite版本有点老(3.6.7),但我怀疑这些结果将与最新版本相当(如果你认为其他的话请留下评论).

让我们写一些代码!

代码:一个读取文本文件line-byline的简单C程序,将字符串分解为值,然后将数据插入到SQLite数据库中.在这个代码的“基线”版本中,创建数据库,但我们实际上不会插入数据:

 /*************************************************************
    Baseline code to experiment with SQLite performance.

    Input data is a 28 MB TAB-delimited text file of the
    complete Toronto Transit System schedule/route info
    from http://www.toronto.ca/open/datasets/ttc-routes/

**************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <string.h>
#include "sqlite3.h"

#define INPUTDATA "C:\TTC_schedule_scheduleitem_10-27-2009.txt"
#define DATABASE "c:\TTC_schedule_scheduleitem_10-27-2009.sqlite"
#define TABLE "CREATE TABLE IF NOT EXISTS TTC (id INTEGER PRIMARY KEY, Route_ID TEXT, Branch_Code TEXT, Version INTEGER, Stop INTEGER, Vehicle_Index INTEGER, Day Integer, Time TEXT)"
#define BUFFER_SIZE 256

int main(int argc, char **argv) {

    sqlite3 * db;
    sqlite3_stmt * stmt;
    char * sErrMsg = 0;
    char * tail = 0;
    int nRetCode;
    int n = 0;

    clock_t cStartClock;

    FILE * pFile;
    char sInputBuf [BUFFER_SIZE] = " ";

    char * sRT = 0;  /* Route */
    char * sBR = 0;  /* Branch */
    char * sVR = 0;  /* Version */
    char * sST = 0;  /* Stop Number */
    char * sVI = 0;  /* Vehicle */
    char * sDT = 0;  /* Date */
    char * sTM = 0;  /* Time */

    char sSQL [BUFFER_SIZE] = " ";

    /*********************************************/
    /* Open the Database and create the Schema */
    sqlite3_open(DATABASE, &db);
    sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);

    /*********************************************/
    /* Open input file and import into Database*/
    cStartClock = clock();

    pFile = fopen (INPUTDATA,"r");
    while (!feof(pFile)) {

        fgets (sInputBuf, BUFFER_SIZE, pFile);

        sRT = strtok (sInputBuf, "	");     /* Get Route */
        sBR = strtok (NULL, "	");            /* Get Branch */
        sVR = strtok (NULL, "	");            /* Get Version */
        sST = strtok (NULL, "	");            /* Get Stop Number */
        sVI = strtok (NULL, "	");            /* Get Vehicle */
        sDT = strtok (NULL, "	");            /* Get Date */
        sTM = strtok (NULL, "	");            /* Get Time */

        /* ACTUAL INSERT WILL GO HERE */

        n++;
    }
    fclose (pFile);

    printf("Imported %d records in %4.2f seconds
", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

    sqlite3_close(db);
    return 0;
}
 

"控制"

运行代码as-is实际上没有执行任何数据库操作,但它会让我们知道原始的C文件I/O和字符串处理操作有多快.

进口记录 864913 0.94 秒

很棒!我们可以每秒做920,000个插入,只要我们实际上没有做任何插入: –)


"Worst-Case-Scenario"

我们将使用从文件中读取的值生成SQL字符串并使用sqlite3_exec调用该SQL操作:

 sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, '%s', '%s', '%s', '%s', '%s', '%s', '%s')", sRT, sBR, sVR, sST, sVI, sDT, sTM);
sqlite3_exec(db, sSQL, NULL, NULL, &sErrMsg);
 

这将很慢,因为SQL将编译成每个插入的VDBE代码,每个插入都会在自己的事务中发生.多么慢?

进口记录 864913 9933.61 秒

yikes!2小时45分钟!每秒只有85个插入。

使用交易

默认情况下,SQLite将在唯一事务中评估每个INSERT / UPDATE语句.如果执行大量插入,最好将操作包装在事务中:

 sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    ...

}
fclose (pFile);

sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
 

进口记录 864913 38.03 秒

这更好.简单地将我们的所有插入包装在一个事务中将我们的性能提高到每秒23,000个插入.

使用编写的说明

使用事务是一个巨大的改进,但是如果我们使用相同的SQL over-and-over,重新编译每个插入的SQL语句没有意义.让我们使用sqlite3_prepare_v2一次编译我们的SQL语句,然后使用sqlite3_bind_text将我们的参数绑定到该语句:

 /* Open input file and import into the database */
cStartClock = clock();

sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, @RT, @BR, @VR, @ST, @VI, @DT, @TM)");
sqlite3_prepare_v2(db,  sSQL, BUFFER_SIZE, &stmt, &tail);

sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    fgets (sInputBuf, BUFFER_SIZE, pFile);

    sRT = strtok (sInputBuf, "	");   /* Get Route */
    sBR = strtok (NULL, "	");        /* Get Branch */
    sVR = strtok (NULL, "	");        /* Get Version */
    sST = strtok (NULL, "	");        /* Get Stop Number */
    sVI = strtok (NULL, "	");        /* Get Vehicle */
    sDT = strtok (NULL, "	");        /* Get Date */
    sTM = strtok (NULL, "	");        /* Get Time */

    sqlite3_bind_text(stmt, 1, sRT, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, sBR, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, sVR, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 4, sST, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 5, sVI, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 6, sDT, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 7, sTM, -1, SQLITE_TRANSIENT);

    sqlite3_step(stmt);

    sqlite3_clear_bindings(stmt);
    sqlite3_reset(stmt);

    n++;
}
fclose (pFile);

sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);

printf("Imported %d records in %4.2f seconds
", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

sqlite3_finalize(stmt);
sqlite3_close(db);

return 0;
 

16.27中的进口记录864913 秒

很好!有更多的代码(不要忘记调用sqlite3_clear_bindingssqlite3_reset),但我们的性能增加了一倍以上,每秒53,000个插入.

pragma同步=OFF

默认情况下,SQLite在发出OSlevel write命令后暂停.这保证数据被写入磁盘.通过设置synchronous = OFF,我们指示SQLite简单地将数据传输到操作系统以便写入,然后继续.如果计算机在数据写入平台之前遇到灾难性崩溃(或功率故障),数据库文件可能会受到损坏:

 /* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
 

12.41中进口的864913条记录 秒

这些改进现在更小了,但我们每秒多达69,600个插入。

pragma journal_mode = MEMORY

考虑通过评估PRAGMA journal_mode = MEMORY将回滚日志存储在内存中.您的事务将更快,但如果您失去功率或您的程序在事务期间崩溃,您的数据库可能会由于部分完成的事务而处于腐败状态:

 /* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);
 

进口记录 864913 13.50 秒

比之前的优化慢一点,每秒64,000插入。

pragma同步= OFF和PRAGMA journal_mode = MEMORY

让我们结合前两个优化.它有点风险(在崩溃的情况下),但我们只是导入数据(不运行银行):

 /* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);
 

进口记录 864913 12.00 秒

太棒了!我们能每秒做72000个插入。

使用内存数据库

只是为了踢球,让我们基于以前的所有优化并重新定义数据库文件名,所以我们完全在RAM中工作:

 #define DATABASE ":memory:"
 

在10.94中导入864913条记录 秒

将数据库存储在 RAM 中是不太实用的,但是我们可以每秒执行 79,000 个插入令人印象深刻。

重构C代码

虽然没有具体的SQLite改进,但我不喜欢char*分配操作中的额外while循环.让我们快速重构该代码以将strtok()的输出直接传递给sqlite3_bind_text(),并让编译器尝试加速我们的事情:

 pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    fgets (sInputBuf, BUFFER_SIZE, pFile);

    sqlite3_bind_text(stmt, 1, strtok (sInputBuf, "	"), -1, SQLITE_TRANSIENT); /* Get Route */
    sqlite3_bind_text(stmt, 2, strtok (NULL, "	"), -1, SQLITE_TRANSIENT);    /* Get Branch */
    sqlite3_bind_text(stmt, 3, strtok (NULL, "	"), -1, SQLITE_TRANSIENT);    /* Get Version */
    sqlite3_bind_text(stmt, 4, strtok (NULL, "	"), -1, SQLITE_TRANSIENT);    /* Get Stop Number */
    sqlite3_bind_text(stmt, 5, strtok (NULL, "	"), -1, SQLITE_TRANSIENT);    /* Get Vehicle */
    sqlite3_bind_text(stmt, 6, strtok (NULL, "	"), -1, SQLITE_TRANSIENT);    /* Get Date */
    sqlite3_bind_text(stmt, 7, strtok (NULL, "	"), -1, SQLITE_TRANSIENT);    /* Get Time */

    sqlite3_step(stmt);        /* Execute the SQL Statement */
    sqlite3_clear_bindings(stmt);    /* Clear bindings */
    sqlite3_reset(stmt);        /* Reset VDBE */

    n++;
}
fclose (pFile);
 

注意:我们回到使用真正的数据库文件. In-内存数据库很快,但不一定实用

8.94中的进口记录864913 秒

对我们参数绑定中使用的字符串处理代码进行轻微重构,使我们能够每秒执行96,700个插入.我认为可以安全地说这是很快的.当我们开始调整其他变量(即页面大小,索引创建等)时,这将是我们的基准.


摘要(迄今)

我希望你仍然跟我在一起!我们开始这条路的原因是bulkinsert性能与SQLite非常不同,并且并不总是明显需要进行哪些更改来加快我们的操作.使用相同的编译器(和编译器选项),相同的SQLite版本和相同的数据,我们已经优化了我们的代码,并且我们使用SQLite从每秒85插入的worstcase场景转到每秒96,000多插入!


创建INDEX然后INSERT与INSERT然后CREATE INDEX

在我们开始测量SELECT性能之前,我们知道我们将创建索引.下面的一个答案建议在进行批量插入时,在插入数据后创建索引更快(而不是先创建索引然后插入数据).让我们尝试:

创建索引然后插入数据

 sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
...
 

进口记录864913 秒

插入数据然后创建索引

 ...
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
 

13.66年进口记录864913 秒

正如预期的那样,如果一列被索引,bulk-instruct会更慢,但如果在插入数据后创建索引,则确实会有所不同.我们的noindex基线是每秒96,000插入.创建索引然后首先插入数据给我们每秒47,700插入,而先插入数据然后创建索引给我们每秒63,300插入.


我很乐意为其他场景提出建议来尝试...并且很快将为SELECT查询编译类似的数据.

  最佳答案

几个提示:

  1. 在事务中放置插入/更新。
  2. 对于旧版本的SQLite – 考虑一个较少偏执的日志模式(pragma journal_mode).有NORMAL,然后有OFF,如果您不太担心如果操作系统崩溃可能会损坏数据库,则可以显著提高插入速度.如果您的应用程序崩溃,数据应该很好.请注意,在较新版本中,OFF/MEMORY设置对于应用程序级崩溃不安全.
  3. 使用页面大小也有所不同(PRAGMA page_size).页面大小更大可以使读写更快,因为更大的页面保存在内存中.请注意,您的数据库将使用更多的内存.
  4. 如果您有索引,在完成所有插入后考虑调用 CREATE INDEX。这比创建索引然后执行插入要快得多。
  5. 如果您同时访问 SQLite,您必须非常小心,因为在写入完成时,整个数据库被锁定,尽管可能有多个读者,写入将被锁定.在较新的 SQLite 版本中添加 WAL 已经有所改进。
  6. 利用保存空间...较小的数据库更快.例如,如果有键值对,请尽可能使键成为INTEGER PRIMARY KEY,这将替换表中隐含的唯一行号列.
  7. 如果您使用多个线程,您可以尝试使用 共享页面缓存 ,这将允许加载页面在线程之间共享,这可以避免昂贵的 I/O 调用。
  8. 不要使用!feof(file)!

我也问过类似的问题here here .

  相同标签的其他问题

cperformancesqliteoptimization