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

#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;
}

结果