sqlite3测试打开关闭数据库的耗时

女爷i 2022-06-07 08:48 215阅读 0赞

#include <iostream>
    #include <string>
    #include <vector>
    #include <list>
    #include <cassert>
    
    #include "sqlite3/sqlite3.h"
    #include "utility/common.h"
    
    // 测试数据库的打开和关闭对数据库性能的影响。
    
    // 测试方法：
    // 场景(A): 执行1000条写数据，一次打开数据库，一次关闭数据库。
    // 场景(B): 执行1000条写数据，每次写都进行打开和关闭数据库。
    
    
    static const char* kDatabaseName = "test.db";
    static const char* kTurnOffSynchronous = "PRAGMA synchronous = OFF;";
    static const char* kInsertSql = "INSERT INTO OPEN_CLOSE_COST_TEST(NAME) VALUES('Zhe');";
    static const int kDataCount = 1000;
    
    static bool OpenDB(const char* path, sqlite3** db) {
      assert(db != NULL);
    
      int rc = sqlite3_open(path, db);
      if (rc != SQLITE_OK) {
        std::cout << "Failed to open " << kDatabaseName << std::endl;
        std::cout << "Error msg: " << sqlite3_errmsg(*db) << std::endl;
        return false;
      }
    
      return true;
    }
    
    static void PrepareTable() {
      sqlite3* db = NULL;
    
      if (!OpenDB(kDatabaseName, &db)) {
        return;
      }
    
      const char* kCreateTableSql = "CREATE TABLE IF NOT EXISTS OPEN_CLOSE_COST_TEST( \
                                     ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, \
                                     NAME TEXT NOT NULL)"; 
      char* error_msg = NULL;
    
      int rc = sqlite3_exec(db, kCreateTableSql, NULL, NULL, &error_msg);
      if (rc != SQLITE_OK) {
        std::cout << "Failed to create table!" << std::endl;
        std::cout << "Error msg: " << error_msg << std::endl;
        sqlite3_free(error_msg);
      }
    
      sqlite3_close(db);
    }
    
    static void ClearTable() {
      sqlite3* db = NULL;
      if (!OpenDB(kDatabaseName, &db)) {
        return;
      }
    
      const char* kClearTableSql = "DELETE FROM OPEN_CLOSE_COST_TEST;";
      char* error_msg = NULL;
    
      int rc = sqlite3_exec(db, kClearTableSql, NULL, NULL, &error_msg);
      if (rc != SQLITE_OK) {
        std::cout << "Failed to clear table!" << std::endl;
        std::cout << "Error msg: " << error_msg << std::endl;
        sqlite3_free(error_msg);
      }
    
      sqlite3_close(db);
    }
    
    static void TestOnceOpenClose() {
      utility::Timer timer;
      timer.Start();
    
      sqlite3* db = NULL;
      sqlite3_open(kDatabaseName, &db);
      sqlite3_exec(db, kTurnOffSynchronous, NULL, NULL, NULL);
    
      for (int i = 0; i < kDataCount; ++i) {
        sqlite3_exec(db, kInsertSql, NULL, NULL, NULL);
      }
    
      sqlite3_close(db);
    
      std::cout << "1. 执行1000条写数据，一次打开数据库，一次关闭数据库: " << timer.GetSeconds() << std::endl;
    }
    
    static void TestMultiOpenClose() {
      utility::Timer timer;
      timer.Start();
    
      sqlite3* db = NULL;
    
      for (int i = 0; i < kDataCount; ++i) {
        sqlite3_open(kDatabaseName, &db);
        sqlite3_exec(db, kTurnOffSynchronous, NULL, NULL, NULL);
    
        sqlite3_exec(db, kInsertSql, NULL, NULL, NULL);
        
        sqlite3_close(db);
      }
    
      std::cout << "2. 执行1000条写数据，每次写都进行打开和关闭数据库: " << timer.GetSeconds() << std::endl;
    }
    
    int main() {
      PrepareTable();
      ClearTable();
    
      TestOnceOpenClose();
      TestMultiOpenClose();
    
      return 0;
    }

**结果**

![Center][]

[Center]: /images/20220607/5e7c35879c004f4ea63043d906822895.png