Pytanie Poprawić wydajność SQLite INSERT na sekundę?


Optymalizacja SQLite jest trudna. Wydajność wkładania luzem aplikacji C może wynosić od 85 przekładek na sekundę do ponad 96 000 wkładek na sekundę!

Tło: Używamy SQLite jako część aplikacji desktopowej. 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 po zainicjowaniu aplikacji. SQLite jest idealny do tej sytuacji, ponieważ jest szybki, nie wymaga specjalistycznej konfiguracji, a baza danych jest przechowywana na dysku jako pojedynczy plik.

Racjonalne uzasadnienie:  Początkowo byłem rozczarowany występem, który widziałem. Okazuje się, że wydajność SQLite może się znacznie różnić (zarówno w przypadku insertów zbiorczych, jak i selekcji) w zależności od konfiguracji bazy danych i sposobu korzystania z interfejsu API. Nie było sprawą trywialną, aby dowiedzieć się, jakie były wszystkie opcje i techniki, więc pomyślałem, że rozsądnie jest stworzyć ten wpis wiki społeczności, aby udostępnić wyniki czytelnikom Stack Overflow, aby zaoszczędzić innym kłopotów z tymi samymi badaniami.

Eksperyment: Zamiast po prostu mówić o poradach dotyczących wydajności w ogólnym znaczeniu (tj. "Użyj transakcji!"), Pomyślałem, że najlepiej napisać kod C i właściwie zmierzyć wpływ różnych opcji. Zaczniemy od prostych danych:

  • Plik tekstowy rozdzielany tabulatorami o wielkości 28 MB (około 865 000 rekordów) kompletny rozkład jazdy dla miasta Toronto
  • Moja maszyna testowa to P4 z procesorem 3,60 GHz z systemem Windows XP.
  • Kod jest kompilowany przy pomocy Visual C ++ 2005 jako "Release" z "Full Optimization" (/ Ox) i Fast Fast Code (/ Ot).
  • Używam SQLite "Amalgamation", skompilowanego bezpośrednio do mojej aplikacji testowej. Wersja SQLite, którą mam, jest nieco starsza (3.6.7), ale podejrzewam, że te wyniki będą porównywalne z najnowszą wersją (proszę zostawić komentarz, jeśli myślisz inaczej).

Napiszmy kod!

Kod: Prosty program w języku C, który odczytuje plik tekstowy wiersz po wierszu, dzieli ciąg na wartości, a następnie wstawi dane do bazy danych SQLite. W tej "podstawowej" wersji kodu tworzona jest baza danych, ale nie będziemy wstawiać 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 rzeczywistości nie wykonuje żadnych operacji na bazie danych, ale daje nam wyobrażenie o szybkości przetwarzania wejściowych / wyjściowych plików C i operacji przetwarzania ciągów.

Zaimportowano 864913 rekordów w 0,94   sekundy

Wspaniały! Możemy wykonać 920 000 wkładek na sekundę, pod warunkiem, że nie wykonujemy żadnych wkładek :-)


"Najgorszy scenariusz"

Zamierzamy wygenerować ciąg SQL używając wartości odczytanych z pliku i wywołać 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 wolno, ponieważ SQL zostanie skompilowany do kodu VDBE dla każdej wstawki, a każdy insert stanie się jego własną transakcją. Jak wolno?

Zaimportowano 864913 rekordów w 9933.61   sekundy

Yikes! 2 godziny i 45 minut! To tylko 85 przekładek na sekundę.

Korzystanie z transakcji

Domyślnie SQLite oceni każdą instrukcję INSERT / UPDATE w ramach unikalnej transakcji. Jeśli wykonujesz dużą liczbę wstawek, dobrze jest zawrzeć operację w transakcji:

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 jest lepiej. Po prostu opakowanie wszystkich naszych wkładek w jednej transakcji poprawiło naszą wydajność 23 000 wkładek na sekundę.

Korzystanie z przygotowanego wyciągu

Korzystanie z transakcji było ogromną poprawą, ale rekompilacja instrukcji SQL dla każdej wstawki nie ma sensu, jeśli używamy tego samego SQL-a-over-over. Użyjmy sqlite3_prepare_v2 aby skompilować naszą instrukcję SQL raz, a następnie powiązać nasze parametry z tą instrukcją za pomocą 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;

Zaimportowane zapisy 864913 w 16.27   sekundy

Miły! Jest trochę więcej kodu (nie zapomnij zadzwonić sqlite3_clear_bindings i sqlite3_reset), ale ponad dwukrotnie zwiększyliśmy naszą wydajność 53 000 przekładek na sekundę.

PRAGMA synchronous = OFF

Domyślnie SQLite zatrzyma się po wydaniu polecenia zapisu na poziomie systemu operacyjnego. Gwarantuje to, że dane są zapisywane na dysku. Przez ustawienie synchronous = OFF, instruujemy SQLite, aby po prostu przekazywał dane do systemu operacyjnego w celu pisania, a następnie kontynuowania. Istnieje prawdopodobieństwo, że plik bazy danych zostanie uszkodzony, jeśli komputer ulegnie katastroficznej awarii (lub awarii zasilania) zanim dane zostaną zapisane 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);

Zaimportowane zapisy 864913 w 12.41   sekundy

Ulepszenia są teraz mniejsze, ale jesteśmy gotowi 69,600 wkładów na sekundę.

PRAGMA journal_mode = MEMORY

Rozważ przechowywanie dziennika wycofania w pamięci przez ocenę PRAGMA journal_mode = MEMORY. Twoja transakcja będzie szybsza, ale jeśli utracisz moc lub program zawiesza się podczas transakcji, baza danych może pozostać w stanie uszkodzonym z częściowo ukoń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 zapisy 864913 w 13.50   sekundy

Trochę wolniej niż poprzednia optymalizacja na 64 000 wkładów na sekundę.

PRAGMA synchronous = OFF i PRAGMA journal_mode = MEMORY

Połączmy dwie poprzednie optymalizacje. Jest trochę bardziej ryzykowny (na wypadek 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 zapisy 864913 w 12.00   sekundy

Fantastyczny! Jesteśmy w stanie to zrobić 72 000 wkładek na sekundę.

Używanie bazy danych w pamięci

Tylko dla kopnięć, wykorzystajmy wszystkie poprzednie optymalizacje i przedefiniujmy nazwę bazy danych, abyśmy pracowali w całości w pamięci RAM:

#define DATABASE ":memory:"

Zaimportowano 864913 rekordów w 10.94   sekundy

Przechowywanie naszej bazy danych w pamięci RAM nie jest zbyt praktyczne, ale imponujące jest to, że możemy ją wykonać 79 000 wkładek na sekundę.

Refaktoryzujący kod C.

Chociaż nie jest to szczególnie poprawa SQLite, nie podoba mi się to char*operacje przypisania w while pętla. Szybko zredukujmy ten kod, aby przekazać wynik strtok() bezpośrednio w sqlite3_bind_text()i niech kompilator spróbuje przyspieszyć działanie 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 korzystania z prawdziwego pliku bazy danych. Bazy danych w pamięci są szybkie, ale niekoniecznie praktyczne

Zaimportowano 864913 rekordów w 8,94   sekundy

Lekki refaktoryzacja kodu przetwarzania ciągu wykorzystywanego w naszym powiązaniu parametrów umożliwiła nam wykonanie 96,700 wkładek na sekundę. Myślę, że bezpiecznie jest powiedzieć, że tak dużo szybko. Gdy zaczniemy dostosowywać inne zmienne (np. Rozmiar strony, tworzenie indeksu itd.), Będzie to nasz benchmark.


Podsumowanie (do tej pory)

Mam nadzieję, że nadal jesteś ze mną! Powodem, dla którego rozpoczęliśmy tę drogę, jest to, że wydajność SQL-a jest tak różna, i nie zawsze jest oczywiste, jakie zmiany należy wprowadzić, aby przyspieszyć działanie. Korzystanie z tego samego kompilatora (i opcji kompilatora), tej samej wersji SQLite i tych samych danych, które zoptymalizowaliśmy nasz kod i nasze użycie SQLite, aby przejść od najgorszego scenariusza - 85 przekładek na sekundę do ponad 96 000 wkładek na sekundę!


UTWÓRZ INDEKS, a następnie INSERT vs. INSERT, a następnie UTWÓRZ INDEKS

Zanim zaczniemy mierzyć SELECT wydajność, wiemy, że będziemy tworzyć indeksy. W jednej z poniższych odpowiedzi zasugerowano, że przy wstawianiu danych luzem szybciej jest utworzyć 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 zapisy 864913 w 18.13   sekundy

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   sekundy

Zgodnie z oczekiwaniami wstawki zbiorcze są wolniejsze, jeśli jedna kolumna jest indeksowana, ale ma znaczenie, jeśli indeks zostanie utworzony po wstawieniu danych. Nasza linia bazowa bez indeksu to 96 000 wkładek na sekundę. Najpierw tworzenie indeksu, a następnie wstawienie danych daje 47 700 wstawek na sekundę, natomiast wstawienie danych najpierw, a następnie utworzenie indeksu daje nam 63.300 wstawek na sekundę.


Z chęcią przyjmuję sugestie dotyczące innych scenariuszy do wypróbowania ... I wkrótce będą gromadzić podobne dane dla zapytań SELECT.


2573


pochodzenie


Słuszna uwaga! W naszym przypadku mamy do czynienia z około 1,5 milionami par klucz / wartość odczytywanych z plików tekstowych XML i CSV do rekordów 200k. Małe w porównaniu do baz danych, które uruchamiają strony takie jak SO - ale wystarczająco duże, aby dostrojenie wydajności SQLite stało się ważne. - Mike Willekes
"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 po zainicjowaniu aplikacji." dlaczego nie przechowujesz wszystkiego w bazie danych sqlite, zamiast przechowywać w XML, a następnie ładować wszystko w czasie inicjalizacji? - CAFxX
Czy próbowałeś nie dzwonić sqlite3_clear_bindings(stmt);? Ustawiasz powiązania za każdym razem, przez które powinno wystarczyć: Przed wywołaniem sqlite3_step () po raz pierwszy lub zaraz po 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 sqlite3_bind () zastępuje wcześniejsze wiązania na tym samym parametrze (widzieć: sqlite.org/cintro.html). Nic nie ma w dokumenty dla tej funkcji mówiąc, że musisz to nazwać. - ahcox
ahcox: wiązanie odbywa się pod adresem wskazanym, a nie zmiennym, więc od tego czasu nie będzie działać strtok zwraca za każdym razem nowy wskaźnik. Musisz albo strcpy po każdym strtok lub stwórz własny tokenizer, który zawsze kopiuje, gdy czyta ciąg znaków. - nemetroid
Czy robiłeś powtarzające się pomiary? "Wygrana" 4s dla uniknięcia 7 lokalnych wskaźników jest dziwna, nawet przy założeniu, że myli optymalizator. - peterchen


Odpowiedzi:


Kilka wskazówek:

  1. Umieść inserty / aktualizacje w transakcji.
  2. Dla starszych wersji SQLite - Rozważ mniejszy czasowy tryb dziennika (pragma journal_mode). Jest NORMAL, a potem jest OFF, co może znacznie zwiększyć prędkość wstawiania, jeśli nie martwisz się zbytnio, że baza danych ulegnie uszkodzeniu, jeśli system ulegnie awarii. Jeśli twoja aplikacja ulegnie awarii, dane powinny być w porządku. Zauważ, że w nowszych wersjach OFF/MEMORY ustawienia nie są bezpieczne dla awarii aplikacji.
  3. Zabawa z rozmiarami stron również robi różnicę (PRAGMA page_size). Większe strony mogą sprawić, że odczytywanie i zapisywanie będzie przebiegać szybciej, ponieważ większe strony będą przechowywane w pamięci. Zauważ, że więcej pamięci zostanie użyte dla twojej bazy danych.
  4. Jeśli masz indeksy, rozważ dzwonienie CREATE INDEXpo wykonaniu wszystkich wkładek. Jest to znacznie szybsze niż tworzenie indeksu, a następnie wykonywanie wstawek.
  5. Musisz być bardzo ostrożny, jeśli masz równoczesny dostęp do SQLite, ponieważ cała baza danych jest zablokowana, gdy zapisy są zrobione, i chociaż wielu czytelników jest możliwych, zapisy zostaną zablokowane. Zostało to nieco poprawione dzięki dodaniu WAL w nowszych wersjach SQLite.
  6. Wykorzystaj oszczędność miejsca ... mniejsze bazy danych działają szybciej. Na przykład, jeśli masz pary wartości kluczy, spróbuj utworzyć klucz INTEGER PRIMARY KEY jeśli to możliwe, co zastąpi domniemaną unikalną kolumnę numeru wiersza w tabeli.
  7. Jeśli używasz wielu wątków, możesz spróbować użyć udostępniona pamięć podręczna strony, co pozwoli na współdzielenie załadowanych stron między wątkami, co może uniknąć kosztownych wywołań we / wy.
  8. Nie używaj !feof(file)!

Zadałem także podobne pytania tutaj i tutaj.


672



Dokumenty nie znają PRAGMA dziennik_dodaj NORMAL sqlite.org/pragma.html#pragma_journal_mode - OneWorld
Minęło trochę czasu, moje sugestie dotyczyły starszych wersji przed wprowadzeniem WAL. Wygląda na to, że DELETE to nowe normalne ustawienie, a teraz są również ustawienia OFF i MEMORY. Przypuszczam, że OFF / MEMORY poprawi wydajność zapisu kosztem integralności bazy danych, a OFF całkowicie wyłącza wycofywanie. - Snazzer
dla # 7 masz przykład, jak włączyć udostępniona pamięć podręczna strony przy użyciu opakowania c # system.data.sqlite? - Aaron Hudon
# 4 przywrócił stare wspomnienia - W przeszłości było co najmniej jeden przypadek, w którym upuszczenie indeksu przed grupą dodawania i ponowne jego utworzenie później znacznie przyspieszyło wstawianie indeksu. Może nadal działać szybciej w nowoczesnych systemach dla niektórych dodatków, gdy wiesz, że masz wyłączny dostęp do stołu przez ten okres. - Bill K


Spróbuj użyć SQLITE_STATIC zamiast SQLITE_TRANSIENT dla tych wkładek.

SQLITE_TRANSIENT spowoduje, że SQLite skopiuje dane ciągu przed powrotem.

SQLITE_STATIC mówi, że adres pamięci, który podałeś, będzie ważny do czasu wykonania kwerendy (co zawsze ma miejsce w tej pętli). Pozwoli to zaoszczędzić kilka operacji przydzielania, kopiowania i zwolnienia na pętlę. Prawdopodobnie duża poprawa.


102





Unikaj sqlite3_clear_bindings (stmt);

Kod w teście ustawia powiązania za każdym razem, przez które powinno wystarczyć.

Wstęp do interfejsu C API z dokumentacji SQLite

Przed wywołaniem sqlite3_step () po raz pierwszy lub natychmiast   po sqlite3_reset () aplikacja może wywołać jeden z plików   sqlite3_bind () Interfejsy do dołączania wartości do parametrów. Każdy   wywołanie sqlite3_bind () przesłania wcześniejsze wiązania na tym samym parametrze

(widzieć: sqlite.org/cintro.html). W dokumentach nie ma niczego ta funkcja mówiąc, że musisz wywołać to oprócz ustawiania powiązań.

Więcej szczegółów: http://www.hoogli.com/blogs/micro/index.html#Avoid_sqlite3_clear_bindings ()


80



Cudownie na prawdę: "W przeciwieństwie do intuicji wielu, sqlite3_reset () nie resetuje powiązań na przygotowanej instrukcji.Użyj tej procedury, aby zresetować wszystkie parametry hosta do NULL." - sqlite.org/c3ref/clear_bindings.html - Francis Straccia


W przypadku wkładek luzem

Zainspirowany tym postem i pytaniem Stack Overflow, które doprowadziło mnie tutaj - Czy w bazie danych SQLite można wstawiać wiele wierszy jednocześnie? - Napisałem swój pierwszy Git magazyn:

https://github.com/rdpoor/CreateOrUpdate

który luzem ładuje tablicę ActiveRecords do MySQL, SQLite lub PostgreSQL bazy danych. Zawiera opcję zignorowania istniejących rekordów, zastąpienia ich lub zgłoszenia błędu. Moje podstawowe testy porównawcze wykazują 10-krotny wzrost szybkości w porównaniu do zapisów sekwencyjnych - YMMV.

Używam go w kodzie produkcyjnym, gdzie często potrzebuję importować duże zestawy danych i jestem z tego całkiem zadowolony.


47



@Jess: Jeśli podążysz za linkiem, zobaczysz, że miał na myśli składnię wsadową wsadu. - Alix Axel


Import zbiorczy wydaje się najlepiej działać, jeśli możesz podzielić się INSERT / UPDATE sprawozdania. Wartość 10 000 lub więcej działa dobrze dla mnie na stole z tylko kilkoma rzędami, YMMV ...


40



Chcesz ustawić x = 10 000, aby x = cache [= cache_size * rozmiar strony] / średni rozmiar wstawki. - Alix Axel


Jeśli zależy ci tylko na czytaniu, nieco szybszy (ale może czytać nieaktualne dane) wersja jest odczytywana z wielu połączeń z wielu wątków (połączenie na wątek).

Najpierw znajdź przedmioty w tabeli:

 SELECT COUNT(*) FROM table

następnie odczytywanie na stronach (LIMIT / OFFSET)

  SELECT * FROM table ORDER BY _ROWID_ LIMIT <limit> OFFSET <offset>

gdzie i są obliczane według wątków, jak poniżej:

int limit = (count + n_threads - 1)/n_threads;

dla każdego wątku:

int offset = thread_index * limit

Dla naszego małego (200 MB) db to przyspieszenie o 50-75% (3.8.0.2 64-bitowe w Windows 7). Nasze tabele są mocno nieznormalizowane (1000-1500 kolumn, w przybliżeniu 100 000 lub więcej wierszy).

Zbyt wiele lub zbyt mało wątków tego nie zrobi, musisz przeprowadzić test porównawczy i samemu się profilować.

Również dla nas SHAREDCACHE spowolnił działanie, więc ręcznie wstawiłem PRIVATECACHE (ponieważ dla nas było to globalnie włączone)


32





Nie mogę uzyskać żadnego zysku z transakcji, dopóki nie podniosę wartości cache_size do wyższej wartości, tj. PRAGMA cache_size=10000;


20





Po przeczytaniu tego samouczka, próbowałem go zaimplementować do mojego programu.

Mam 4-5 plików zawierających adresy. Każdy plik ma około 30 milionów rekordów. Używam tej samej konfiguracji, którą sugerujesz, ale moja liczba INSERT na sekundę jest zdecydowanie niska (~ 10.000 rekordów na sekundę).

Tutaj kończy się Twoja sugestia. Korzystasz z pojedynczej transakcji dla wszystkich rekordów i pojedynczego wkładu bez błędów / awarii. Powiedzmy, że dzielisz każdy rekord na kilka insertów na różnych tabelach. Co się stanie, jeśli rekord zostanie złamany?

Komenda ON CONFLICT nie ma zastosowania, ponieważ jeśli masz 10 elementów w rekordzie i potrzebujesz, aby każdy element został wstawiony do innej tabeli, jeśli element 5 otrzyma błąd CONSTRAINT, wszystkie poprzednie 4 inserty również muszą przejść.

Oto, gdzie nadchodzi rollback. Jedynym problemem z wycofywaniem jest utrata wszystkich wstawek i rozpoczęcie od góry. Jak możesz rozwiązać ten problem?

Moim rozwiązaniem było użycie wielokrotność transakcje. Rozpoczynam i kończę transakcję co 10.000 rekordów (nie pytaj dlaczego to numer, to był najszybszy testowany przeze mnie). Stworzyłem tablicę o wielkości 10.000 i wstawiłem tam udane rekordy. Gdy wystąpi błąd, wykonuję wycofanie, rozpoczynam transakcję, wstawiam rekordy z mojej tablicy, zatwierdzam, a następnie rozpoczynam nową transakcję po uszkodzeniu rekordu.

To rozwiązanie pomogło mi ominąć problemy, które mam, gdy zajmowałem się plikami zawierającymi złe / zduplikowane rekordy (miałem prawie 4% złych zapisów).

Algorytm, który stworzyłem, pomógł mi zredukować mój proces o 2 godziny. Ostateczny proces ładowania pliku 1 godz. 30m, który jest nadal powolny, ale nie w porównaniu do 4 godzin, które początkowo miał. Udało mi się przyspieszyć wstawki od 10.000 / s do ~ 14.000 / s

Jeśli ktoś ma jakieś pomysły na przyspieszenie, jestem otwarty na sugestie.

AKTUALIZACJA:

Oprócz powyższej odpowiedzi powinieneś pamiętać, że wkładki na sekundę zależą od używanego dysku twardego. Przetestowałem go na 3 różnych komputerach z różnymi dyskami twardymi i uzyskałem ogromne różnice w czasie. PC1 (1 godz. 30 m), PC2 (6 godz.) PC3 (14 godz.), Więc zacząłem się zastanawiać, dlaczego tak się stało.

Po dwóch tygodniach badań i sprawdzeniu wielu zasobów: dysku twardego, pamięci RAM, pamięci podręcznej, dowiedziałem się, że niektóre ustawienia dysku twardego mogą wpływać na szybkość operacji we / wy. Klikając właściwości na żądanym napędzie wyjściowym, można wyświetlić dwie opcje na karcie ogólnej. Opt1: Kompresuj ten dysk, Opt2: Zezwalaj na pliki z tego dysku do indeksowania zawartości.

Po wyłączeniu tych dwóch opcji wszystkie 3 komputery kończą się w przybliżeniu w tym samym czasie (1 godzina i 20 do 40 minut). Jeśli napotkasz powolne wstawki, sprawdź, czy Twój dysk twardy jest skonfigurowany z tymi opcjami. Zaoszczędzi ci to mnóstwo czasu i bólów głowy, próbując znaleźć rozwiązanie


11