SQLite를 최적화하는 것은 까다 롭습니다. C 어플리케이션의 대량 삽입 성능은 초당 85 개의 삽입에서 초당 96,000 이상의 삽입까지 다양합니다!

배경 : 데스크톱 응용 프로그램의 일부로 SQLite를 사용하고 있습니다. 애플리케이션이 초기화 될 때 추가 처리를 위해 구문 분석되어 SQLite 데이터베이스로로드되는 XML 파일에 많은 양의 구성 데이터가 저장되어 있습니다. SQLite는 속도가 빠르며 특수한 구성이 필요하지 않으며 데이터베이스가 단일 파일로 디스크에 저장되므로 이러한 상황에 이상적입니다.

근거 : 처음에는 내가보고있는 성능에 실망했습니다. 데이터베이스 구성 방법과 API 사용 방법에 따라 SQLite의 성능이 크게 달라질 수 있습니다 (대량 삽입 및 선택). 모든 옵션과 기술이 무엇인지 파악하는 것은 사소한 일이 아니므로 동일한 커뮤니티의 조사 문제를 다른 사람들이 해결하기 위해 스택 오버플로 리더와 결과를 공유하기 위해이 커뮤니티 위키 항목을 작성하는 것이 현명하다고 생각했습니다.

실험 : 일반적인 의미의 성능 팁 (예 : “트랜잭션 사용” )에 대해서만 이야기하는 대신 C 코드를 작성하고 실제로 다양한 옵션의 영향을 측정 하는 것이 가장 좋습니다 . 우리는 간단한 데이터로 시작할 것입니다.

• 토론토시의 전체 운송 일정에 대한 28MB의 탭으로 구분 된 텍스트 파일 (약 865,000 개의 레코드)
• 내 테스트 컴퓨터는 Windows XP를 실행하는 3.60GHz P4입니다.
• 이 코드는 Visual C ++ 2005에서 “완전 최적화”(/ Ox) 및 Favor Fast Code (/ Ot)와 함께 “릴리스”로 컴파일됩니다 .
• 테스트 애플리케이션에 직접 컴파일 된 SQLite “Amalgamation”을 사용하고 있습니다. 내가 가지고있는 SQLite 버전은 조금 오래되었지만 (3.6.7)이 결과가 최신 릴리스와 비슷할 것으로 생각됩니다 (그렇지 않으면 의견을 남겨주세요).

코드를 작성하자!

코드 : 텍스트 파일을 한 줄씩 읽고 문자열을 값으로 분할 한 다음 SQLite 데이터베이스에 데이터를 삽입하는 간단한 C 프로그램입니다. 이 “기본”버전의 코드에서는 데이터베이스가 생성되지만 실제로 데이터를 삽입하지는 않습니다.

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

제어”

코드를있는 그대로 실행하면 실제로 데이터베이스 작업이 수행되지 않지만 원시 C 파일 I / O 및 문자열 처리 작업이 얼마나 빠른지 알 수 있습니다.

0.94 초 내에 864913 개의 레코드를 가져 왔습니다.

큰! 실제로 인서트를 수행하지 않으면 초당 920,000 개의 인서트를 수행 할 수 있습니다.

“가장 최악의 시나리오”

파일에서 읽은 값을 사용하여 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 코드로 컴파일되고 모든 삽입이 자체 트랜잭션에서 발생하기 때문에 속도가 느려집니다. 얼마나 느려?

9933.61 초 내에 864913 개의 레코드를 가져 왔습니다.

이케! 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);

38.03 초 내에 864913 개의 레코드를 가져 왔습니다.

그게 낫다. 한 번의 트랜잭션으로 모든 인서트를 포장하면 초당 23,000 개의 인서트로 성능이 향상되었습니다 .

준비된 진술 사용

트랜잭션 사용은 크게 개선되었지만 동일한 SQL을 반복해서 사용하는 경우 모든 삽입에 대해 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, "\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;

16.27 초 내에 864913 개의 레코드를 가져 왔습니다.

좋은! 이 조금 더 코드 (전화하는 것을 잊지 마세요 비트의 sqlite3_clear_bindings과 sqlite3_reset), 그러나 우리는 더 이상 우리의 성능을 두 배로 초당 53,000 삽입합니다.

PRAGMA 동기식 = OFF

기본적으로 SQLite는 OS 수준 쓰기 명령을 실행 한 후 일시 중지됩니다. 이를 통해 데이터가 디스크에 기록됩니다. 을 설정 synchronous = OFF하여 SQLite에 데이터를 OS로 전달하여 쓰기를 계속하도록 지시합니다. 데이터가 플래터에 기록되기 전에 컴퓨터에 치명적인 충돌 (또는 정전)이 발생하면 데이터베이스 파일이 손상 될 수 있습니다.

/* 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 = 메모리

평가하여 롤백 저널을 메모리에 저장하십시오 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);

13.50 초 내에 864913 개의 레코드를 가져 왔습니다.

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

12.00 초 내에 864913 개의 레코드를 가져 왔습니다.

환상적인! 초당 72,000 개의 인서트 를 수행 할 수 있습니다.

인 메모리 데이터베이스 사용

킥을 위해 이전의 모든 최적화를 기반으로하고 데이터베이스 파일 이름을 재정 의하여 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, "\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);

참고 : 실제 데이터베이스 파일을 다시 사용합니다. 인 메모리 데이터베이스는 빠르지 만 반드시 실용적이지는 않습니다.

8.94 초 내에 864913 개의 레코드를 가져 왔습니다.

매개 변수 바인딩에 사용 된 문자열 처리 코드를 약간 리팩토링하면 초당 96,700 개의 삽입 을 수행 할 수있었습니다 . 나는 이것이 매우 빠르다고 말하는 것이 안전하다고 생각합니다 . 다른 변수 (예 : 페이지 크기, 색인 작성 등)를 조정하기 시작하면 이것이 벤치 마크가됩니다.

요약 (지금까지)

나는 당신이 여전히 나와 함께 있기를 바랍니다! 이 길을 시작한 이유는 대량 삽입 성능이 SQLite에 따라 크게 다르기 때문에 운영 속도를 높이기 위해 어떤 변경이 필요한지 항상 명확하지는 않기 때문입니다. 동일한 컴파일러 (및 컴파일러 옵션), 동일한 버전의 SQLite 및 동일한 데이터를 사용하여 코드와 SQLite 사용을 최적화하여 초당 85 삽입의 최악의 시나리오에서 초당 96,000 이상의 삽입으로 전환합니다!

INDEX 작성 후 INSERT vs. INSERT 작성 후 INDEX 작성

SELECT성능 측정을 시작하기 전에 인덱스를 만들 것임을 알고 있습니다. 아래 답변 중 하나에서 대량 삽입을 수행 할 때 데이터를 삽입 한 후 색인을 만드는 것이 더 빠릅니다 (먼저 색인을 만든 다음 데이터를 삽입하는 것과는 대조적으로). 해보자:

인덱스 생성 후 데이터 삽입

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

18.13 초 내에 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 개의 레코드를 가져 왔습니다.

예상대로, 하나의 열이 색인화되면 대량 삽입이 느려지지만 데이터가 삽입 된 후 색인이 작성되면 차이가 발생합니다. 인덱스가없는 기준은 초당 96,000 개의 인서트입니다. 먼저 인덱스를 생성 한 다음 데이터를 삽입하면 초당 47,700 개의 삽입이 생성되는 반면, 데이터를 먼저 삽입 한 다음 인덱스를 생성하면 초당 63,300 개의 삽입이 생성됩니다.

다른 시나리오에 대한 제안을 기쁘게 생각합니다 … 곧 SELECT 쿼리에 대한 유사한 데이터를 컴파일 할 것입니다.

답변

SELECT COUNT(*) FROM table

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

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

int offset = thread_index * limit