Sqlite3插入速度比较(sqlite3_exec/sqlite3_prepare)

本文探讨了SQLite在大数据量缓存重传中的应用,详细介绍了不同插入方式的性能差异,包括慢插入、事务插入、同步关闭模式以及执行前准备。通过测试,展示了开启事务、关闭同步写和使用预编译语句如何显著提升插入速度。实验结果显示,事务配合预编译语句在保持数据安全的同时,能大幅提高插入效率。
摘要由CSDN通过智能技术生成

最近做到的项目涉及一个大数据量缓存重传,其中要用到的sqlite技术,把自己的学习心得整理了一下。

SQLite,是一款轻型的数据库,是遵守ACID的关系型数据库管理系统,它包含在一个相对小的C库中。同时能够跟很多程序语言相结合,比如 Tcl、C#、PHP、Java等,还有ODBC接口,同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲,它的处理速度比他们都快。SQLite数据库由于其简单、灵活、轻量、开源,已经被越来越多的被应用到中小型应用中。因此在许多软件中例如(QQ,微信)等许多软件中都有广泛应用。
在这里插入图片描述
在这里插入图片描述

sqlite应用蛮广泛的,小到app应用,大到服务器缓存。不同的插入方法有不同的优劣,在实际开发过程中,不要一味的追求快,而忽视了安全性。下面我就介绍几种我在这段学习过程中所了解的插入方式。

慢插入-暴力插入

在这里插入图片描述

调用sqlite3_exec()函数,会隐式地开启了一个事务,其次,sqlite3_exec() 是sqlite3_perpare(),sqlite3_step(), sqlite3_finalize()的一个结合,每调用一次这个函数,就会重复的执行这三条语句,对于相同的语句,其中sqlite3_perpare相当于编译sql语句,如果语句相同且重复操作,就会增加很多重复操作。如果插入一条数据,就调该函数一次,事务就会被反复地开启、关闭,会增大IO量。所以当大批量数据插入时,此方法简直无法忍受。

事务插入-显示的开启事务
在这里插入图片描述
在这里插入图片描述

所谓”事务“就是指一组SQL命令,这些命令要么一起执行,要么都不被执行。如果在插入数据前显式开启事务,插入后再一起提交,

则会大大提高IO效率,进而加数据快插入速度

同步关闭模式-synchronous = OFF

在这里插入图片描述

当synchronous设置为FULL, SQLite数据库引擎在紧急时刻会暂停以确定数据已经写入磁盘。这使系统崩溃或电源出问题时能确保数据库在重起后不会损坏。FULL synchronous很安全但很慢。

当synchronous设置为NORMAL, SQLite数据库引擎在大部分紧急时刻会暂停,但不像FULL模式下那么频繁。 NORMAL模式下有很小的几率(但不是不存在)发生电源故障导致数据库损坏的情况。但实际上,在这种情况 下很可能你的硬盘已经不能使用,或者发生了其他的不可恢复的硬件错误。

当设置为synchronous OFF时,SQLite在传递数据给系统以后直接继续而不暂停。若运行SQLite的应用程序崩溃, 数据不会损伤,但在系统崩溃或写入数据时意外断电的情况下数据库可能会损坏。另一方面,在synchronous OFF时 一些操作可能会快50倍甚至更多。在SQLite 2中,缺省值为NORMAL.而在3中修改为FULL。

执行前准备-sqlite3_prepare_v2

在这里插入图片描述

此方法就是“执行准备”(类似于存储过程)操作,即先将SQL语句编译好,然后再一步一步(或一行一行)地执行。如果采用前者的话,就算开起了事务,SQLite仍然要对循环中每一句SQL语句进行“词法分析”和“语法分析”,这对于同时插入大量数据的操作来说,简直就是浪费时间。因此,要进一步提高插入效率的话,就应该使用此方法

测试结果展示
在这里插入图片描述

extern "C"
{
    #include "sqlite3.h"
};

#include<sstream>
#include <string>
#include <iostream>
#include <stdlib.h>
#include <ctime>
#include<windows.h>


#define MAX_TEST_COUNT 200

using namespace std;


int main()
{
    char cmdCreatTable[256] = "create table SqliteTest (id integer , x integer , y integer, weight real)" ;
    sqlite3* db = NULL;
    char * errorMessage = NULL;
    int iResult = sqlite3_open("SqliteTest.db", &db);
    do
    {
        if (SQLITE_OK != iResult)
        {
            cout<<"创建InsertTest.db文件失败"<<endl;
            break;
        }

        sqlite3_exec(db,"drop table if exists SqliteTest",0,0,0);

        iResult = sqlite3_exec(db, cmdCreatTable, NULL, NULL, &errorMessage);
        if (SQLITE_OK != iResult)
        {
            cout<<"创建表SqliteTest失败"<<endl;
            break;
        }
        DWORD timeStart;
        DWORD timeStop;
        timeStart = GetTickCount();
        for (int i = 0; i< MAX_TEST_COUNT; ++i)
        {
            stringstream ssm;
            ssm<<"insert into SqliteTest values("<<i<<","<<i*2<<","<<i/2<<","<<i*i<<")";
            iResult = sqlite3_exec(db,ssm.str().c_str(),0,0,0);
        }
        timeStop = GetTickCount();
        cout<< "直接Insert"<<MAX_TEST_COUNT<<"条数据操作执行时间" << timeStart<<"结束时间:"<<timeStop<<"共耗时:"<<timeStop-timeStart<<"ms"<<endl;

        timeStart = GetTickCount();
        sqlite3_exec(db,"PRAGMA synchronous = OFF; ",0,0,0);
        for(int i = MAX_TEST_COUNT; i < MAX_TEST_COUNT*2; ++i)
        {
            stringstream ssm;
            ssm<<"insert into SqliteTest values("<<i<<","<<i*2<<","<<i/2<<","<<i*i<<")";
            sqlite3_exec(db,ssm.str().c_str(),0,0,0);
        }
        timeStop = GetTickCount();

        cout<< "同步写关闭+直接Insert"<<MAX_TEST_COUNT<<"条数据操作执行时间" << timeStart<<"结束时间:"<<timeStop<<"共耗时:"<<timeStop-timeStart<<"ms"<<endl;


        timeStart = GetTickCount();
        sqlite3_exec(db,"PRAGMA synchronous = FULL; ",0,0,0);
        sqlite3_exec(db,"begin;",0,0,0);
        for(int i= MAX_TEST_COUNT*2; i< MAX_TEST_COUNT*3; ++i)
        {
            stringstream ssm;
            ssm<<"insert into SqliteTest values("<<i<<","<<i*2<<","<<i/2<<","<<i*i<<")";
            sqlite3_exec(db,ssm.str().c_str(),0,0,0);
        }
        sqlite3_exec(db,"commit;",0,0,0);
        timeStop = GetTickCount();
        cout<< "事务Insert"<<MAX_TEST_COUNT<<"条数据操作执行时间"<< timeStart<<"结束时间:"<<timeStop<<"共耗时:"<<timeStop-timeStart<<"ms"<<endl;


        timeStart = GetTickCount();
        sqlite3_exec(db,"PRAGMA synchronous = OFF; ",0,0,0);
        sqlite3_exec(db,"begin;",0,0,0);
        for(int i = MAX_TEST_COUNT*3; i < MAX_TEST_COUNT*4; ++i)
        {
            stringstream ssm;
            ssm<<"insert into SqliteTest values("<<i<<","<<i*2<<","<<i/2<<","<<i*i<<")";
            sqlite3_exec(db,ssm.str().c_str(),0,0,0);
        }
        sqlite3_exec(db,"commit;",0,0,0);
        timeStop = GetTickCount();

        cout<< "事务+同步写关闭Insert"<<MAX_TEST_COUNT<<"条数据操作执行时间" << timeStart<<"结束时间:"<<timeStop<<"共耗时:"<<timeStop-timeStart<<"ms"<<endl;

        timeStart = GetTickCount();
        //sqlite3_exec(db,"PRAGMA synchronous = FULL; ",0,0,0);
        sqlite3_exec(db,"begin;",0,0,0);
        sqlite3_stmt *stmt;
        const char* sql = "insert into SqliteTest values(?,?,?,?)";
        sqlite3_prepare(db,sql,strlen(sql),&stmt,0);
        for(int i = MAX_TEST_COUNT*4; i<MAX_TEST_COUNT*5; ++i)
        {
            sqlite3_reset(stmt);
            sqlite3_bind_int(stmt,1,i);
            sqlite3_bind_int(stmt,2,i*2);
            sqlite3_bind_int(stmt,3,i/2);
            sqlite3_bind_double(stmt,4,i*i);
            sqlite3_step(stmt);
         }
         sqlite3_finalize(stmt);
         sqlite3_exec(db,"commit;",0,0,0);

         timeStop = GetTickCount();
         cout<< "事务+执行准备+同步写关闭Insert"<<MAX_TEST_COUNT<<"条数据操作执行时间:"<< timeStart<<"结束时间:"<<timeStop<<"共耗时:"<<timeStop-timeStart<<"ms"<<endl;


    }while(0);

    cout<<"插入测试结束"<<endl;
    Sleep(2000);
    sqlite3_close(db);
    system("pause");

}
`sqlite3_prepare_v2`和`sqlite3_exec`函数都是SQLite数据库的API函数,但在功能和使用方面有所不同。 `sqlite3_prepare_v2`函数是用于准备SQL语句的,它需要三个参数:一个SQLite数据库连接对象,一个SQL语句字符串和一个指向已编译SQL语句的指针。它将SQL语句编译为字节码,但并不执行该语句,因此它通常与`sqlite3_step`函数结合使用,以逐步执行SQL语句。 例如,以下代码演示了如何使用`sqlite3_prepare_v2`和`sqlite3_step`函数来执行SELECT语句: ``` sqlite3_stmt* stmt; const char* sql = "SELECT * FROM mytable"; sqlite3_prepare_v2(db, sql, -1, &stmt, NULL); while (sqlite3_step(stmt) == SQLITE_ROW) { // 处理结果集 } sqlite3_finalize(stmt); ``` 相比之下,`sqlite3_exec`函数可以在一次调用中执行一条完整的SQL语句。它需要四个参数:一个SQLite数据库连接对象,一个SQL语句字符串,一个回调函数和一个回调函数的第一个参数。该回调函数将在每次执行SQL语句时调用,以处理结果集或执行其他自定义操作。 例如,以下代码演示了如何使用`sqlite3_exec`函数来执行INSERT语句: ``` const char* sql = "INSERT INTO mytable (id, name) VALUES (1, 'John')"; sqlite3_exec(db, sql, NULL, NULL, NULL); ``` 总的来说,`sqlite3_prepare_v2`函数比`sqlite3_exec`函数更灵活,因为它允许逐步执行SQL语句,并提供更多的控制和错误处理机制。但是,如果您只需要执行一次简单的SQL语句,则`sqlite3_exec`函数可能更方便。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值