最近项目中使用到了Sqlite3来保存结果数据,大约100万条数据,插入到sqlite数据库中竟然耗时5分钟,这在真个数据处理流程中占用了太多的时间,是不可接受的,那么如何优化sqlite的写数据的性能呢?
通过查阅资料和其他大牛们的博客,确定有几个点可以尝试:
使用上面提到的方法,测试下来速度对比如下所示:
优化方法 | 无优化 | 关闭写同步 | 开启事务 | 执行准备 | 内存模式 |
---|---|---|---|---|---|
每秒插入 | 13条 | 1321条 | 5万条 | 213万条 | 215万条 |
错误检查宏定义:
#define CHECKZERO(a) if((a)!=0) throw("error.");
sqlite3* db = nullptr; CHECKZERO(sqlite3_open(path, &db)); CHECKZERO(sqlite3_exec(db, "CREATE TABLE 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));
sqlite3* db = nullptr; CHECKZERO(sqlite3_open(path, &db)); CHECKZERO(sqlite3_exec(db, "PRAGMA synchronous = OFF", 0, 0, 0)); CHECKZERO(sqlite3_exec(db, "CREATE TABLE 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));
sqlite3* db = nullptr; CHECKZERO(sqlite3_open(path, &db)); CHECKZERO(sqlite3_exec(db, "PRAGMA synchronous = OFF", 0, 0, 0)); CHECKZERO(sqlite3_exec(db, "CREATE TABLE 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));
sqlite3* db = nullptr; CHECKZERO(sqlite3_open(path, &db)); CHECKZERO(sqlite3_exec(db, "PRAGMA synchronous = OFF", 0, 0, 0)); CHECKZERO(sqlite3_exec(db, "CREATE TABLE 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_close(db));
sqlite3* db = nullptr; CHECKZERO(sqlite3_open(":memory:", &db)); CHECKZERO(sqlite3_exec(db, "PRAGMA synchronous = OFF", 0, 0, 0)); CHECKZERO(sqlite3_exec(db, "CREATE TABLE 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));
sqlite3作为如此强大轻量级的数据库引擎,插入速度必然不会很慢,如果自己使用过程中发现效率问题,那一定是自己没有找到合适的用法,在最终的测试结果中,sqlite3的写入速度达到惊人的200万条每秒。
完整的测试工程代码在此处下载:sqlite3性能优化源代码数据插入开启事务执行准备性能提升每秒百万条数据写入-其它文档类资源-CSDN文库