ํƒœ๊ทธ ๋ณด๊ด€๋ฌผ: sqlite

sqlite

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_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 ์ฟผ๋ฆฌ์— ๋Œ€ํ•œ ์œ ์‚ฌํ•œ ๋ฐ์ดํ„ฐ๋ฅผ ์ปดํŒŒ์ผ ํ•  ๊ฒƒ์ž…๋‹ˆ๋‹ค.



๋‹ต๋ณ€

๋ช‡ ๊ฐ€์ง€ ํŒ :

  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;