gpt4 book ai didi

c - 提高 SQLite 的每秒插入性能

转载 作者:行者123 更新时间:2023-12-01 16:04:45 24 4
gpt4 key购买 nike

优化 SQLite 很棘手。 C 应用程序的批量插入性能可以从每秒 85 次插入到每秒超过 96,000 次插入不等!
背景:我们使用 SQLite 作为桌面应用程序的一部分。我们将大量配置数据存储在 XML 文件中,这些数据会在应用程序初始化时被解析并加载到 SQLite 数据库中以供进一步处理。 SQLite 非常适合这种情况,因为它速度快,不需要专门的配置,并且数据库作为单个文件存储在磁盘上。
理由:最初我对我所看到的表现感到失望。事实证明,根据数据库的配置方式以及您使用 API 的方式,SQLite 的性能可能会有很大差异(对于批量插入和选择)。弄清楚所有选项和技术是什么并不是一件容易的事,所以我认为创建这个社区 wiki 条目以与 StackOverflow 读者分享结果是明智的,以便为其他人避免相同调查的麻烦。
实验:与其简单地谈论一般意义上的性能技巧(即“使用事务!”),我认为最好编写一些 C 代码并实际衡量各种选项的影响。我们将从一些简单的数据开始:

  • complete transit schedule for the city of Toronto 的 28 MB 制表符分隔文本文件(大约 865,000 条记录)
  • 我的测试机器是运行 Windows XP 的 3.60 GHz P4。
  • 代码是用Visual C++编译的2005 年作为具有“完全优化”(/Ox) 和 Favor Fast Code (/Ot) 的“发布”。
  • 我正在使用 SQLite“合并”,直接编译到我的测试应用程序中。我碰巧拥有的 SQLite 版本有点旧(3.6.7),但我怀疑这些结果将与最新版本相媲美(如果您不这么认为,请发表评论)。

  • 让我们写一些代码吧!
    代码:一个简单的 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] = "\0";

    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] = "\0";

    /*********************************************/
    /* 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, "\t"); /* Get Route */
    sBR = strtok (NULL, "\t"); /* Get Branch */
    sVR = strtok (NULL, "\t"); /* Get Version */
    sST = strtok (NULL, "\t"); /* Get Stop Number */
    sVI = strtok (NULL, "\t"); /* Get Vehicle */
    sDT = strtok (NULL, "\t"); /* Get Date */
    sTM = strtok (NULL, "\t"); /* Get Time */

    /* ACTUAL INSERT WILL GO HERE */

    n++;
    }
    fclose (pFile);

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

    sqlite3_close(db);
    return 0;
    }

    “控制”
    按原样运行代码实际上并不执行任何数据库操作,但它会让我们了解原始 C 文件 I/O 和字符串处理操作的速度。

    Imported 864913 records in 0.94seconds


    伟大的!我们每秒可以执行 920,000 次插入,前提是我们实际上不执行任何插入 :-)

    “最坏情况”
    我们将使用从文件中读取的值生成 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 代码,并且每次插入都将在其自己的事务中发生。有多慢?

    Imported 864913 records in 9933.61seconds


    哎呀! 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);

    Imported 864913 records in 38.03seconds


    这样更好。简单地将我们所有的插入包装在一个事务中,我们的性能提高到 每秒 23,000 次插入。
    使用准备好的语句
    使用事务是一个巨大的改进,但如果我们一遍又一遍地使用相同的 SQL,为每个插入重新编译 SQL 语句没有意义。让我们使用 sqlite3_prepare_v2编译一次我们的 SQL 语句,然后使用 sqlite3_bind_text 将我们的参数绑定(bind)到该语句。 :
    /* 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, "\t"); /* Get Route */
    sBR = strtok (NULL, "\t"); /* Get Branch */
    sVR = strtok (NULL, "\t"); /* Get Version */
    sST = strtok (NULL, "\t"); /* Get Stop Number */
    sVI = strtok (NULL, "\t"); /* Get Vehicle */
    sDT = strtok (NULL, "\t"); /* Get Date */
    sTM = strtok (NULL, "\t"); /* 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", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

    sqlite3_finalize(stmt);
    sqlite3_close(db);

    return 0;

    Imported 864913 records in 16.27seconds


    好的!还有一点代码(不要忘记调用 sqlite3_clear_bindingssqlite3_reset ),但我们的性能提高了一倍多到 每秒 53,000 次插入。
    PRAGMA 同步 = 关闭
    默认情况下,SQLite 将在发出操作系统级写入命令后暂停。这保证了数据被写入磁盘。通过设置 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);

    Imported 864913 records in 12.41seconds


    改进现在更小了,但我们最大 每秒 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);

    Imported 864913 records in 13.50seconds


    比之前的优化在 慢一点每秒 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);

    Imported 864913 records in 12.00seconds


    极好的!我们能够做到 每秒 72,000 次插入。
    使用内存数据库
    只是为了踢球,让我们建立在所有先前的优化基础上并重新定义数据库文件名,以便我们完全在 RAM 中工作:
    #define DATABASE ":memory:"

    Imported 864913 records in 10.94seconds


    将我们的数据库存储在 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, "\t"), -1, SQLITE_TRANSIENT); /* Get Route */
    sqlite3_bind_text(stmt, 2, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Branch */
    sqlite3_bind_text(stmt, 3, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Version */
    sqlite3_bind_text(stmt, 4, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Stop Number */
    sqlite3_bind_text(stmt, 5, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Vehicle */
    sqlite3_bind_text(stmt, 6, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Date */
    sqlite3_bind_text(stmt, 7, strtok (NULL, "\t"), -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);
    注意:我们又回到使用真正的数据库文件了。内存数据库速度很快,但不一定实用

    Imported 864913 records in 8.94seconds


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

    总结(到目前为止)
    我希望你还在我身边!我们开始走这条路的原因是 SQLite 的批量插入性能变化如此之大,并且需要进行哪些更改以加快我们的操作并不总是很明显。使用相同的编译器(和编译器选项)、相同版本的 SQLite 和相同的数据,我们优化了我们的代码和我们对 SQLite 的使用 从每秒 85 次插入的最坏情况到每秒超过 96,000 次插入!

    创建索引然后插入 vs. 插入然后创建索引
    在我们开始测量之前 SELECT性能,我们知道我们将创建索引。在下面的一个答案中建议,在进行批量插入时,插入数据后创建索引会更快(而不是先创建索引然后插入数据)。我们试试看:
    创建索引然后插入数据
    sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
    sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
    ...

    Imported 864913 records in 18.13seconds


    插入数据然后创建索引
    ...
    sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
    sqlite3_exec(db, "CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);

    Imported 864913 records in 13.66seconds


    正如预期的那样,如果对一列进行索引,批量插入会变慢,但如果在插入数据后创建索引,则确实有所不同。我们的无索引基线是每秒 96,000 次插入。 先创建索引,然后插入数据,每秒插入 47,700 次,而先插入数据,然后创建索引,每秒插入 63,300 次。

    我很乐意接受其他场景的建议来尝试......并且很快就会为 SELECT 查询编译类似的数据。

    最佳答案

    几个提示:

  • 将插入/更新放在事务中。
  • 对于旧版本的 SQLite - 考虑一个不那么偏执的日志模式( pragma journal_mode )。有NORMAL ,然后是 OFF ,如果您不太担心操作系统崩溃时数据库可能会损坏,这可以显着提高插入速度。如果您的应用程序崩溃,数据应该没问题。请注意,在较新版本中,OFF/MEMORY设置对于应用程序级别的崩溃是不安全的。
  • 使用页面大小也会有所不同( PRAGMA page_size )。由于较大的页面保存在内存中,因此具有较大的页面大小可以使读取和写入速度更快。请注意,更多内存将用于您的数据库。
  • 如果您有索引,请考虑致电 CREATE INDEX完成所有插入后。这比创建索引然后进行插入要快得多。
  • 如果您同时访问 SQLite,您必须非常小心,因为在写入完成时整个数据库都被锁定,尽管可能有多个读取器,但写入将被锁定。通过在较新的 SQLite 版本中添加 WAL,这一点有所改善。
  • 利用节省空间...较小的数据库运行速度更快。例如,如果您有键值对,请尝试将键设为 INTEGER PRIMARY KEY如果可能,它将替换表中隐含的唯一行号列。
  • 如果您使用多线程,您可以尝试使用 shared page cache ,这将允许在线程之间共享加载的页面,从而避免昂贵的 I/O 调用。
  • Don't use !feof(file) !

  • 我也问过类似的问题 herehere .

    关于c - 提高 SQLite 的每秒插入性能,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/1711631/

    24 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com