对比sqlite3_exec 和sqlite3_bind 插入100万行数据的速度 with BEGIN TRANSACTION using C++ and SQLite

使用sqlite3_exec 插入100万行数据需要 27 s,而使用sqlite3_bind_double 插入100万行数据只需要3.7 s。

主要是因为采用sqlite3_exec(),相当于每插入一行数据同时用到sqlite3_prepare_v2(), sqlite3_step() 和 sqlite3_finalize(),另外需要把double 强制转换成 string 然后再转换成 const char*,这也需要耗费时间;而如果采用sqlite3_bind_double来加入数据,只要用到sqlite3_prepare_v2(),然后不断地使用sqlite3_step() 和 sqlite3_reset();并且不需要数据类型的转换。

当然,BEGIN TRANSACTION 的功能居功至伟。如果把sqlite3_exec(database,"BEGIN TRANSACTION;",NULL,NULL,&errmsg); 和 sqlite3_exec(database,"COMMIT TRANSACTION;",NULL,NULL,NULL); 这两行注释掉,那么上述两种方法将耗费大量的时间;需要几分钟吧?

关于不同插入方法对插入速度的影响,见http://www.sqlite.org/faq.html#q19 中的“(19) INSERT is really slow - I can only do few dozen INSERTs per second



下面是两种类型的代码:


使用sqlite3_exec 插入100万行数据

#include <iostream>
#include <iostream>
#include "sqlite3.h"
#include <string.h>
#include <stdio.h>
#include <sys/time.h>
#include <boost/lexical_cast.hpp>


using namespace std;
using namespace boost;

int first_row;
sqlite3* database;

// callback function;
int select_callback(void *p_data, int num_fields, char **p_fields, char **p_col_names)
{
    int i;
    int* nof_records = (int*) p_data;
    (*nof_records)++;

    // first_row was defined in <select_stmt> function;
    // if first_row == 1, print the first row
    // and then set first_row = 0 to avoid the subsequent execution for the following rows.
    if (first_row == 1)
    {
        first_row = 0;
        for (i=0; i < num_fields; i++)
        {
//            printf("%20s", p_col_names[i]);
        }
//        printf("\n");
        for (i=0; i< num_fields*10; i++)
        {
//            printf("=");
        }
//        printf("\n");
    }

    for(i=0; i < num_fields; i++)
    {   if (p_fields[i])
        {
//            printf("%20s", p_fields[i]);
        }
        else
        {
//            printf("%20s", " ");
        }
    }

//    printf("\n");
    return 0;
}


// With callback function;
void select_stmt(const char* stmt)
{   char *errmsg;
    int   ret;
    int   nrecs = 0;
    first_row = 1;

    ret = sqlite3_exec(database, stmt, select_callback, &nrecs, &errmsg);

    if(ret!=SQLITE_OK)
    {   printf("Error in select statement %s [%s].\n", stmt, errmsg);
    }
    else
    {   printf("\n   %d records returned.\n", nrecs);
    }
}


//time caculation
long timecacul () {
    struct timeval tv;
    struct timezone tz;
    gettimeofday(&tv,&tz);
    return (tv.tv_sec * 1000 + tv.tv_usec / 1000);
}

int main()
{   long starttime, endtime, resulttime;




    char *errmsg;
    sqlite3_open("./Database.db", &database);

//    sqlite3_exec(database, "PRAGMA synchronous = OFF", 0, 0, &errmsg);


    sqlite3_stmt* stmt;
    string s = "create table wujie (x decimal(5,2), y decimal(5,2), z decimal(5,2))";
    const char *creatTable = s.c_str();
    cout << "creatTable : " << creatTable << endl;

//    char creatTable[] = "create table wujie (a, b, c)";


    int result = sqlite3_exec ( database,
                                creatTable,  // stmt
                                0,
                                0,
                                &errmsg
                              );
    if ( result != SQLITE_OK )
    {   cout << "\nCould not prepare statement: creatTable: " << result << endl;
        return 1;
    }


    ////////BEGIN TRANSACTION
    starttime=timecacul();
    sqlite3_exec(database,"BEGIN TRANSACTION;",NULL,NULL,&errmsg);


    string insertDataStr;
    double x, y, z;
    double yTimes = 1.222222222;
    int iNum;
    for ( iNum = 1; iNum <= 1000000; iNum++ )
    {   x = 1 * iNum;
        y = yTimes * iNum;
        z = 2 * iNum;
        insertDataStr = "insert into wujie VALUES("
                        + lexical_cast<string>(x) + ","
                        + lexical_cast<string>(y) + ","
                        + lexical_cast<string>(z) + ")";
//        cout << "insertDataStr: " << insertDataStr << endl;
        const char* insertDataChar = insertDataStr.c_str();

        result = sqlite3_exec
                 ( database,
                   insertDataChar,  // stmt
                   0,
                   0,
                   &errmsg
                 );

        if ( result != SQLITE_OK )
        {   cout << "\nCould not prepare statement: inserData: " << result << endl;
            return 1;
        }
    }

    sqlite3_exec(database,"COMMIT TRANSACTION;",NULL,NULL,NULL);
    endtime=timecacul();
    resulttime=endtime-starttime;

    printf("NO AUTOCOMMIT INSERT:%dms.",resulttime);
    cout << endl;


    char selectData[] = "Select x,y,z from wujie";
    starttime=timecacul();
    select_stmt(selectData);
    endtime=timecacul();
    resulttime=endtime-starttime;
    printf("Select SQL time:%dms.",resulttime);

    sqlite3_close(database);

    return 0;
}


使用sqlite3_bind_double 插入100万行数据

#include <iostream>
#include <iostream>
#include "sqlite3.h"
#include <string.h>
#include <stdio.h>
#include <sys/time.h>
#include <boost/lexical_cast.hpp>


using namespace std;
using namespace boost;

int first_row;
sqlite3* database;

// callback function;
int select_callback(void *p_data, int num_fields, char **p_fields, char **p_col_names)
{
    int i;
    int* nof_records = (int*) p_data;
    (*nof_records)++;

    // first_row was defined in <select_stmt> function;
    // if first_row == 1, print the first row
    // and then set first_row = 0 to avoid the subsequent execution for the following rows.
    if (first_row == 1)
    {
        first_row = 0;
        for (i=0; i < num_fields; i++)
        {
//            printf("%20s", p_col_names[i]);
        }
        printf("\n");
        for (i=0; i< num_fields*10; i++)
        {
//            printf("=");
        }
//        printf("\n");
    }

    for(i=0; i < num_fields; i++)
    {   if (p_fields[i])
        {
//            printf("%20s", p_fields[i]);
        }
        else
        {
//            printf("%20s", " ");
        }
    }

//    printf("\n");
    return 0;
}

// With callback function;
void select_stmt(const char* stmt)
{   char *errmsg;
    int   ret;
    int   nrecs = 0;
    first_row = 1;
    ret = sqlite3_exec(database, stmt, select_callback, &nrecs, &errmsg);

    if(ret!=SQLITE_OK)
    {   printf("Error in select statement %s [%s].\n", stmt, errmsg);
    }
    else
    {   printf("\n   %d records returned.\n", nrecs);
    }
}


//time caculation
long timecacul () {
    struct timeval tv;
    struct timezone tz;
    gettimeofday(&tv,&tz);
    return (tv.tv_sec * 1000 + tv.tv_usec / 1000);
}


int main()
{   long starttime, endtime, resulttime;

    char *errmsg;
    sqlite3_open("./Database.db", &database);

    sqlite3_stmt* stmt;

    string s = "create table wujie (x , y , z )";
    const char *creatTable = s.c_str();
//    cout << "creatTable : " << creatTable << endl;

    int result = sqlite3_exec ( database,
                                creatTable,  // stmt
                                0,
                                0,
                                &errmsg
                              );
    if ( result != SQLITE_OK )
    {   cout << "\nCould not prepare statement: creatTable: " << result << endl;
        return 1;
    }


    if ( sqlite3_prepare
            ( database,
              "insert into wujie values (:x,:y,:z)",  // stmt
              -1, // If than zero, then stmt is read up to the first nul terminator
              &stmt,
              0  // Pointer to unused portion of stmt
            )
            != SQLITE_OK )
    {   printf("\nCould not prepare statement.");
        return 1;
    }

    int index1, index2, index3;
    index1 =  sqlite3_bind_parameter_index(stmt, ":x");
    index2 =  sqlite3_bind_parameter_index(stmt, ":y");
    index3 =  sqlite3_bind_parameter_index(stmt, ":z");

//    cout << index1 << endl;
//    cout << index2 << endl;
//    cout << index3 << endl;

    printf("\nThe statement has %d wildcards\n", sqlite3_bind_parameter_count(stmt));


    starttime=timecacul();
    sqlite3_exec(database,"BEGIN TRANSACTION;",NULL,NULL,&errmsg);
    double x, y, z;
    double yTimes = 1.222222222;
    int iNum;
    for ( iNum = 1; iNum <= 1000000; iNum++ )
    {   x = 1 * iNum;
        y = yTimes * iNum;
        z = 2 * iNum;

        if (sqlite3_bind_double (stmt,
                                 index1,  // Index of wildcard
                                 x
                                )
                != SQLITE_OK)
        {   printf("\nCould not bind double.\n");
            return 1;
        }

        if (sqlite3_bind_double (stmt,
                                 index2,  // Index of wildcard
                                 y
                                )
                != SQLITE_OK)
        {   printf("\nCould not bind double.\n");
            return 1;
        }

        if (sqlite3_bind_double (stmt,
                                 index3,  // Index of wildcard
                                 z
                                )
                != SQLITE_OK)
        {   printf("\nCould not bind double.\n");
            return 1;
        }

        if (sqlite3_step(stmt) != SQLITE_DONE)
        {   printf("\nCould not step (execute) stmt.\n");
            return 1;
        }
        sqlite3_reset(stmt);

    }

    sqlite3_exec(database,"COMMIT TRANSACTION;",NULL,NULL,NULL);
    endtime=timecacul();
    resulttime=endtime-starttime;
    printf("NO AUTOCOMMIT INSERT:%dms.",resulttime);

    ///////////////////////////////////////////////
    starttime=timecacul();

    char selectData[] = "Select * from wujie";
    select_stmt(selectData);
    sqlite3_close(database);

    endtime=timecacul();
    resulttime=endtime-starttime;
    printf("NO AUTOCOMMIT INSERT:%dms.",resulttime);

    return 0;
}




发布了3 篇原创文章 · 获赞 3 · 访问量 3万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览