Câu hỏi Cải thiện hiệu suất INSERT mỗi giây của SQLite?


Tối ưu hóa SQLite là khó khăn. Hiệu suất chèn số lượng lớn của ứng dụng C có thể thay đổi từ 85 lần chèn trên giây đến hơn 96.000 lần chèn mỗi giây!

Lý lịch: Chúng tôi đang sử dụng SQLite như một phần của ứng dụng dành cho máy tính để bàn. Chúng tôi có một lượng lớn dữ liệu cấu hình được lưu trữ trong các tệp XML được phân tích cú pháp và được tải vào cơ sở dữ liệu SQLite để xử lý thêm khi ứng dụng được khởi tạo. SQLite là lý tưởng cho tình huống này vì nó nhanh, nó không yêu cầu cấu hình chuyên biệt, và cơ sở dữ liệu được lưu trữ trên đĩa như một tệp duy nhất.

Lý do:  Ban đầu tôi đã thất vọng với màn biểu diễn mà tôi đã thấy. Nó chỉ ra rằng hiệu suất của SQLite có thể thay đổi đáng kể (cả cho chèn và chọn số lượng lớn) tùy thuộc vào cách cấu hình cơ sở dữ liệu và cách bạn đang sử dụng API. Đó không phải là vấn đề nhỏ nhặt để tìm hiểu tất cả các tùy chọn và kỹ thuật là gì, vì vậy tôi nghĩ rằng nên thận trọng để tạo mục wiki cộng đồng này để chia sẻ kết quả với trình đọc Stack Overflow để tiết kiệm cho người khác những rắc rối của các cuộc điều tra tương tự.

Cuộc thí nghiệm: Thay vì chỉ nói về mẹo hiệu suất theo nghĩa chung (tức là "Sử dụng giao dịch!"), Tôi nghĩ tốt nhất là viết một số mã C và thực sự đo lường tác động của các tùy chọn khác nhau. Chúng ta sẽ bắt đầu với một số dữ liệu đơn giản:

  • Tệp văn bản TAB phân tách 28 MB (khoảng 865.000 bản ghi) của lịch trình vận chuyển hoàn chỉnh cho thành phố Toronto
  • Máy thử nghiệm của tôi là 3.60 GHz P4 chạy Windows XP.
  • Mã được biên dịch với Visual C ++ 2005 là "Phát hành" với "Tối ưu hóa đầy đủ" (/ Ox) và Mã Fast (/ Ot).
  • Tôi đang sử dụng "Amalgamation" SQLite, được biên dịch trực tiếp vào ứng dụng thử nghiệm của tôi. Các phiên bản SQLite tôi xảy ra có là một chút cũ (3.6.7), nhưng tôi nghi ngờ những kết quả này sẽ được so sánh với phiên bản mới nhất (xin vui lòng để lại một bình luận nếu bạn nghĩ khác).

Hãy viết một số mã!

Mật mã: Một chương trình C đơn giản đọc tệp văn bản theo từng dòng, chia chuỗi thành các giá trị và sau đó sẽ chèn dữ liệu vào cơ sở dữ liệu SQLite. Trong phiên bản "cơ sở" này của mã, cơ sở dữ liệu được tạo, nhưng chúng tôi sẽ không thực sự chèn dữ liệu:

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

"Điều khiển"

Việc chạy mã as-is không thực sự thực hiện bất kỳ hoạt động cơ sở dữ liệu nào, nhưng nó sẽ cho chúng ta một ý tưởng về việc các tệp I / O và các hoạt động xử lý chuỗi thô của C hoạt động nhanh như thế nào.

Đã nhập 864913 bản ghi trong 0,94   giây

Tuyệt quá! Chúng tôi có thể thực hiện 920.000 lần chèn mỗi giây, miễn là chúng tôi không thực sự thực hiện bất kỳ khoản chèn nào :-)


"Trường hợp xấu nhất-Kịch bản"

Chúng ta sẽ tạo chuỗi SQL bằng cách sử dụng các giá trị được đọc từ tệp và gọi hoạt động SQL đó bằng 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);

Điều này sẽ chậm vì SQL sẽ được biên dịch thành mã VDBE cho mỗi lần chèn và mỗi lần chèn sẽ xảy ra trong giao dịch của riêng nó. Làm thế nào chậm?

Đã nhập 864913 bản ghi trong 9933,61   giây

Yikes! 2 giờ 45 phút! Đó chỉ là 85 lần chèn mỗi giây.

Sử dụng giao dịch

Theo mặc định, SQLite sẽ đánh giá mọi câu lệnh INSERT / UPDATE trong một giao dịch duy nhất. Nếu thực hiện một số lượng lớn chèn, nó được khuyến khích để quấn hoạt động của bạn trong một giao dịch:

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

Đã nhập 864913 bản ghi trong 38.03   giây

Cái đó tốt hơn. Chỉ cần gói tất cả các chèn của chúng tôi trong một giao dịch duy nhất đã cải thiện hiệu suất của chúng tôi thành 23.000 lần chèn mỗi giây.

Sử dụng một tuyên bố đã chuẩn bị

Sử dụng một giao dịch là một cải tiến lớn, nhưng việc biên dịch lại câu lệnh SQL cho mỗi lần chèn không có ý nghĩa nếu chúng ta sử dụng cùng một câu lệnh SQL over-and-over. Hãy sử dụng sqlite3_prepare_v2 để biên dịch câu lệnh SQL của chúng ta một lần và sau đó liên kết các tham số của chúng ta với câu lệnh đó bằng cách sử dụng 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;

Đã nhập 864913 bản ghi trong 16,27   giây

Tốt đẹp! Có nhiều mã hơn một chút (đừng quên gọi sqlite3_clear_bindings và sqlite3_reset), nhưng chúng tôi đã tăng gấp đôi hiệu suất của mình lên 53.000 lần chèn mỗi giây.

PRAGMA đồng bộ = TẮT

Theo mặc định, SQLite sẽ tạm dừng sau khi phát hành lệnh viết mức OS. Điều này đảm bảo rằng dữ liệu được ghi vào đĩa. Bằng cách thiết lập synchronous = OFF, chúng tôi đang hướng dẫn SQLite chỉ đơn giản là chuyển dữ liệu sang hệ điều hành để viết và sau đó tiếp tục. Có khả năng tệp cơ sở dữ liệu có thể bị hỏng nếu máy tính bị lỗi thảm khốc (hoặc mất điện) trước khi dữ liệu được ghi vào đĩa:

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

Đã nhập 864913 bản ghi trong 12,41   giây

Các cải tiến hiện nhỏ hơn nhưng chúng tôi 69.600 lần chèn mỗi giây.

PRAGMA journal_mode = MEMORY

Xem xét lưu trữ tạp chí rollback trong bộ nhớ bằng cách đánh giá PRAGMA journal_mode = MEMORY. Giao dịch của bạn sẽ nhanh hơn, nhưng nếu bạn mất quyền lực hoặc chương trình của bạn bị treo trong một giao dịch, cơ sở dữ liệu của bạn có thể bị bỏ lại ở trạng thái hỏng với giao dịch được hoàn tất một phần:

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

Đã nhập 864913 bản ghi trong 13,50   giây

Một chút chậm hơn so với tối ưu hóa trước đó tại 64.000 lần chèn mỗi giây.

PRAGMA đồng bộ = TẮT  PRAGMA journal_mode = MEMORY

Hãy kết hợp hai tối ưu hóa trước đó. Đó là một chút nguy hiểm hơn (trong trường hợp xảy ra sự cố), nhưng chúng tôi chỉ nhập dữ liệu (không chạy ngân hàng):

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

Nhập khẩu 864913 hồ sơ trong 12,00   giây

Tuyệt diệu! Chúng tôi có thể làm 72.000 lần chèn mỗi giây.

Sử dụng cơ sở dữ liệu trong bộ nhớ

Chỉ cần cho đá, chúng ta hãy xây dựng dựa trên tất cả các tối ưu hóa trước đó và xác định lại tên tệp cơ sở dữ liệu, vì vậy chúng tôi đang làm việc hoàn toàn trong RAM:

#define DATABASE ":memory:"

Đã nhập 864913 bản ghi trong 10,94   giây

Nó không phải là siêu thực tế để lưu trữ cơ sở dữ liệu của chúng tôi trong RAM, nhưng thật ấn tượng mà chúng tôi có thể thực hiện 79.000 lần chèn mỗi giây.

Mã C tái cấu trúc

Mặc dù không cụ thể là một cải tiến SQLite, tôi không thích thêm char*các hoạt động gán trong while vòng lặp. Hãy nhanh chóng cấu trúc lại mã đó để vượt qua đầu ra của strtok() trực tiếp vào sqlite3_bind_text()và để trình biên dịch cố gắng tăng tốc cho chúng tôi:

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

Lưu ý: Chúng tôi quay lại sử dụng tệp cơ sở dữ liệu thực. Cơ sở dữ liệu trong bộ nhớ nhanh, nhưng không nhất thiết phải thực tế

Đã nhập 864913 bản ghi trong 8.94   giây

Việc tái cấu trúc lại chuỗi mã xử lý được sử dụng trong ràng buộc tham số của chúng ta đã cho phép chúng ta thực hiện 96.700 lần chèn mỗi giây. Tôi nghĩ rằng nó an toàn để nói rằng đây là rất nhanh. Khi chúng tôi bắt đầu tinh chỉnh các biến khác (tức là kích thước trang, tạo chỉ mục, v.v.), đây sẽ là điểm chuẩn của chúng tôi.


Tóm tắt (cho đến nay)

Tôi hy vọng bạn vẫn còn với tôi! Lý do chúng tôi bắt đầu xuống con đường này là hiệu suất chèn hàng loạt thay đổi rất nhiều với SQLite và không phải lúc nào cũng rõ ràng những thay đổi cần phải được thực hiện để tăng tốc hoạt động của chúng tôi. Sử dụng cùng một trình biên dịch (và các tùy chọn trình biên dịch), cùng một phiên bản của SQLite và cùng một dữ liệu chúng tôi đã tối ưu hóa mã của chúng tôi và việc sử dụng SQLite của chúng tôi để đi từ trường hợp xấu nhất là 85 lần chèn / giây đến hơn 96.000 lần chèn mỗi giây!


TẠO INDEX rồi nhấn INSERT và INSERT rồi TẠO INDEX

Trước khi chúng tôi bắt đầu đo SELECT hiệu suất, chúng tôi biết rằng chúng tôi sẽ tạo các chỉ mục. Nó được đề xuất trong một trong những câu trả lời dưới đây khi thực hiện chèn số lượng lớn, nó sẽ nhanh hơn để tạo chỉ mục sau khi dữ liệu được chèn vào (trái ngược với việc tạo chỉ mục trước rồi chèn dữ liệu). Hãy thử:

Tạo chỉ mục rồi chèn dữ liệu

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

Đã nhập các bản ghi 864913 vào năm 18.13   giây

Chèn dữ liệu rồi tạo chỉ mục

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

Đã nhập 864913 bản ghi trong 13.66   giây

Như dự kiến, chèn hàng loạt sẽ chậm hơn nếu một cột được lập chỉ mục, nhưng nó sẽ tạo sự khác biệt nếu chỉ mục được tạo sau khi dữ liệu được chèn vào. Đường cơ sở không có chỉ số của chúng tôi là 96.000 lần chèn mỗi giây. Tạo chỉ mục đầu tiên sau đó chèn dữ liệu cho chúng ta 47.700 lần chèn mỗi giây, trong khi chèn dữ liệu đầu tiên sau đó tạo chỉ mục cho chúng ta 63.300 lần chèn mỗi giây.


Tôi sẵn sàng đưa ra các đề xuất cho các kịch bản khác để thử ... Và sẽ sớm biên dịch dữ liệu tương tự cho các truy vấn SELECT.


2573


gốc


Điểm tốt! Trong trường hợp của chúng tôi, chúng tôi đang xử lý khoảng 1,5 triệu cặp khóa / giá trị được đọc từ các tệp văn bản XML và CSV thành 200k bản ghi. Nhỏ bằng cách so sánh với cơ sở dữ liệu chạy các trang web như SO - nhưng đủ lớn để điều chỉnh hiệu suất SQLite trở nên quan trọng. - Mike Willekes
"Chúng tôi có một lượng lớn dữ liệu cấu hình được lưu trữ trong các tệp XML được phân tích cú pháp và được nạp vào cơ sở dữ liệu SQLite để xử lý thêm khi ứng dụng được khởi tạo." tại sao bạn không giữ tất cả mọi thứ trong cơ sở dữ liệu sqlite ở nơi đầu tiên, thay vì lưu trữ trong XML và sau đó tải tất cả mọi thứ tại thời gian khởi tạo? - CAFxX
Bạn đã cố gắng không gọi điện thoại chưa sqlite3_clear_bindings(stmt);? Bạn thiết lập các ràng buộc mỗi lần thông qua đó là đủ: Trước khi gọi sqlite3_step () lần đầu tiên hoặc ngay sau khi sqlite3_reset (), ứng dụng có thể gọi một trong các giao diện sqlite3_bind () để đính kèm các giá trị vào các tham số. Mỗi cuộc gọi đến sqlite3_bind () ghi đè các ràng buộc trước trên cùng một tham số (xem: sqlite.org/cintro.html). Không có gì trong tài liệu cho hàm đó nói rằng bạn phải gọi nó. - ahcox
ahcox: ràng buộc là đến địa chỉ được trỏ vào và không phải là biến, do đó sẽ không hoạt động kể từ strtok trả về một con trỏ mới mỗi lần. Bạn sẽ phải strcpy sau mỗi cái strtok hoặc tạo mã thông báo của riêng bạn luôn luôn sao chép khi nó đọc dọc theo chuỗi. - nemetroid
Bạn đã thực hiện các phép đo lặp lại? Bốn "chiến thắng" để tránh 7 con trỏ địa phương là lạ, thậm chí giả sử một trình tối ưu hóa bối rối. - peterchen


Các câu trả lời:


Một số mẹo:

  1. Đặt chèn / cập nhật trong giao dịch.
  2. Đối với các phiên bản SQLite cũ hơn - Hãy xem xét chế độ nhật ký ít hoang tưởng hơn (pragma journal_mode). Có NORMALvà sau đó là OFF, có thể tăng đáng kể tốc độ chèn nếu bạn không quá lo lắng về cơ sở dữ liệu có thể bị hỏng nếu hệ điều hành gặp sự cố. Nếu ứng dụng của bạn gặp sự cố thì dữ liệu sẽ ổn. Lưu ý rằng trong các phiên bản mới hơn, OFF/MEMORY cài đặt không an toàn cho các sự cố cấp ứng dụng.
  3. Chơi với kích thước trang cũng tạo ra sự khác biệt (PRAGMA page_size). Việc có kích thước trang lớn hơn có thể giúp đọc và ghi nhanh hơn một chút khi các trang lớn hơn được giữ trong bộ nhớ. Lưu ý rằng nhiều bộ nhớ hơn sẽ được sử dụng cho cơ sở dữ liệu của bạn.
  4. Nếu bạn có chỉ mục, hãy cân nhắc gọi CREATE INDEXsau khi làm tất cả các chèn của bạn. Điều này nhanh hơn đáng kể so với việc tạo chỉ mục và sau đó thực hiện chèn của bạn.
  5. Bạn phải khá cẩn thận nếu bạn có quyền truy cập đồng thời vào SQLite, vì toàn bộ cơ sở dữ liệu bị khóa khi viết xong, và mặc dù nhiều người đọc có thể, viết sẽ bị khóa. Điều này đã được cải thiện một chút với việc bổ sung WAL trong các phiên bản SQLite mới hơn.
  6. Tận dụng lợi thế của tiết kiệm không gian ... cơ sở dữ liệu nhỏ hơn đi nhanh hơn. Ví dụ: nếu bạn có cặp giá trị khóa, hãy thử tạo khóa INTEGER PRIMARY KEY nếu có thể, sẽ thay thế cột số hàng duy nhất ngụ ý trong bảng.
  7. Nếu bạn đang sử dụng nhiều chủ đề, bạn có thể thử sử dụng bộ nhớ cache trang được chia sẻ, cho phép tải các trang được chia sẻ giữa các luồng, có thể tránh các cuộc gọi I / O đắt tiền.
  8. Không sử dụng !feof(file)!

Tôi cũng đã hỏi những câu hỏi tương tự đây và đây.


672



Tài liệu không biết PRAGMA journal_mode NORMAL sqlite.org/pragma.html#pragma_journal_mode - OneWorld
Đã lâu rồi, các đề xuất của tôi đã áp dụng cho các phiên bản cũ hơn trước khi một WAL được giới thiệu. Có vẻ như DELETE là cài đặt bình thường mới và giờ đây cũng có cài đặt TẮT và BỘ NHỚ. Tôi cho rằng OFF / MEMORY sẽ cải thiện hiệu suất ghi tại các chi phí của tính toàn vẹn cơ sở dữ liệu, và OFF vô hiệu hóa rollbacks hoàn toàn. - Snazzer
cho # 7, bạn có ví dụ về cách bật bộ nhớ cache trang được chia sẻ bằng cách sử dụng c # system.data.sqlite wrapper? - Aaron Hudon
# 4 mang lại những kỷ niệm cũ kỷ nguyên - Có ít nhất một trường hợp trở lại trong thời gian trước đó, nơi thả một chỉ số trước khi một nhóm thêm và tái tạo nó sau đó sped chèn đáng kể. Có thể vẫn làm việc nhanh hơn trên các hệ thống hiện đại cho một số bổ sung mà bạn biết bạn có quyền truy cập duy nhất vào bảng trong giai đoạn này. - Bill K


Thử sử dụng SQLITE_STATIC thay vì SQLITE_TRANSIENT cho những cái chèn đó.

SQLITE_TRANSIENT sẽ khiến SQLite sao chép dữ liệu chuỗi trước khi trở về.

SQLITE_STATIC nói với nó rằng địa chỉ bộ nhớ bạn đã cho nó sẽ có hiệu lực cho đến khi truy vấn được thực hiện (mà trong vòng lặp này luôn luôn là trường hợp). Điều này sẽ giúp bạn tiết kiệm một số phân bổ, sao chép và deallocate hoạt động cho mỗi vòng lặp. Có thể là một cải tiến lớn.


102





Tránh sqlite3_clear_bindings (stmt);

Mã trong thử nghiệm thiết lập các ràng buộc mỗi lần thông qua đó là đủ.

Giới thiệu API C từ các tài liệu SQLite nói

Trước khi gọi đến sqlite3_step () lần đầu tiên hoặc ngay lập tức   sau sqlite3_reset (), ứng dụng có thể gọi một trong các   sqlite3_bind () giao diện để đính kèm các giá trị cho các tham số. Mỗi   call to sqlite3_bind () ghi đè các ràng buộc trước trên cùng một tham số

(xem: sqlite.org/cintro.html). Không có gì trong tài liệu cho chức năng đó nói rằng bạn phải gọi nó ngoài việc chỉ cần thiết lập các ràng buộc.

Thêm chi tiết: http://www.hoogli.com/blogs/micro/index.html#Avoid_sqlite3_clear_bindings ()


80



Tuyệt đối đúng: "Trái ngược với trực giác của nhiều, sqlite3_reset () không đặt lại các ràng buộc trên một tuyên bố chuẩn bị. Sử dụng thường trình này để thiết lập lại tất cả các tham số máy chủ để NULL." - - sqlite.org/c3ref/clear_bindings.html - Francis Straccia


Trên chèn số lượng lớn

Lấy cảm hứng từ bài đăng này và câu hỏi về Stack Overflow đã dẫn tôi đến đây - Có thể chèn nhiều hàng tại một thời điểm trong cơ sở dữ liệu SQLite không? - Tôi đã đăng bài đầu tiên của mình Git kho:

https://github.com/rdpoor/CreateOrUpdate

tải hàng loạt các mảng ActiveRecords vào MySQL, SQLite hoặc PostgreSQL cơ sở dữ liệu. Nó bao gồm một tùy chọn để bỏ qua các bản ghi hiện có, ghi đè lên chúng hoặc gây ra lỗi. Điểm chuẩn thô sơ của tôi cho thấy cải thiện tốc độ gấp 10 lần so với viết tuần tự - YMMV.

Tôi đang sử dụng nó trong mã sản xuất, nơi tôi thường xuyên cần phải nhập dữ liệu lớn, và tôi khá hài lòng với nó.


47



@Jess: Nếu bạn theo liên kết, bạn sẽ thấy rằng anh ấy có nghĩa là cú pháp chèn hàng loạt. - Alix Axel


Nhập hàng loạt dường như hoạt động tốt nhất nếu bạn có thể chia nhỏ INSERT / UPDATE các câu lệnh. Một giá trị của 10.000 hoặc hơn đã làm việc tốt cho tôi trên một bảng chỉ với một vài hàng, YMMV ...


40



Bạn muốn điều chỉnh x = 10.000 để x = cache [= cache_size * page_size] / kích thước trung bình của chèn của bạn. - Alix Axel


Nếu bạn chỉ quan tâm đến việc đọc, phần mềm nhanh hơn (nhưng có thể đọc dữ liệu cũ) là đọc từ nhiều kết nối từ nhiều luồng (kết nối cho mỗi luồng).

Đầu tiên tìm các mục, trong bảng:

 SELECT COUNT(*) FROM table

sau đó đọc trong các trang (LIMIT / OFFSET)

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

ở đâu và được tính theo chủ đề, như sau:

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

cho mỗi chủ đề:

int offset = thread_index * limit

Đối với db nhỏ (200mb) của chúng tôi, tốc độ này tăng 50-75% (3.8.0.2 64 bit trên Windows 7). Bảng của chúng tôi không được chuẩn hóa nhiều (1000-1500 cột, khoảng 100.000 hàng hoặc nhiều hơn).

Quá nhiều hoặc quá ít chủ đề sẽ không làm điều đó, bạn cần phải chuẩn và hồ sơ chính mình.

Ngoài ra đối với chúng tôi, SHAREDCACHE đã thực hiện hiệu suất chậm hơn, vì vậy tôi đã đặt PRIVATECACHE theo cách thủ công (vì nó đã được bật trên toàn cầu cho chúng tôi)


32





Tôi không nhận được bất kỳ lợi ích nào từ các giao dịch cho đến khi tôi tăng cache_size lên một giá trị cao hơn, tức là PRAGMA cache_size=10000;


20