改进INSERT

2018-05-29 02:04:45

优化SQLite是非常棘手的。 C应用程序的批量插入性能可以从每秒85次插入到每秒超过96,000次插入！

背景：我们使用SQLite作为桌面应用程序的一部分。我们有大量的配置数据存储在XML文件中，这些数据被解析并加载到SQLite数据库中，以便在初始化应用程序时进一步处理。 SQLite非常适合这种情况，因为它速度很快，不需要专门的配置，数据库作为单个文件存储在磁盘上。

理由：最初我对我所看到的表现感到失望。事实证明，根据数据库的配置方式以及如何使用API，SQLite的性能可能会有很大差异（对于批量插入和选择）。找出所有的选项和技术并不是一件简单的事情，所以我认为创建这个社区wiki条目与Stack Overflow读者分享结果是很明智的，以便为其他人省去相同调查的麻烦。

实验：我认为最好是编写一些C代码并实际测量各种选项的影响，而不是简单地谈论一般意义上的性能提示（即“使用事务！”）。我们将从一些简单的数据开始：

多伦多市全部交通时间表的28 MB TAB分隔文本文件（约865,000条记录）

我的测试机是运行Windows XP的3.60 GHz P4。

代码使用Visual C ++ 2005作为“发布”与“完全优化”（/ Ox）和优惠快速代码（/ Ot）进行编译。

我使用SQLite“Amalgamation”，直接编译到我的测试应用程序中。我碰巧遇到的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] = "";

    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, "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 secondsn", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

    sqlite3_close(db);
    return 0;
}

“控制”

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

在0.94秒内导入864913条记录

大！如果我们实际上没有做任何插入，我们可以每秒做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代码，并且每个插入都会发生在它自己的事务中。有多慢？

在9933.61秒内导入864913条记录

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

在38.03秒内导入864913条记录

这样更好。简单地将所有插入内容包装在单个事务中，将我们的性能提高到每秒23,000次插入。

使用预先准备好的声明

使用事务是一个巨大的改进，但是如果我们使用相同的SQL over-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, "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 secondsn", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

sqlite3_finalize(stmt);
sqlite3_close(db);

return 0;

在16.27秒内导入864913条记录

太好了！还有一点点的代码（不要忘记调用sqlite3_clear_bindings和sqlite3_reset ），但是我们的性能已经提高了一倍多，达到每秒53,000次插入。

PRAGMA同步=关闭

默认情况下，SQLite会在发出OS级写入命令后暂停。这保证了数据被写入磁盘。通过设置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);

13.50秒内导入864913条记录

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

PRAGMA synchronous = 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);

在12.00秒内导入864913条记录

太棒了！我们能够每秒完成72,000次插入。

使用内存数据库

只是为了踢，让我们建立在以前的所有优化之上，并重新定义数据库文件名，以便我们完全使用RAM：

#define DATABASE ":memory:"

在10.94秒内导入864913条记录

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

重构C代码

虽然不是特别的SQLite改进，但我不喜欢while循环中额外的char*赋值操作。让我们快速重构该代码以将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);

注意：我们又回到了使用真正的数据库文件。 内存数据库很快，但不一定实用

在8.94秒内导入864913条记录

对参数绑定中使用的字符串处理代码进行轻微重构使我们能够每秒执行96,700次插入。 我认为可以肯定地说这个速度很快。随着我们开始调整其他变量（例如页面大小，索引创建等），这将成为我们的基准。

总结（迄今）

我希望你仍然和我在一起！我们开始走这条路的原因是，批量插入的性能与SQLite差异很大，并且通常不需要做出什么样的改变来加速我们的操作。使用相同的编译器（和编译器选项），相同版本的SQLite和相同的数据，我们已经优化了我们的代码和SQLite的使用， 从每秒85次插入到每秒超过96,000次插入的最坏情况！

CREATE 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);
...

在18.13秒内导入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条记录

正如预期的那样，如果一列被编入索引，批量插入会变慢，但是如果在插入数据之后创建索引，批量插入会有所帮助。我们的无索引基线是每秒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如果可能），这将替换表中隐含的唯一行号列。

如果您使用多个线程，则可以尝试使用共享页面缓存，这将允许在线程之间共享加载的页面，这可以避免昂贵的I / O调用。

不要使用!feof(file) ！

我也在这里和这里问过类似的问题。

对于这些插入尝试使用SQLITE_STATIC而不是SQLITE_TRANSIENT 。

SQLITE_TRANSIENT将导致SQLite在返回之前复制字符串数据。

SQLITE_STATIC告诉它你的内存地址是有效的，直到查询完成（在这个循环中总是这样）。这将为您节省几次每个循环的分配，复制和释放操作。可能是一个很大的改进。

避免sqlite3_clear_bindings（stmt）;

测试中的代码每次都会设置绑定，通过这些绑定应该足够了。

来自SQLite文档的C API介绍说

在第一次或在sqlite3_reset（）之后立即调用sqlite3_step（）之前，应用程序可调用其中一个sqlite3_bind（）接口以将值附加到参数。每次调用sqlite3_bind（）都会覆盖同一个参数上的先前绑定

（请参阅：sqlite.org/cintro.html）。文档中没有关于该功能的说法，除了简单地设置绑定外，还必须调用它。

更多详细信息：http://www.hoogli.com/blogs/micro/index.html#Avoid_sqlite3_clear_bindings（）

链接地址: http://www.djcxy.com/p/441.html

上一篇: Improve INSERT

下一篇: How do I copy to the clipboard in JavaScript?