对比sqlite3_exec 和sqlite3_bind 插入100万行数据的速度 with B

使用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,&errmsg); 和sqlite3_exec(database,"COMMIT TRANSACTION;",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",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",&errmsg);


    sqlite3_stmt* stmt;
    string s = "create table wujie (x decimal(5,2),y decimal(5,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,&errmsg
                              );
    if ( result != SQLITE_OK )
    {   cout << "nCould not prepare statement: creatTable: " << result << endl;
        return 1;
    }


    ////////BEGIN TRANSACTION
    starttime=timecacul();
    sqlite3_exec(database,&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,&errmsg
                 );

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

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

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


    char selectData[] = "Select x,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,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"," ");
        }
    }

//    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,&tz);
    return (tv.tv_sec * 1000 + tv.tv_usec / 1000);
}


int main()
{   long starttime,resulttime;

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

    sqlite3_stmt* stmt;

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

    int result = sqlite3_exec ( database,&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 wildcardsn",sqlite3_bind_parameter_count(stmt));


    starttime=timecacul();
    sqlite3_exec(database,&errmsg);
    double x,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,// 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,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;
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!