Sqlite3 常见几种数据插入方式性能比较

#include <QCoreApplication>
#include "mysqliteop.h"
#include <QDateTime>
#include <QDebug>
#include <QFile>
#include <QDataStream>
#include <string>
#include <vector>
using namespace std;

#define CHECKZERO(a) if((a)!= SQLITE_OK) throw("error.");

// 无优化
auto f1 = [](){
    sqlite3* db = nullptr;
    const char* path = "./perftest.db";
    CHECKZERO(sqlite3_open(path, &db));
    CHECKZERO(sqlite3_exec(db, "CREATE TABLE IF not exists Test(ID INTEGER,var0 INTEGER,var1 REAL,var2 TEXT);", 0, 0, 0));
    const int maxcount = 100;
    for (int i = 0; i < maxcount; i++) {
        CHECKZERO(sqlite3_exec(db, "INSERT INTO Test (ID,var0,var1,var2) VALUES (0,1,2.0,'\"hello sqlite3.\"');", 0, 0, 0));
    }
    CHECKZERO(sqlite3_close(db));
};

//关闭写同步
auto f2 = [](){
    sqlite3* db = nullptr;
    const char* path = "./perftest.db";
    CHECKZERO(sqlite3_open(path, &db));
    CHECKZERO(sqlite3_exec(db, "PRAGMA synchronous = OFF", 0, 0, 0));
    CHECKZERO(sqlite3_exec(db, "CREATE TABLE IF not exists Test(ID INTEGER,var0 INTEGER,var1 REAL,var2 TEXT);", 0, 0, 0));
    const int maxcount = 10000;
    for (int i = 0; i < maxcount; i++) {
        CHECKZERO(sqlite3_exec(db, "INSERT INTO Test (ID,var0,var1,var2) VALUES (0,1,2.0,'\"hello sqlite3.\"');", 0, 0, 0));
    }
    CHECKZERO(sqlite3_close(db));
};

//开启事务
auto f3 = [](){
    sqlite3* db = nullptr;
    const char* path = "./perftest.db";
    CHECKZERO(sqlite3_open(path, &db));
    CHECKZERO(sqlite3_exec(db, "PRAGMA synchronous = OFF", 0, 0, 0));
    CHECKZERO(sqlite3_exec(db, "CREATE TABLE IF not exists Test(ID INTEGER,var0 INTEGER,var1 REAL,var2 TEXT);", 0, 0, 0));
    CHECKZERO(sqlite3_exec(db, "BEGIN", 0, 0, 0));
    const int maxcount = 1000000;
    for (int i = 0; i < maxcount; i++) {
        CHECKZERO(sqlite3_exec(db, "INSERT INTO Test (ID,var0,var1,var2) VALUES (0,1,2.0,'\"hello sqlite3.\"');", 0, 0, 0));
        if (i % 10000 == 9999) {
            CHECKZERO(sqlite3_exec(db, "COMMIT", 0, 0, 0));
            CHECKZERO(sqlite3_exec(db, "BEGIN", 0, 0, 0));
        }
    }
    CHECKZERO(sqlite3_exec(db, "COMMIT", 0, 0, 0));
    CHECKZERO(sqlite3_close(db));
};


auto f4 = [](){
    //执行准备
    sqlite3* db = nullptr;
    const char* path = "./perftest.db";
    CHECKZERO(sqlite3_open(path, &db));
    CHECKZERO(sqlite3_exec(db, "PRAGMA synchronous = OFF", 0, 0, 0));
    CHECKZERO(sqlite3_exec(db, "CREATE TABLE IF not exists Test(ID INTEGER,var0 INTEGER,var1 REAL,var2 TEXT);", 0, 0, 0));
    // 执行准备
    sqlite3_stmt *pPrepare = nullptr;
    auto sql = "INSERT INTO Test (ID,var0,var1,var2) VALUES (?,?,?,?);";
    CHECKZERO(sqlite3_prepare_v2(db, sql, strlen(sql), &pPrepare, 0));
    CHECKZERO(sqlite3_exec(db, "BEGIN", 0, 0, 0));
    const int maxcount = 1000000;
    for (int i = 0; i < maxcount; i++) {
        CHECKZERO(sqlite3_reset(pPrepare));
        CHECKZERO(sqlite3_bind_int(pPrepare, 1, 0));
        CHECKZERO(sqlite3_bind_int(pPrepare, 2, 1));
        CHECKZERO(sqlite3_bind_double(pPrepare, 3, 2.0));
        const char* str = "hello sqlite3.";
        CHECKZERO(sqlite3_bind_text(pPrepare, 4, str, strlen(str), 0));
        int err = sqlite3_step(pPrepare);
        assert(SQLITE_DONE == err);
        if (i % 10000 == 9999) {
            CHECKZERO(sqlite3_exec(db, "COMMIT", 0, 0, 0));
            CHECKZERO(sqlite3_exec(db, "BEGIN", 0, 0, 0));
        }
    }
    CHECKZERO(sqlite3_exec(db, "COMMIT", 0, 0, 0));
    CHECKZERO(sqlite3_finalize(pPrepare)); // 释放
    CHECKZERO(sqlite3_close(db));
};


auto f5 = [](){
    //内存模式
    sqlite3* db = nullptr;
    const char* path = "./perftest.db";
    CHECKZERO(sqlite3_open(":memory:", &db));
    CHECKZERO(sqlite3_exec(db, "PRAGMA synchronous = OFF", 0, 0, 0));
    CHECKZERO(sqlite3_exec(db, "CREATE TABLE IF not exists Test(ID INTEGER,var0 INTEGER,var1 REAL,var2 TEXT);", 0, 0, 0));
    // 执行准备
    sqlite3_stmt *pPrepare = nullptr;
    auto sql = "INSERT INTO Test (ID,var0,var1,var2) VALUES (?,?,?,?);";
    CHECKZERO(sqlite3_prepare_v2(db, sql, strlen(sql), &pPrepare, 0));
    CHECKZERO(sqlite3_exec(db, "BEGIN", 0, 0, 0));
    const int maxcount = 10000000;
    for (int i = 0; i < maxcount; i++) {
        CHECKZERO(sqlite3_reset(pPrepare));
        CHECKZERO(sqlite3_bind_int(pPrepare, 1, 0));
        CHECKZERO(sqlite3_bind_int(pPrepare, 2, 1));
        CHECKZERO(sqlite3_bind_double(pPrepare, 3, 2.0));
        const char* str = "hello sqlite3.";
        CHECKZERO(sqlite3_bind_text(pPrepare, 4, str, strlen(str), 0));

        int err = sqlite3_step(pPrepare);
        assert(SQLITE_DONE == err);
        if (i % 10000 == 9999) {
            CHECKZERO(sqlite3_exec(db, "COMMIT", 0, 0, 0));
            CHECKZERO(sqlite3_exec(db, "BEGIN", 0, 0, 0));
        }
    }
    CHECKZERO(sqlite3_exec(db, "COMMIT", 0, 0, 0));
    CHECKZERO(sqlite3_finalize(pPrepare)); // 释放
    // 导出
    CHECKZERO(sqlite3_exec(db, "VACUUM INTO 'out.db3';", 0, 0, 0));
    CHECKZERO(sqlite3_close(db));
};


auto f6 = [](){
    //执行准备
    sqlite3* db = nullptr;
    const char* path = "./perftest.db";
    CHECKZERO(sqlite3_open(path, &db));
    CHECKZERO(sqlite3_exec(db, "PRAGMA synchronous = OFF", 0, 0, 0));
    CHECKZERO(sqlite3_exec(db, "CREATE TABLE IF not exists Test(ID INTEGER,var0 INTEGER,var1 REAL,var2 BLOB);", 0, 0, 0));

    // 执行准备
    sqlite3_stmt *pPrepare = nullptr;
    auto sql = "INSERT INTO Test (ID,var0,var1,var2) VALUES (?,?,?,?);";
    CHECKZERO(sqlite3_prepare_v2(db, sql, strlen(sql), &pPrepare, 0));
    CHECKZERO(sqlite3_exec(db, "BEGIN", 0, 0, 0));

    CHECKZERO(sqlite3_reset(pPrepare));
    CHECKZERO(sqlite3_bind_int(pPrepare, 1, 0));
    CHECKZERO(sqlite3_bind_int(pPrepare, 2, 1));
    CHECKZERO(sqlite3_bind_double(pPrepare, 3, 2.0));

    QFile file("./stitching_gray.bmp");
    if(!file.open(QIODevice::ReadOnly)){return -1;}
    QByteArray bytes = file.readAll();
    file.close();

    QDateTime tb = QDateTime::currentDateTime();
    CHECKZERO(sqlite3_bind_blob(pPrepare, 4, bytes, bytes.length(), SQLITE_STATIC));
    int err = sqlite3_step(pPrepare);
    assert(SQLITE_DONE == err);

    CHECKZERO(sqlite3_exec(db, "COMMIT", 0, 0, 0));
    CHECKZERO(sqlite3_finalize(pPrepare)); // 释放

    QDateTime te = QDateTime::currentDateTime();
    qDebug() << "Just insert :" << tb.toString() << " <==> " << te.toString() << (te.toMSecsSinceEpoch()  - tb.toMSecsSinceEpoch()) << "ms";

    CHECKZERO(sqlite3_close(db));
};



auto f_query = [](){
    // query using one bind parameter

    sqlite3_stmt * pStmt = nullptr;
    sqlite3* pDB = nullptr;
    const char* path = "./perftest.db";
    CHECKZERO(sqlite3_open(path, &pDB));

    // CHECKZERO(sqlite3_exec(pDB, "PRAGMA synchronous = OFF", 0, 0, 0));

    std::string str = "SELECT var2 FROM Test WHERE ID=?1";
    int iReturn = sqlite3_prepare_v2(pDB, str.c_str(), str.size() + 1, &pStmt, nullptr);
    if (iReturn != SQLITE_OK) {
        return 1;
    }

    printf("The statement %s has %d parameters(s).\n", str.c_str(), sqlite3_bind_parameter_count(pStmt));

    // fourth parameter is length = position of \0
    iReturn = sqlite3_bind_int(pStmt, 1, 0);
    if (iReturn != SQLITE_OK) {
        return 1;
    }

    std::vector<std::string> vecResults;
    char cBuffer[1024*100]={0};
    string strBuffer;
    while (sqlite3_step(pStmt) == SQLITE_ROW)
    {
        sprintf(cBuffer, "%s", sqlite3_column_blob(pStmt, 0));
        printf("%s\n",cBuffer);
        strBuffer = cBuffer;
        vecResults.push_back(strBuffer);
    }

    sqlite3_finalize(pStmt);
    CHECKZERO(sqlite3_close(pDB));

    printf("Found %d results.\n", vecResults.size());
    for (unsigned int i = 0, iEnd = vecResults.size(); i != iEnd; ++i)
    {
        printf("%d: %s\n", i, vecResults[i].c_str());
    }

} ;



/// test_insert
auto f_insert_test = [](){
    // 执行准备
    sqlite3* db = nullptr;
    const char* path = "./perftest.db";
    CHECKZERO(sqlite3_open(path, &db));
    CHECKZERO(sqlite3_exec(db, "PRAGMA synchronous = OFF", 0, 0, 0));
    CHECKZERO(sqlite3_exec(db, "CREATE TABLE IF not exists Test(ID INTEGER,var0 INTEGER,var1 REAL,var2 BLOB);", 0, 0, 0));

    // 执行准备
    sqlite3_stmt *pPrepare = nullptr;
    auto sql = "INSERT INTO Test (ID,var0,var1,var2) VALUES (?,?,?,?);";
    CHECKZERO(sqlite3_prepare_v2(db, sql, strlen(sql), &pPrepare, 0));
    CHECKZERO(sqlite3_exec(db, "BEGIN", 0, 0, 0));

    QFile file("./stitching_gray.bmp");
    if(!file.open(QIODevice::ReadOnly))return -1;
    QByteArray bytes = file.readAll();
    file.close();

    CHECKZERO(sqlite3_reset(pPrepare));
    CHECKZERO(sqlite3_bind_int(pPrepare, 1, 0));
    CHECKZERO(sqlite3_bind_int(pPrepare, 2, 1));
    CHECKZERO(sqlite3_bind_zeroblob(pPrepare, 3,  file.size() /*bytes.length()*/));

    int err = sqlite3_step(pPrepare);
    assert(SQLITE_DONE == err);
    CHECKZERO(sqlite3_exec(db, "COMMIT", 0, 0, 0));
    CHECKZERO(sqlite3_finalize(pPrepare)); // 释放

    QDateTime tb = QDateTime::currentDateTime();
    sqlite3_blob* blob = NULL;
    sqlite3_blob_open(db,path,"Test","var2",1,1,&blob);
    sqlite3_blob_write(blob,bytes,bytes.length(),0);

    QDateTime te = QDateTime::currentDateTime();
    qDebug() << "Just insert :" << tb.toString() << " <==> " << te.toString() << (te.toMSecsSinceEpoch()  - tb.toMSecsSinceEpoch()) << "ms";

    CHECKZERO(sqlite3_blob_close(blob));
    CHECKZERO(sqlite3_close(db));
};


auto f_preftest = [](std::function<void()> f){
    QDateTime tb = QDateTime::currentDateTime();
    f();
    QDateTime te = QDateTime::currentDateTime();

    qDebug() << tb.toString() << " <==> " << te.toString() << (te.toMSecsSinceEpoch()  - tb.toMSecsSinceEpoch()) << "ms";
};


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

    /// 性能測試
    /// 無優化
    //f_preftest(f1);  // "周三 12月 7 09:59:14 2022"  <==>  "周三 12月 7 09:59:26 2022" 11442 ms（百）

    /// 關閉讀寫同步
    //f_preftest(f2);  // "周三 12月 7 10:00:32 2022"  <==>    "周三 12月 7 10:00:43 2022" 11262 ms（萬）

    /// 開啟事務
    // f_preftest(f3); //"周三 12月 7 10:03:08 2022"  <==>  "周三 12月 7 10:03:12 2022" 3209 ms（百萬）

    /// 關閉讀寫同步 + 開啟事務
    // f_preftest(f4); // "周三 12月 7 10:08:32 2022"  <==>  "周三 12月 7 10:08:40 2022" 8260 ms (千萬) （734 ms 百萬）

    /// 內存模式
     //f_preftest(f5);  // 7110ms (千万)

    /// 大數據寫入
    f_preftest(f6); // 105 ms

    /// 大數據讀
    // f_preftest(f_query); // 10ms

    //f_preftest(f_insert_test);
}

总结： 内存模式最快，千万条在7110ms，每条不到7ms；其次是开始事务并关闭读写同步模式；
对于秒级百万条数据插入来说毫无压力。后面我会对sqlite3的源码进行由浅到深的剖析。