Melhorar o desempenho de inserção por segundo do SQLite?

fundo: estamos a usar o SQLite como parte de uma aplicação desktop. Temos grandes quantidades de dados de configuração armazenados em arquivos XML que são processados e carregados em um banco de dados SQLite para processamento posterior quando a aplicação é inicializada. O SQLite é ideal para esta situação porque é rápido, requer nenhuma configuração especializada, e o banco de dados é armazenado em disco como um único arquivo.

fundamentação: inicialmente fiquei desapontado com o desempenho que estava vendo. acontece que o desempenho do SQLite pode variar significativamente (tanto para inserções em massa como para seleções) dependendo de como a base de dados está configurada e como você está usando a API. Não era um assunto trivial para descobrir quais eram todas as opções e técnicas, então eu pensei que era prudente criar este entrada comunitária wiki para compartilhar os resultados com os leitores de Stack Overflow, a fim de salvar outros o problema das mesmas investigações.

The Experiment: Rather than simply talking about performance tips in the general sense (i.e. " Use a transaction!"), eu achei melhor escrever algum código C e realmente medir o impacto de várias opções. Vamos começar com alguns dados simples:

• um ficheiro de texto delimitado por tabulações de 28 MB (aproximadamente 865.000 registos) do plano de trânsito completo para a cidade de Toronto
• a minha máquina de testes é uma janela XP de 3,60 GHz P4 em execução.
• o código é compilado comVisual C++ 2005 como "Release" com "Full Optimization" (/Ox) E Favor Fast Code (/Ot).
Estou a usar a "amalgamação" SQLite, compilada directamente na minha aplicação de teste. A versão SQLite que eu tenho é um pouco mais velha( 3.6.7), mas eu suspeito que estes resultados serão comparável ao último lançamento (por favor deixe um comentário se você pensar de outra forma).

vamos escrever um código!

o código: UM programa C simples que lê o ficheiro de texto linha-a-linha, divide o texto em valores e, em seguida, irá inserir os dados numa base de dados SQLite. Nesta versão "baseline" do código, a base de dados é criada, mas não vamos inserir Dados:

/*************************************************************
    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;
}

O "Controlo"

a correr o código tal como está na verdade não executa nenhuma operação de banco de dados, mas nos dará uma idéia de quão rápido são as operações de processamento de arquivo C raw e string.

importou 864913 registos em 0,94. segundos

O "Pior Cenário"

vamos gerar o texto SQL usando os valores lidos do ficheiro e invocar essa operação SQL usando 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);

isto vai ser lento porque o SQL será compilado em código VDBE para cada sequência inserida e cada sequência irá acontecer na sua própria transacção. Quão lento?

importou 864913 registos em 9933.61 segundos

usando uma transacção

por omissão, o SQLite irá avaliar todas as instruções de inserção / actualização dentro de uma instrução única transaccao. Se efectuar um grande número de inserções, é aconselhável terminar a sua operação numa transacção:

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);

importou 864913 registos em 38.03 segundos

utilizando uma declaração preparada

usar uma transacção foi uma grande melhoria, mas recompilar a declaração SQL para cada inserção não faz sentido se usarmos o mesmo SQL vezes sem conta. Vamos usar sqlite3_prepare_v2 para compilar a nossa declaração SQL uma vez e depois ligar os nossos parâmetros a essa Declaração usando 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;

importou 864913 registos em 16.27 segundos

PRAGMA síncrono = OFF

por omissão, o SQLite irá pausa após a emissão de um comando de escrita ao nível do sistema operacional. Isto garante que os dados são escritos no disco. Ao definir synchronous = OFF, estamos a instruir o SQLite para simplesmente transferir os dados para o SO para escrita e depois continuar. Há uma chance de que o arquivo de banco de dados pode se corromper se o computador sofrer um desastre catastrófico (ou falha de energia) antes que os dados sejam escritos no platter:

/* 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);

importou 864913 registos em 12.41 segundos

PRAGMA journal_mode = memória

considere armazenar o diário de rollback em memória, avaliando PRAGMA journal_mode = MEMORY. A sua transacção será mais rápida, mas se perder energia ou o seu programa falhar durante uma transacção, a sua base de dados poderá ficar num estado corrompido com uma transacção parcialmente concluída:

/* 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);

importou 864913 registos em 13,50 segundos

PRAGMA synchronous = OFF and 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);

importou 864913 registos às 12h00. segundos

usando uma memória Base de dados

só por gozo, vamos construir sobre todas as optimizações anteriores e redefinir o nome do ficheiro de base de dados para que estejamos a trabalhar inteiramente em RAM:

#define DATABASE ":memory:"

importou 864913 registos em 10.94 segundos

Refactoring C Código

embora não seja especificamente uma melhoria SQLite, eu não gosto da operações de atribuição extra char* no circuito while. Vamos rapidamente refazer esse código para passar a saída de strtok() diretamente em sqlite3_bind_text(), e deixar o compilador tentar acelerar as coisas para nós:

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);

Nota: estamos de volta a usar um arquivo de banco de dados real. As bases de dados na memória são rápidas, mas não necessariamente práticas.

importou 864913 registos em 8.94 segundos

um ligeiro ajuste ao código de processamento de texto usado na nossa ligação de parâmetros permitiu-nos realizar 96.700 inserções por segundo.Acho que é seguro dizer que isto é muito rápido. À medida que começamos a ajustar outras variáveis (ou seja, tamanho da página, criação de índice, etc.) esta será a nossa referência.

sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
...

...
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);