카테고리 보관물: C#

C#

SQLite의 초당 INSERT 성능 향상 char *

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_bindingssqlite3_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 쿼리에 대한 유사한 데이터를 컴파일 할 것입니다.



답변

몇 가지 팁 :

  1. 트랜잭션에 삽입 / 업데이트를 넣습니다.
  2. 이전 버전의 SQLite의 경우 덜 편집증적인 저널 모드 ( pragma journal_mode)를 고려하십시오 . 이 NORMAL다음이 OFF너무 OS가 충돌하는 경우 가능성이 손상지고 데이터베이스에 대해 걱정하지 않는 경우 크게 속도 삽입 높일 수있다. 응용 프로그램이 충돌하면 데이터가 정상이어야합니다. 최신 버전에서는 OFF/MEMORY설정이 응용 프로그램 수준 충돌에 안전하지 않습니다.
  3. 페이지 크기로 재생하면 차이가 있습니다 ( PRAGMA page_size). 더 큰 페이지 크기를 가지면 더 큰 페이지가 메모리에 유지되므로 읽기 및 쓰기 속도가 약간 빨라집니다. 데이터베이스에 더 많은 메모리가 사용됩니다.
  4. 색인이있는 경우 CREATE INDEX모든 삽입 작업을 수행 한 후 전화 를 고려 하십시오. 이것은 색인을 작성하고 삽입을 수행하는 것보다 훨씬 빠릅니다.
  5. 쓰기가 완료 될 때 전체 데이터베이스가 잠기고 여러 판독기가 가능하더라도 쓰기가 잠기므로 SQLite에 동시에 액세스 할 수있는 경우 매우주의해야합니다. 최신 SQLite 버전에 WAL이 추가되어 다소 개선되었습니다.
  6. 공간을 절약하십시오. 작은 데이터베이스는 더 빠릅니다. 예를 들어, 키 값 쌍이있는 INTEGER PRIMARY KEY경우 키를 가능 하면 키로 만들어 테이블의 내재 된 고유 행 번호 열을 대체하십시오.
  7. 여러 스레드를 사용하는 경우 공유 페이지 캐시를 사용하여 로드 된 페이지를 스레드간에 공유 할 수 있으므로 값 비싼 I / O 호출을 피할 수 있습니다.
  8. 사용하지 마십시오 !feof(file)!

나는 또한 여기여기에 비슷한 질문 을 했다 .


답변

해당 인서트 SQLITE_STATIC대신 사용하십시오 SQLITE_TRANSIENT.

SQLITE_TRANSIENT 반환하기 전에 SQLite가 문자열 데이터를 복사하게합니다.

SQLITE_STATIC주어진 메모리 주소는 쿼리가 수행 될 때까지 유효합니다 (이 루프에서는 항상 그렇습니다). 이를 통해 루프 당 여러 할당, 복사 및 할당 해제 작업을 줄일 수 있습니다. 아마 큰 개선.


답변

피하십시오 sqlite3_clear_bindings(stmt).

테스트의 코드는 바인딩이 충분할 때마다 바인딩을 설정합니다.

SQLite 문서 의 C API 소개 는 다음과 같이 말합니다.

sqlite3_step () 을 처음으로 호출하기 전에 또는 sqlite3_reset () 직후 에 응용 프로그램은 sqlite3_bind () 인터페이스를 호출하여 값을 매개 변수에 첨부 할 수 있습니다
. sqlite3_bind () 에 대한 각 호출 은 동일한 매개 변수의 이전 바인딩을 대체합니다.

sqlite3_clear_bindings단순히 바인딩을 설정하는 것 외에도 호출해야한다고 말하는 문서에는 아무것도 없습니다 .

자세한 내용 : avoid_sqlite3_clear_bindings ()


답변

벌크 인서트

이 게시물과 스택 오버플로 질문에서 영감을 얻었습니다 .SQLite 데이터베이스에 한 번에 여러 행을 삽입 할 수 있습니까? -첫 Git 저장소를 게시했습니다 .

https://github.com/rdpoor/CreateOrUpdate

이는 ActiveRecord 배열을 MySQL , SQLite 또는 PostgreSQL 데이터베이스에 대량로드 합니다. 기존 레코드를 무시하거나 덮어 쓰거나 오류를 발생시키는 옵션이 포함되어 있습니다. 필자의 기초 벤치 마크는 순차적 쓰기 (YMMV)에 비해 속도가 10 배 향상되었습니다.

대용량 데이터 세트를 자주 가져와야하는 프로덕션 코드에서 사용하고 있으며 매우 만족합니다.


답변

INSERT / UPDATE 문을 청크 할 수 있으면 대량 가져 오기가 가장 잘 수행되는 것 같습니다 . 10,000 개 정도의 값은 YMMV … 몇 행만있는 테이블에서 잘 작동했습니다.


답변

읽기에만 관심이있는 경우 다소 빠른 (그러나 오래된 데이터를 읽을 수 있음) 버전은 여러 스레드 (스레드 당 연결)의 여러 연결에서 읽는 것입니다.

먼저 표에서 항목을 찾으십시오.

SELECT COUNT(*) FROM table

그런 다음 페이지를 읽습니다 (LIMIT / OFFSET).

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

다음과 같이 스레드 당 계산되는 위치는 다음과 같습니다.

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

각 스레드마다 :

int offset = thread_index * limit

우리의 작은 (200mb) db의 경우 50-75 %의 속도가 향상되었습니다 (Windows 7의 경우 3.8.0.2 64 비트). 우리 테이블은 정규화되지 않았습니다 (1000-1500 열, 약 100,000 개 이상의 행).

스레드가 너무 많거나 너무 적 으면 벤치마킹하고 프로필을 작성해야합니다.

또한 우리를 위해 SHAREDCACHE는 성능을 느리게 만들었으므로 PRIVATECACHE를 수동으로 넣었습니다.


답변

cache_size를 더 높은 값으로 올릴 때까지 트랜잭션에서 이익을 얻지 못했습니다. PRAGMA cache_size=10000;