プログラミング C++ 関連の技術メモ【SQLite の活用方法（その5）】

SQLite 技術メモ　：プログラム実践（何とか早くならんのか）

前回からの続きです。あまりのInsertの遅さに驚いていましたが、いろいろ調べてみて、1回のInsertに複数行をまとめてつめこめば早くなるという噂を聞きつけて、試してみます。

とりあえず、1000行単位でInsertをぶち込んでみましょう。

ソースコード

#include <iostream>
#include <sqlite3.h>
#include <fstream>
#include <string>
#include <sstream>
#include <vector>
#include <stdio.h>
#include <locale>
#include <codecvt>
#include <cstdio>
#include <chrono>
#include <Windows.h>

#define MY_TABLE_NAME "test_table"
#define MY_DB_NAME "./test.db"
#define CSV_DELIMITER L','
const std::string SQL_CREATE_TABLE = "CREATE TABLE ";
const std::string SQL_INSERT = "INSERT INTO ";

class csv_data {
public:
std::vector<std::string> titleData;
std::vector<std::vector<std::string>> tableData;
public:
csv_data(std::wstring& filename)
{
setlocale(LC_CTYPE, "ja_JP.UTF-8");
FILE* fpr = nullptr;
wchar_t tmp[1024];
std::wstring lineTitle;
_wfopen_s(&fpr, filename.c_str(), L"r, ccs=UTF-8");
fgetws(tmp, 1024, fpr);
lineTitle = tmp;
std::string cvtTitleData = WStringToString(lineTitle);
std::vector<std::string> strvec = split(cvtTitleData);
for (auto title : strvec)
{
printf("TITLE = %s\n", title.c_str());
titleData.push_back(title);
}

while (fgetws(tmp, 1024, fpr) != NULL)
{
std::vector<std::string> rowData;
lineTitle = tmp;
std::string cvtStringData = WStringToString(lineTitle);
std::vector<std::string> strvec = split(cvtStringData);
for (auto title : strvec)
{
rowData.push_back(title);
}
tableData.push_back(rowData);
}
fclose(fpr);;
}
~csv_data() {}

private:
std::vector<std::string> split(std::string &originalStr)
{
int topOfStr = 0;
int endOfStr = 0;
std::vector<std::string> result;

do {
endOfStr = (int)originalStr.find_first_of(CSV_DELIMITER, topOfStr);
if (endOfStr == std::string::npos)
{
endOfStr = (int)originalStr.size();
}
std::string subStr(originalStr, topOfStr, (endOfStr - topOfStr));
result.push_back(subStr);
topOfStr = endOfStr + 1;
} while (topOfStr < originalStr.size());
return result;
}
std::string WStringToString(std::wstring &oWString)
{
setlocale(LC_ALL, "Japanese");
int bufferSize = WideCharToMultiByte(CP_ACP, 0, oWString.c_str(),
-1, (char *)NULL, 0, NULL, NULL);
std::unique_ptr<CHAR> cpMultiByte = std::make_unique<CHAR>(bufferSize);
WideCharToMultiByte(CP_ACP, 0, oWString.c_str(), -1, cpMultiByte.get(),
bufferSize, NULL, NULL);
std::string result(cpMultiByte.get(), cpMultiByte.get() + bufferSize - 1);
return(result);
}
};

int main()
{
std::chrono::system_clock::time_point start, end;
start = std::chrono::system_clock::now(); // 計始時間

std::wstring fileName(L"humanFlow_zenkoku.csv");
csv_data csvData(fileName);
end = std::chrono::system_clock::now();
double elapsed = (double)std::chrono::duration_cast<std::chrono::milliseconds>(end - start).count();
std::cout << "Lap Time 01 = " << elapsed << std::endl;

// ２回目以降の実行前にファイルを削除しておく。
std::remove(MY_DB_NAME);
sqlite3 *db = nullptr;

int ret = sqlite3_open(MY_DB_NAME, &db);

if (ret != SQLITE_OK)
{
std::cout << "Failed to open Database File : " << ret << std::endl;
return -1;
}

char *errorMessage = nullptr;
const std::string tableName = MY_TABLE_NAME;

std::string sql_create = SQL_CREATE_TABLE + tableName + "(";
for (int i =0; i < csvData.titleData.size();i++)
{
std::string tmp = csvData.titleData[i];
sql_create += "\"" + tmp + "\"";
sql_create += *1
{
sql_insert += ";";
ret = sqlite3_exec(db, sql_insert.c_str(), NULL, NULL, &errorMessage);
if (ret != SQLITE_OK)
{
std::cout << "Failed to insert data (" << tableName.c_str() << ") : " << ret << std::endl;
std::cout << errorMessage << std::endl;
sqlite3_close(db);
sqlite3_free(errorMessage);
return -1;
}
sql_insert = SQL_INSERT + tableName + " values(";
}
else
{
sql_insert += ", (";
}
}
end = std::chrono::system_clock::now();
elapsed = (double)std::chrono::duration_cast<std::chrono::milliseconds>(end - start).count();
std::cout << "Fin Time = " << elapsed << std::endl;
sqlite3_close(db);
}

実行結果

え・・・・？うん？3783msec？4秒弱！？マジか。8分(480秒くらい）かかっていたのが、4秒弱？100倍速くなってるじゃあないか。え、どういうこと？結局のところ、SQLの実行回数が支配的だということ？
一応、dbファイルを確認して、ちゃんとデータも入っていることを確認。

そして、いやいや、それよりも先に、トランザクションで一気に処理すれば早くなるでしょっていう話もある。

ソースコード

#define MY_TABLE_NAME "test_table"
#define MY_DB_NAME "./test.db"
#define CSV_DELIMITER L','
const std::string SQL_CREATE_TABLE = "CREATE TABLE ";
const std::string SQL_INSERT = "INSERT INTO ";

private:
std::vector<std::string> split(std::string &originalStr)
{
int topOfStr = 0;
int endOfStr = 0;
std::vector<std::string> result;

int main()
{
std::chrono::system_clock::time_point start, end;
start = std::chrono::system_clock::now(); // 計始時間

// ２回目以降の実行前にファイルを削除しておく。
std::remove(MY_DB_NAME);
sqlite3 *db = nullptr;

int ret = sqlite3_open(MY_DB_NAME, &db);

if (ret != SQLITE_OK)
{
std::cout << "Failed to open Database File : " << ret << std::endl;
return -1;
}

char *errorMessage = nullptr;
const std::string tableName = MY_TABLE_NAME;

std::string sql_create = SQL_CREATE_TABLE + tableName + "(";
for (int i = 0; i < csvData.titleData.size(); i++)
{
std::string tmp = csvData.titleData[i];
sql_create += "\"" + tmp + "\"";
sql_create += *2
{
sql_insert += ";";
ret = sqlite3_exec(db, sql_insert.c_str(), NULL, NULL, &errorMessage);
if (ret != SQLITE_OK)
{
std::cout << "Failed to insert data (" << tableName.c_str() << ") : " << ret << std::endl;
std::cout << errorMessage << std::endl;
sqlite3_close(db);
sqlite3_free(errorMessage);
return -1;
}
sql_insert = SQL_INSERT + tableName + " values(";
}
else
{
sql_insert += ", (";
}
}
sqlite3_exec(db, "COMMIT;", 0, NULL, &errorMessage);
end = std::chrono::system_clock::now();
elapsed = (double)std::chrono::duration_cast<std::chrono::milliseconds>(end - start).count();
std::cout << "Fin Time = " << elapsed << std::endl;
sqlite3_close(db);
}

実行結果

ついに1秒を切った！
SQLiteはとにかく、SQLのコマンド回数を減らし、なるべくトランザクションで一連の処理をまとめて実行するのが良いということですね。

とはいえリアルタイム系の仕組みの場合、毎秒ポンポンデータが流れてくるような場合は、数秒くらいずつデータを貯めながら書き込むくらいが良いのかな。

しかし、まだ、これは序の口で書き込み早くなったで喜ぶことはできない。実際のアプリケーションでは、クライアント側から読み込みが入るはずだ。SQLiteは、そのようなReadとWriteの同時処理が苦手で有名。次回からは、読み込み側の処理を加えていって、Writeがどのような影響が出るのかを観察します。

*1:i + 1) < (csvData.titleData.size())) ? ", " : ");";
}
std::cout << sql_create << std::endl;

ret = sqlite3_exec(db, sql_create.c_str(), NULL, NULL, &errorMessage);
if (ret != SQLITE_OK)
{
std::cout << "Failed to create table (" << tableName.c_str() << ") : " << ret << std::endl;
std::cout << errorMessage << std::endl;
sqlite3_close(db);
sqlite3_free(errorMessage);
return -1;
}
end = std::chrono::system_clock::now();
elapsed = (double)std::chrono::duration_cast<std::chrono::milliseconds>(end - start).count();
std::cout << "Lap Time 02 = " << elapsed << std::endl;

std::string sql_insert = SQL_INSERT + tableName + " values(";
for (int i = 0; i < csvData.tableData.size(); i++)
{
std::vector<std::string> tmpVec = csvData.tableData[i];
//std::string sql_insert = SQL_INSERT + tableName + " values(";
for (int j = 0; j < tmpVec.size(); j++)
{
std::string tmp = tmpVec[j];
sql_insert += "\"" + tmp + "\"";
sql_insert += ((j + 1) < (tmpVec.size())) ? ", " : ")";
}

if ((i + 1) % 1000 == 0 || (i + 1) >= csvData.tableData.size(

*2:i + 1) < (csvData.titleData.size())) ? ", " : ");";
}
std::cout << sql_create << std::endl;

if ((i + 1) % 1000 == 0 || (i + 1) >= csvData.tableData.size(