最佳化SQLite很棘手. C應用程式的Bulkinsert效能可以從每秒85個插入到每秒96,000多個插入!
背景:我們使用SQLite作為桌面應用程式的一部分.我們有大量的配置資料儲存在XML檔案中,當應用程式初始化時解析並載入到SQLite資料庫進一步處理. SQLite對於這種情況是理想的,因為它很快,它不需要專門的配置,資料庫儲存在磁碟上作為單個檔案.
理由:最初我對我看到的效能感到失望.它轉出了SQLite的效能可以根據資料庫的配置方式和使用API的方式大不相同(包括bulking和選擇).弄清楚所有選項和技術是什麼並不是一個微不足道的事情,所以我認為建立這個社群wiki條目來與Stack Overflow讀者共享結果以儲存其他人的調查麻煩是謹慎的.
實驗:我認為最好編寫一些C程式碼並實際測量各種選項的影響.我們將從一些簡單的資料開始:
- 一個 28 MB TAB 分隔的文字檔案(約 865,000 條記錄) 完整的多倫多 的過境時間表
- 我的測試機是執行Windows XP的3.60 GHz P4.
- 程式碼是用 Visual C++ 2005作為“Release”編譯的,其中包括“完全最佳化”(/ Ox)和法惠或快速程式碼(/ Ot)。
- 我正在使用SQLite“Amalgamation”,直接編譯到我的測試應用程式中.我碰巧擁有的SQLite版本有點老(3.6.7),但我懷疑這些結果將與最新版本相當(如果你認為其他的話請留下評論).
讓我們寫一些程式碼!
程式碼:一個讀取文字檔案line-byline的簡單C程式,將字串分解為值,然後將資料插入到SQLite資料庫中.在這個程式碼的“基線”版本中,建立資料庫,但我們實際上不會插入資料:
/*************************************************************
Baseline code to experiment with SQLite performance.
Input data is a 28 MB TAB-delimited text file of the
complete Toronto Transit System schedule/route info
from http://www.toronto.ca/open/datasets/ttc-routes/
**************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <string.h>
#include "sqlite3.h"
#define INPUTDATA "C:\TTC_schedule_scheduleitem_10-27-2009.txt"
#define DATABASE "c:\TTC_schedule_scheduleitem_10-27-2009.sqlite"
#define TABLE "CREATE TABLE IF NOT EXISTS TTC (id INTEGER PRIMARY KEY, Route_ID TEXT, Branch_Code TEXT, Version INTEGER, Stop INTEGER, Vehicle_Index INTEGER, Day Integer, Time TEXT)"
#define BUFFER_SIZE 256
int main(int argc, char **argv) {
sqlite3 * db;
sqlite3_stmt * stmt;
char * sErrMsg = 0;
char * tail = 0;
int nRetCode;
int n = 0;
clock_t cStartClock;
FILE * pFile;
char sInputBuf [BUFFER_SIZE] = " ";
char * sRT = 0; /* Route */
char * sBR = 0; /* Branch */
char * sVR = 0; /* Version */
char * sST = 0; /* Stop Number */
char * sVI = 0; /* Vehicle */
char * sDT = 0; /* Date */
char * sTM = 0; /* Time */
char sSQL [BUFFER_SIZE] = " ";
/*********************************************/
/* Open the Database and create the Schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
/*********************************************/
/* Open input file and import into Database*/
cStartClock = clock();
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {
fgets (sInputBuf, BUFFER_SIZE, pFile);
sRT = strtok (sInputBuf, " "); /* Get Route */
sBR = strtok (NULL, " "); /* Get Branch */
sVR = strtok (NULL, " "); /* Get Version */
sST = strtok (NULL, " "); /* Get Stop Number */
sVI = strtok (NULL, " "); /* Get Vehicle */
sDT = strtok (NULL, " "); /* Get Date */
sTM = strtok (NULL, " "); /* Get Time */
/* ACTUAL INSERT WILL GO HERE */
n++;
}
fclose (pFile);
printf("Imported %d records in %4.2f seconds
", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);
sqlite3_close(db);
return 0;
}
"控制"
執行程式碼as-is實際上沒有執行任何資料庫操作,但它會讓我們知道原始的C檔案I/O和字串處理操作有多快.
進口記錄 864913 0.94 秒
很棒!我們可以每秒做920,000個插入,只要我們實際上沒有做任何插入: –)
"Worst-Case-Scenario"
我們將使用從檔案中讀取的值生成SQL字串並使用sqlite3_exec呼叫該SQL操作:
sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, '%s', '%s', '%s', '%s', '%s', '%s', '%s')", sRT, sBR, sVR, sST, sVI, sDT, sTM);
sqlite3_exec(db, sSQL, NULL, NULL, &sErrMsg);
這將很慢,因為SQL將編譯成每個插入的VDBE程式碼,每個插入都會在自己的事務中發生.多麼慢?
進口記錄 864913 9933.61 秒
yikes!2小時45分鐘!每秒只有85個插入。
使用交易
預設情況下,SQLite將在唯一事務中評估每個INSERT / UPDATE語句.如果執行大量插入,最好將操作包裝在事務中:
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {
...
}
fclose (pFile);
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
進口記錄 864913 38.03 秒
這更好.簡單地將我們的所有插入包裝在一個事務中將我們的效能提高到每秒23,000個插入.
使用編寫的說明
使用事務是一個巨大的改進,但是如果我們使用相同的SQL over-and-over,重新編譯每個插入的SQL語句沒有意義.讓我們使用sqlite3_prepare_v2
一次編譯我們的SQL語句,然後使用sqlite3_bind_text
將我們的引數繫結到該語句:
/* Open input file and import into the database */
cStartClock = clock();
sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, @RT, @BR, @VR, @ST, @VI, @DT, @TM)");
sqlite3_prepare_v2(db, sSQL, BUFFER_SIZE, &stmt, &tail);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {
fgets (sInputBuf, BUFFER_SIZE, pFile);
sRT = strtok (sInputBuf, " "); /* Get Route */
sBR = strtok (NULL, " "); /* Get Branch */
sVR = strtok (NULL, " "); /* Get Version */
sST = strtok (NULL, " "); /* Get Stop Number */
sVI = strtok (NULL, " "); /* Get Vehicle */
sDT = strtok (NULL, " "); /* Get Date */
sTM = strtok (NULL, " "); /* Get Time */
sqlite3_bind_text(stmt, 1, sRT, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 2, sBR, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 3, sVR, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 4, sST, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 5, sVI, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 6, sDT, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 7, sTM, -1, SQLITE_TRANSIENT);
sqlite3_step(stmt);
sqlite3_clear_bindings(stmt);
sqlite3_reset(stmt);
n++;
}
fclose (pFile);
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
printf("Imported %d records in %4.2f seconds
", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
16.27中的進口記錄864913 秒
很好!有更多的程式碼(不要忘記呼叫sqlite3_clear_bindings
和sqlite3_reset
),但我們的效能增加了一倍以上,每秒53,000個插入.
pragma同步=OFF
預設情況下,SQLite在發出OSlevel write命令後暫停.這保證資料被寫入磁碟.透過設定synchronous = OFF
,我們指示SQLite簡單地將資料傳輸到作業系統以便寫入,然後繼續.如果計算機在資料寫入平臺之前遇到災難性崩潰(或功率故障),資料庫檔案可能會受到損壞:
/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
12.41中進口的864913條記錄 秒
這些改進現在更小了,但我們每秒多達69,600個插入。
pragma journal_mode = MEMORY
考慮透過評估PRAGMA journal_mode = MEMORY
將回滾日誌儲存在記憶體中.您的事務將更快,但如果您失去功率或您的程式在事務期間崩潰,您的資料庫可能會由於部分完成的事務而處於腐敗狀態:
/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);
進口記錄 864913 13.50 秒
比之前的最佳化慢一點,每秒64,000插入。
pragma同步= OFF和PRAGMA journal_mode = MEMORY
讓我們結合前兩個最佳化.它有點風險(在崩潰的情況下),但我們只是匯入資料(不執行銀行):
/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);
進口記錄 864913 12.00 秒
太棒了!我們能每秒做72000個插入。
使用記憶體資料庫
只是為了踢球,讓我們基於以前的所有最佳化並重新定義資料庫檔名,所以我們完全在RAM中工作:
#define DATABASE ":memory:"
在10.94中匯入864913條記錄 秒
將資料庫儲存在 RAM 中是不太實用的,但是我們可以每秒執行 79,000 個插入令人印象深刻。
重構C程式碼
雖然沒有具體的SQLite改進,但我不喜歡char*
分配操作中的額外while
迴圈.讓我們快速重構該程式碼以將strtok()
的輸出直接傳遞給sqlite3_bind_text()
,並讓編譯器嘗試加速我們的事情:
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {
fgets (sInputBuf, BUFFER_SIZE, pFile);
sqlite3_bind_text(stmt, 1, strtok (sInputBuf, " "), -1, SQLITE_TRANSIENT); /* Get Route */
sqlite3_bind_text(stmt, 2, strtok (NULL, " "), -1, SQLITE_TRANSIENT); /* Get Branch */
sqlite3_bind_text(stmt, 3, strtok (NULL, " "), -1, SQLITE_TRANSIENT); /* Get Version */
sqlite3_bind_text(stmt, 4, strtok (NULL, " "), -1, SQLITE_TRANSIENT); /* Get Stop Number */
sqlite3_bind_text(stmt, 5, strtok (NULL, " "), -1, SQLITE_TRANSIENT); /* Get Vehicle */
sqlite3_bind_text(stmt, 6, strtok (NULL, " "), -1, SQLITE_TRANSIENT); /* Get Date */
sqlite3_bind_text(stmt, 7, strtok (NULL, " "), -1, SQLITE_TRANSIENT); /* Get Time */
sqlite3_step(stmt); /* Execute the SQL Statement */
sqlite3_clear_bindings(stmt); /* Clear bindings */
sqlite3_reset(stmt); /* Reset VDBE */
n++;
}
fclose (pFile);
注意:我們回到使用真正的資料庫檔案. In-記憶體資料庫很快,但不一定實用
8.94中的進口記錄864913 秒
對我們引數繫結中使用的字串處理程式碼進行輕微重構,使我們能夠每秒執行96,700個插入.我認為可以安全地說這是很快的.當我們開始調整其他變數(即頁面大小,索引建立等)時,這將是我們的基準.
摘要(迄今)
我希望你仍然跟我在一起!我們開始這條路的原因是bulkinsert效能與SQLite非常不同,並且並不總是明顯需要進行哪些更改來加快我們的操作.使用相同的編譯器(和編譯器選項),相同的SQLite版本和相同的資料,我們已經優化了我們的程式碼,並且我們使用SQLite從每秒85插入的worstcase場景轉到每秒96,000多插入!
建立INDEX然後INSERT與INSERT然後CREATE INDEX
在我們開始測量SELECT
效能之前,我們知道我們將建立索引.下面的一個答案建議在進行批次插入時,在插入資料後建立索引更快(而不是先建立索引然後插入資料).讓我們嘗試:
建立索引然後插入資料
sqlite3_exec(db, "CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
...
進口記錄864913 秒
插入資料然後建立索引
...
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
13.66年進口記錄864913 秒
正如預期的那樣,如果一列被索引,bulk-instruct會更慢,但如果在插入資料後建立索引,則確實會有所不同.我們的noindex基線是每秒96,000插入.建立索引然後首先插入資料給我們每秒47,700插入,而先插入資料然後建立索引給我們每秒63,300插入.
我很樂意為其他場景提出建議來嘗試...並且很快將為SELECT查詢編譯類似的資料.