SQLite批量插入效率_sqlite 批量插入

二、使用事务提升插入效率

使用 BEGIN 命令手动启动事务，COMMIT 命令是用于把事务调用的更改保存到数据库中的事务命令。由此可以得出，只有等到 COMMIT 命令才会执行磁盘IO的读写，事务中执行的多次插入语句，只用打开关闭一次数据库文件，极大的提升了批量插入数据的效率。

插入 100000 条数据测试：

const static char * insert_record_static = "INSERT INTO tbl_test (a, b, c) VALUES (1, 1, 1);";
sqlite3_exec(db, "begin;", 0, 0, 0);
t_start = clock();
for(int i = 0; i < 100000; i++)
    sqlite3_exec(db, insert_record_static, 0, 0, &err);
sqlite3_exec(db, "commit;", 0, 0, 0);
t_stop = clock();
std::cout << "Insert static 100000 cost time " << (t_stop - t_start) << " ms" << std::endl;

结果输出:

Insert static 1000 cost time 1588 ms

三、使用预编译执行提升插入效率

To execute an SQL statement, it must first be compiled into a byte-code program using one of these routines. Or, in other words, these routines are constructors for the prepared statement object.

要执行SQL语句，必须首先使用这些例程之一将其编译成字节码程序。换句话说，这些例程是预备语句对象的构造函数。

实际sqlite每次在执行语句时，都会对语句进行编译，查看源码可以看到sqlite3_exec函数中都会调用sqlite3_prepare_v2执行编译，因此直接采用预编译的方式代替语句的直接执行也能够提升效率。

插入 100000 条数据测试：

sqlite3_exec(db, "begin;", 0, 0, 0);
t_start = clock();
sqlite3_prepare_v2(db, insert_record_bind, strlen(insert_record_bind), &stmt, 0);
for(int i = 0; i < 100000; i++)
    sqlite3_bind_int(stmt, 1, i);
    sqlite3_bind_int(stmt, 2, i * 2);
    sqlite3_bind_int(stmt, 3, i * 3);
    sqlite3_step(stmt);
    sqlite3_reset(stmt);
sqlite3_exec(db, "commit;", 0, 0, 0);
t_stop = clock();
std::cout << "Insert bind 100000 cost time " << (t_stop - t_start) << " ms" << std::endl;

结果输出:

Insert bind 100000 cost time 411 ms

四、关闭写同步（synchronous）

个人建议这个优化项可以忽略，速度的提高带来的结果是安全性的降低。

synchronous的设置有四种：

sqlite3_exec(db, "PRAGMA synchronous = EXTRA;", 0, 0, 0);   // (3)
sqlite3_exec(db, "PRAGMA synchronous = FULL;", 0, 0, 0);    // (2)
sqlite3_exec(db, "PRAGMA synchronous = NORMAL;", 0, 0, 0);  // (1)
sqlite3_exec(db, "PRAGMA synchronous = OFF;", 0, 0, 0);     // (0)

EXTRA (3)

EXTRA synchronous is like FULL with the addition that the directory containing a rollback journal is synced after that journal is unlinked to commit a transaction in DELETE mode. EXTRA provides additional durability if the commit is followed closely by a power loss.

EXTRA同步类似于FULL，只不过在日志被解除链接后，包含回滚日志的目录将被同步，以以DELETE模式提交事务。如果提交之后紧接着出现功耗损失，则EXTRA提供了额外的持久性。

FULL (2)

When synchronous is FULL (2), the SQLite database engine will use the xSync method of the VFS to ensure that all content is safely written to the disk surface prior to continuing. This ensures that an operating system crash or power failure will not corrupt the database. FULL synchronous is very safe, but it is also slower. FULL is the most commonly used synchronous setting when not in WAL mode .

当synchronous为FULL(2)时，SQLite数据库引擎将使用VFS的xSync方法来确保在继续之前将所有内容安全地写入磁盘表面。这可以确保操作系统崩溃或电源故障不会破坏数据库。全同步非常安全，但也比较慢。 FULL是不使用WAL模式时最常用的同步设置。

NORMAL (1)

When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in journal_mode =DELETE on an older filesystem. WAL mode is safe from corruption with synchronous=NORMAL, and probably DELETE mode is safe too on modern filesystems. WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash. Transactions are durable across application crashes regardless of the synchronous setting or journal mode. The synchronous=NORMAL setting is a good choice for most applications running in WAL mode

当同步模式为NORMAL(1)时，SQLite数据库引擎仍然会在最关键的时刻进行同步，但频率比FULL模式低。在一个较旧的文件系统上，在错误的时间出现电源故障可能会破坏journal_mode=DELETE中的数据库，这种可能性很小(尽管不为零)。 WAL模式在使用synchronous=NORMAL时是安全的，DELETE模式在现代文件系统上可能也是安全的。 WAL模式总是与synchronous=NORMAL一致，但是WAL模式会失去耐久性。使用synchronous=NORMAL的WAL模式提交的事务可能会在断电或系统崩溃后回滚。事务在应用程序崩溃时是持久的，无论采用同步设置还是日志模式。对于大多数以WAL模式运行的应用程序来说，synchronous=NORMAL设置是一个很好的选择。

OFF (0)

With synchronous OFF (0), SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, commits can be orders of magnitude faster with synchronous OFF.

使用同步OFF (0)， SQLite一旦将数据传递给操作系统，就会继续不进行同步。如果运行SQLite的应用程序崩溃，数据将是安全的，但如果操作系统崩溃或计算机在数据被写到磁盘表面之前失去电源，则数据库可能会损坏。另一方面，使用同步OFF可以使提交快几个数量级。

五、测试代码

// sqlite_example.cpp : Defines the entry point for the console application.
#include "stdafx.h"
#include <time.h>
#include <iostream>
#include "sqlite3/sqlite3.h"
int _tmain(int argc, _TCHAR* argv[])
    int ret = 0;
    char *err = NULL;
    sqlite3 *db = NULL;
    sqlite3_stmt *stmt = NULL;
    clock_t t_start, t_stop;
    // 打开数据库
    sqlite3_open("sqlite_example.db", &db);
    // 关闭写同步
    sqlite3_exec(db, "PRAGMA synchronous = OFF;", 0, 0, 0);
    // 删除表
    sqlite3_exec(db, "DROP TABLE if exists tbl_test;", 0, 0, 0);
    // 创建表
    sqlite3_exec(db, "CREATE TABLE tbl_test (a INTEGER, b INTEGER, c INTEGER);", 0, 0, 0);
    const static char * insert_record_static = "INSERT INTO tbl_test (a, b, c) VALUES (1, 1, 1);";
    const static char * insert_record_bind = "INSERT INTO tbl_test (a, b, c) VALUES (?, ?, ?);";
    // 1、循环插入 1000 条数据
    t_start = clock();
    for(int i = 0; i < 1000; i++)
        sqlite3_exec(db, insert_record_static, 0, 0, &err);
    t_stop = clock();
    std::cout << "Insert static 1000 cost time " << (t_stop - t_start) << " ms" << std::endl;
    t_start = clock();
    sqlite3_prepare_v2(db, insert_record_bind, strlen(insert_record_bind), &stmt, 0);
    for(int i = 0; i < 1000; i++)
        sqlite3_bind_int(stmt, 1, i);
        sqlite3_bind_int(stmt, 2, i * 2);
        sqlite3_bind_int(stmt, 3, i * 3);
        sqlite3_step(stmt);
        sqlite3_reset(stmt);
    t_stop = clock();
    std::cout << "Insert bind 1000 cost time " << (t_stop - t_start) << " ms" << std::endl;
    // 2、使用事务提升插入效率
    sqlite3_exec(db, "begin;", 0, 0, 0);
    t_start = clock();
    for(int i = 0; i < 100000; i++)
        sqlite3_exec(db, insert_record_static, 0, 0, &err);
    sqlite3_exec(db, "commit;", 0, 0, 0);
    t_stop = clock();
    std::cout << "Insert static 100000 cost time " << (t_stop - t_start) << " ms" << std::endl;
    // 3、使用预编译提升插入效率
    sqlite3_exec(db, "begin;", 0, 0, 0);
    t_start = clock();
    sqlite3_prepare_v2(db, insert_record_bind, strlen(insert_record_bind), &stmt, 0);
    for(int i = 0; i < 100000; i++)
        sqlite3_bind_int(stmt, 1, i);
        sqlite3_bind_int(stmt, 2, i * 2);
        sqlite3_bind_int(stmt, 3, i * 3);
        sqlite3_step(stmt);
        sqlite3_reset(stmt);
    sqlite3_exec(db, "commit;", 0, 0, 0);
    t_stop = clock();
    std::cout << "Insert bind 100000 cost time " << (t_stop - t_start) << " ms" << std::endl;
    sqlite3_close(db);
    sqlite3_finalize(stmt);
    return 0;
				Android中在sqlite插入数据的时候默认一条语句是一个事务，因此如果存在上万条数据插入的话，那需要执行上万次插入操作，操作速度可想而知。因此在Android中插入数据时，使用批量插入的方式可以大大提高插入速度。
	　　有时需要把一些数据内置到应用中，常用的有以下2种方式：其一直接拷贝制作好的SQLite数据库文件，其二是使用系统提供的数据库，然后把数据批量插入。我更倾向于使用第二种方式：使用系统创建的数据库，然后批量插入数据。批量插入数据也有很多方法，那么那种方法更快呢，下面通过一个demo比较一下各个方法的插入速度。
	　　1、使用db.execSQL(sql)
	　　这里是把要插入
				<div id="article_content" class="article_content clearfix">
    <link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/kdoc_html_views-1a98987dfd.css">
    <link rel="stylesheet" href="https://csd
				SQLite是我在客户端上使用得最多的数据库，方便易用稳定强大，其支持很多的平台和语言：我分别在Windows
（C++、QT）、Android、IOS上开发过使用SQLite来保存客户端本地数据的应用。下面说说对使用SQLite的一些心得。
1、批量插入。很多人发现使用SQLite插入大量数据的时候非常慢。我有印象刚使用SQLite的时候发现插入2000条数据（10列左右）居然要好几
				需要在sqlite数据库中插入大量测试数据，打算用事务进行循环插入操作，不过sqlite不支持declare变量定义，所以无法通过定义循环变量值来实现。需要通过一个批处理文件来循环调用插入sqlite语句，方式如下 建立一个循环调用的批处理文件sqlite.bat，文件内容  代码如下: @ECHO OFF For /L %%i in (1,1,10000) do (sqlite3.exe test.db<insertdb.bat) pause  这个文件表示循环10000调用insertdb.bat对test.db数据库进行操作。将sqlite语句写在insertdb.bat文件中。  代码
c.execute('''CREATE TABLE IF NOT EXISTS test_table
             (id INT PRIMARY KEY NOT NULL,
              name TEXT NOT NULL,
              age INT NOT NULL);''')
# 批量插入数据
data = [(1, 'Alice', 25), (2, 'Bob', 30), (3, 'Charlie', 35)]
c.executemany('INSERT INTO test_table VALUES (?, ?, ?)', data)
# 提交更改
conn.commit()
# 关闭数据库连接
conn.close()
在上面的代码中，首先使用`sqlite3.connect()`函数连接数据库，然后使用`c.execute()`函数创建数据表。接着，使用`c.executemany()`函数进行批量插入数据，其中的`?`表示占位符，可以通过元组或列表来传递插入的数据。最后，使用`conn.commit()`函数提交更改，关闭数据库连接。