Optymalizacja SQLite jest trudna. Wydajność wkładania luzem w aplikacji C może wynosić od 85 płytek na sekundę do ponad 96 000 płytek na sekundę!
Tło: Używamy SQLite jako części aplikacji komputerowej. Mamy duże ilości danych konfiguracyjnych przechowywanych w plikach XML, które są analizowane i ładowane do bazy danych SQLite w celu dalszego przetwarzania podczas inicjalizacji aplikacji. SQLite jest idealny w tej sytuacji, ponieważ jest szybki, nie wymaga specjalnej konfiguracji, a baza danych jest przechowywana na dysku jako pojedynczy plik.
Uzasadnienie: Początkowo byłem rozczarowany występem, który oglądałem. Okazuje się, że wydajność SQLite może się znacznie różnić (zarówno w przypadku wstawiania zbiorczego, jak i selekcji) w zależności od konfiguracji bazy danych i sposobu korzystania z interfejsu API. Nie było trywialne ustalenie, jakie były wszystkie opcje i techniki, więc pomyślałem, że rozsądnie jest utworzyć ten wpis wiki społeczności, aby udostępnić wyniki czytelnikom stosu przepełnienia, aby zaoszczędzić innym problemów związanych z tymi samymi dochodzeniami.
Eksperyment: Zamiast po prostu mówić o ogólnych wskazówkach dotyczących wydajności (tj. „Użyj transakcji!” ), Pomyślałem, że najlepiej napisać trochę kodu C i zmierzyć wpływ różnych opcji. Zaczniemy od kilku prostych danych:
- Plik tekstowy rozdzielony 28 MB TAB (około 865 000 rekordów) z kompletnym harmonogramem tranzytu dla miasta Toronto
- Moja maszyna testowa to 3,60 GHz P4 z systemem Windows XP.
- Kod jest kompilowany z Visual C ++ 2005 jako „Release” z „Full Optimization” (/ Ox) i Favor Fast Code (/ Ot).
- Używam SQLite „Amalgamation”, skompilowanego bezpośrednio w mojej aplikacji testowej. Wersja SQLite, którą posiadam, jest nieco starsza (3.6.7), ale podejrzewam, że te wyniki będą porównywalne z najnowszą wersją (proszę zostawić komentarz, jeśli uważasz inaczej).
Napiszmy kod!
Kod: Prosty program C, który odczytuje plik tekstowy wiersz po wierszu, dzieli ciąg na wartości, a następnie wstawia dane do bazy danych SQLite. W tej „podstawowej” wersji kodu baza danych jest tworzona, ale tak naprawdę nie wstawiamy danych:
/*************************************************************
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] = "\0";
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] = "\0";
/*********************************************/
/* 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, "\t"); /* Get Route */
sBR = strtok (NULL, "\t"); /* Get Branch */
sVR = strtok (NULL, "\t"); /* Get Version */
sST = strtok (NULL, "\t"); /* Get Stop Number */
sVI = strtok (NULL, "\t"); /* Get Vehicle */
sDT = strtok (NULL, "\t"); /* Get Date */
sTM = strtok (NULL, "\t"); /* Get Time */
/* ACTUAL INSERT WILL GO HERE */
n++;
}
fclose (pFile);
printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);
sqlite3_close(db);
return 0;
}
Kontrola"
Uruchamianie kodu w obecnej postaci nie wykonuje żadnych operacji na bazie danych, ale da nam wyobrażenie o tym, jak szybkie są operacje we / wy nieprzetworzonego pliku C i przetwarzania łańcucha.
Zaimportowano 864913 rekordów w 0,94 sekundy
Świetny! Możemy wykonać 920 000 wstawek na sekundę, pod warunkiem, że tak naprawdę nie zrobimy żadnych wstawek :-)
„Scenariusz najgorszego przypadku”
Wygenerujemy ciąg SQL za pomocą wartości odczytanych z pliku i wywołamy tę operację SQL za pomocą sqlite3_exec:
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);
To będzie powolne, ponieważ SQL zostanie skompilowany w kodzie VDBE dla każdej wstawki, a każda wstawka nastąpi w ramach własnej transakcji. Jak wolno
Zaimportowano 864913 rekordów w 9933.61 sekund
Yikes! 2 godziny i 45 minut! To tylko 85 wstawek na sekundę.
Korzystanie z transakcji
Domyślnie SQLite ocenia każdą instrukcję INSERT / UPDATE w ramach unikalnej transakcji. W przypadku wykonywania dużej liczby wstawek wskazane jest zawinięcie operacji w transakcję:
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);
Zaimportowano 864913 rekordów w 38,03 sekundy
Tak lepiej Po prostu zawinięcie wszystkich naszych wkładek w jedną transakcję poprawiło naszą wydajność do 23 000 wkładek na sekundę.
Korzystanie z przygotowanego wyciągu
Wykorzystanie transakcji było ogromnym ulepszeniem, ale rekompilacja instrukcji SQL dla każdej wstawki nie ma sensu, jeśli używamy tego samego SQL w kółko. Użyjmy raz, sqlite3_prepare_v2aby skompilować naszą instrukcję SQL, a następnie powiązać nasze parametry z tą instrukcją, używając 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, "\t"); /* Get Route */
sBR = strtok (NULL, "\t"); /* Get Branch */
sVR = strtok (NULL, "\t"); /* Get Version */
sST = strtok (NULL, "\t"); /* Get Stop Number */
sVI = strtok (NULL, "\t"); /* Get Vehicle */
sDT = strtok (NULL, "\t"); /* Get Date */
sTM = strtok (NULL, "\t"); /* 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", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
Zaimportowano 864913 rekordów w 16,27 sekund
Miły! Jest trochę więcej kodu (nie zapomnij zadzwonić sqlite3_clear_bindingsi sqlite3_reset), ale podwoiliśmy naszą wydajność do 53 000 wstawek na sekundę.
PRAGMA synchroniczny = WYŁ
Domyślnie SQLite zatrzyma się po wydaniu polecenia zapisu na poziomie systemu operacyjnego. To gwarantuje, że dane są zapisywane na dysku. Ustawiając synchronous = OFF, instruujemy SQLite, aby po prostu przekazał dane do systemu operacyjnego w celu zapisu, a następnie kontynuował. Istnieje prawdopodobieństwo, że plik bazy danych zostanie uszkodzony, jeśli komputer ulegnie katastrofalnej awarii (lub awarii zasilania) przed zapisaniem danych na talerzu:
/* 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);
Zaimportowano 864913 rekordów w 12,41 sekund
Ulepszenia są teraz mniejsze, ale mamy do 69 600 wkładek na sekundę.
PRAGMA journal_mode = MEMORY
Rozważ zapisanie dziennika wycofania w pamięci, oceniając PRAGMA journal_mode = MEMORY. Twoja transakcja będzie szybsza, ale jeśli stracisz moc lub program ulegnie awarii podczas transakcji, baza danych może pozostać w stanie uszkodzonym z częściowo zakończoną transakcją:
/* 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);
Zaimportowano 864913 rekordów w 13,50 sekundy
Nieco wolniej niż poprzednia optymalizacja przy 64 000 płytek na sekundę.
PRAGMA synchroniczny = WYŁ. I PRAGMA tryb_ dziennika = PAMIĘĆ
Połączmy dwie poprzednie optymalizacje. Jest to trochę bardziej ryzykowne (w przypadku awarii), ale po prostu importujemy dane (nie prowadzimy banku):
/* 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);
Zaimportowano 864913 rekordów w 12,00 sekund
Fantastyczny! Jesteśmy w stanie wykonać 72 000 wstawek na sekundę.
Korzystanie z bazy danych w pamięci
Na wszelki wypadek wykorzystajmy wszystkie poprzednie optymalizacje i ponownie zdefiniuj nazwę pliku bazy danych, abyśmy pracowali całkowicie w pamięci RAM:
#define DATABASE ":memory:"Zaimportowano 864913 rekordów w 10,94 sekund
Przechowywanie naszej bazy danych w pamięci RAM nie jest zbyt praktyczne, ale imponujące jest to, że możemy wykonać 79 000 wstawek na sekundę.
Refaktoryzacja kodu C.
Chociaż nie jest to specjalnie poprawa SQLite, nie lubię dodatkowych char*operacji przypisywania w whilepętli. Szybko przekształćmy ten kod, aby przekazać dane wyjściowe strtok()bezpośrednio do sqlite3_bind_text(), i pozwólmy kompilatorowi spróbować przyspieszyć dla nas:
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {
fgets (sInputBuf, BUFFER_SIZE, pFile);
sqlite3_bind_text(stmt, 1, strtok (sInputBuf, "\t"), -1, SQLITE_TRANSIENT); /* Get Route */
sqlite3_bind_text(stmt, 2, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Branch */
sqlite3_bind_text(stmt, 3, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Version */
sqlite3_bind_text(stmt, 4, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Stop Number */
sqlite3_bind_text(stmt, 5, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Vehicle */
sqlite3_bind_text(stmt, 6, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Date */
sqlite3_bind_text(stmt, 7, strtok (NULL, "\t"), -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);
Uwaga: wróciliśmy do używania prawdziwego pliku bazy danych. Bazy danych w pamięci są szybkie, ale niekoniecznie praktyczne
Zaimportowano 864913 rekordów w 8,94 sekundy
Nieznaczne przefakturowanie kodu przetwarzania łańcucha używanego w naszym wiązaniu parametrów pozwoliło nam na wykonanie 96 700 wstawień na sekundę. Myślę, że można śmiało powiedzieć, że to dość szybko . Gdy zaczniemy dostosowywać inne zmienne (tj. Rozmiar strony, tworzenie indeksu itp.), Będzie to nasz punkt odniesienia.
Podsumowanie (do tej pory)
Mam nadzieję, że wciąż jesteś ze mną! Powodem, dla którego zaczęliśmy tę drogę, jest to, że wydajność wstawiania zbiorczego zmienia się tak bardzo w zależności od SQLite, i nie zawsze jest oczywiste, jakie zmiany należy wprowadzić, aby przyspieszyć naszą działalność. Korzystając z tego samego kompilatora (i opcji kompilatora), tej samej wersji SQLite i tych samych danych zoptymalizowaliśmy nasz kod i nasze użycie SQLite, aby przejść od najgorszego scenariusza z 85 wstawkami na sekundę do ponad 96 000 wstawień na sekundę!
UTWÓRZ INDEKS, następnie WSTAW vs. WSTAW, a następnie UTWÓRZ INDEKS
Zanim zaczniemy mierzyć SELECTwydajność, wiemy, że będziemy tworzyć wskaźniki. Zasugerowano w jednej z poniższych odpowiedzi, że podczas wykonywania wstawiania zbiorczego szybciej jest tworzyć indeks po wstawieniu danych (w przeciwieństwie do tworzenia indeksu najpierw, a następnie wstawiania danych). Spróbujmy:
Utwórz indeks, a następnie wstaw dane
sqlite3_exec(db, "CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
...Zaimportowano 864913 rekordów w 18,13 sekund
Wstaw dane, a następnie Utwórz indeks
...
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);Zaimportowano 864913 rekordów w 13,66 sekund
Zgodnie z oczekiwaniami wstawianie zbiorcze jest wolniejsze, jeśli jedna kolumna jest indeksowana, ale robi to różnicę, jeśli indeks jest tworzony po wstawieniu danych. Nasza linia bazowa bez indeksu wynosi 96 000 wstawek na sekundę. Utworzenie indeksu najpierw, a następnie wstawienie danych daje nam 47 700 wstawek na sekundę, podczas gdy wstawienie danych, a następnie utworzenie indeksu daje nam 63 300 wstawek na sekundę.
Z przyjemnością skorzystam z sugestii dotyczących innych scenariuszy do wypróbowania ... I wkrótce skompiluję podobne dane dla zapytań SELECT.
sqlite3_clear_bindings(stmt);? Powiązania ustawia się za każdym razem, gdy powinno wystarczyć: Przed pierwszym wywołaniem funkcji sqlite3_step () lub bezpośrednio po funkcji sqlite3_reset () aplikacja może wywołać jeden z interfejsów sqlite3_bind () w celu dołączenia wartości do parametrów. Każde wywołanie funkcji sqlite3_bind () zastępuje wcześniejsze wiązania tego samego parametru (patrz: sqlite.org/cintro.html ). Nie ma nic w docs dla tej funkcji mówiąc trzeba to nazwać.
feof()do kontrolowania zakończenia pętli wejściowej. Użyj wyniku zwróconego przez fgets(). stackoverflow.com/a/15485689/827263