Pertanyaan Meningkatkan kinerja INSERT per detik dari SQLite?


Mengoptimalkan SQLite itu rumit. Kinerja insert massal dari aplikasi C dapat bervariasi dari 85 sisipan per detik ke lebih dari 96.000 sisipan per detik!

Latar Belakang: Kami menggunakan SQLite sebagai bagian dari aplikasi desktop. Kami memiliki sejumlah besar data konfigurasi yang disimpan dalam file XML yang diurai dan dimuat ke dalam database SQLite untuk diproses lebih lanjut saat aplikasi diinisialisasi. SQLite sangat ideal untuk situasi ini karena cepat, tidak memerlukan konfigurasi khusus, dan database disimpan pada disk sebagai file tunggal.

Alasan:  Awalnya saya kecewa dengan pertunjukan yang saya lihat. Ternyata kinerja SQLite dapat bervariasi secara signifikan (baik untuk insert massal dan memilih) tergantung pada bagaimana database dikonfigurasi dan bagaimana Anda menggunakan API. Itu bukan masalah sepele untuk mencari tahu apa semua opsi dan teknik itu, jadi saya pikir itu bijaksana untuk membuat entri wiki komunitas ini untuk berbagi hasil dengan pembaca Stack Overflow untuk menyelamatkan orang lain kesulitan penyelidikan yang sama.

Percobaan: Daripada hanya berbicara tentang kiat kinerja dalam pengertian umum (mis. "Gunakan transaksi!"), Saya pikir lebih baik untuk menulis beberapa kode C dan sebenarnya mengukur dampak dari berbagai opsi. Kita akan mulai dengan beberapa data sederhana:

  • A 28 MB file teks TAB-delimited (sekitar 865.000 catatan) dari jadwal transit lengkap untuk kota Toronto
  • Mesin uji saya adalah P4 3,60 GHz yang menjalankan Windows XP.
  • Kode dikompilasi dengan Visual C ++ 2005 sebagai "Rilis" dengan "Pengoptimalan Penuh" (/ Ox) dan Kode Cepat Favour (/ Ot).
  • Saya menggunakan SQLite "Amalgamation", dikompilasi langsung ke aplikasi pengujian saya. Versi SQLite yang saya miliki sedikit lebih tua (3.6.7), tetapi saya menduga hasil ini akan sebanding dengan rilis terbaru (silakan tinggalkan komentar jika Anda berpikir sebaliknya).

Mari menulis beberapa kode!

Kode: Sebuah program C sederhana yang membaca file teks baris-demi-baris, membagi string ke dalam nilai-nilai dan kemudian akan memasukkan data ke dalam database SQLite. Dalam versi "awal" dari kode ini, basis data dibuat, tetapi kami sebenarnya tidak akan memasukkan data:

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

Kontrol"

Menjalankan kode as-is tidak benar-benar melakukan operasi database, tetapi akan memberi kita gambaran tentang seberapa cepat file C mentah I / O dan operasi pemrosesan string.

Mengimpor 864913 catatan di 0,94   detik

Besar! Kita dapat melakukan 920.000 sisipan per detik, asalkan kita tidak benar-benar melakukan penyisipan apa pun :-)


The "Worst-Case-Scenario"

Kita akan menghasilkan string SQL menggunakan nilai yang dibaca dari file dan menjalankan operasi SQL menggunakan 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);

Ini akan menjadi lambat karena SQL akan dikompilasi menjadi kode VDBE untuk setiap sisipan dan setiap sisipan akan terjadi dalam transaksi sendiri. Seberapa lambat?

Impor 864913 catatan di 9933.61   detik

Astaga! 2 jam dan 45 menit! Hanya itu saja 85 sisipan per detik.

Menggunakan Transaksi

Secara default, SQLite akan mengevaluasi setiap pernyataan INSERT / UPDATE dalam transaksi unik. Jika melakukan banyak insert, Anda disarankan untuk membungkus operasi Anda dalam sebuah transaksi:

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

Impor 864913 catatan dalam 38.03   detik

Itu lebih baik. Cukup membungkus semua sisipan kami dalam satu transaksi meningkatkan kinerja kami 23.000 sisipan per detik.

Menggunakan Pernyataan yang Sudah Disiapkan

Menggunakan transaksi adalah peningkatan besar, tetapi mengkompilasi ulang pernyataan SQL untuk setiap sisipan tidak masuk akal jika kita menggunakan SQL yang sama secara berlebihan. Ayo gunakan sqlite3_prepare_v2 untuk mengkompilasi pernyataan SQL kami sekali dan kemudian mengikat parameter kami ke pernyataan itu 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;

Impor 864913 catatan dalam 16.27   detik

Bagus! Ada kode sedikit lebih (jangan lupa untuk menelepon sqlite3_clear_bindings dan sqlite3_reset), tetapi kami telah meningkatkan kinerja kami lebih dari dua kali lipat 53.000 sisipan per detik.

PRAGMA sinkron = MATI

Secara default, SQLite akan berhenti setelah mengeluarkan perintah tulis tingkat-OS. Ini menjamin bahwa data ditulis ke disk. Dengan pengaturan synchronous = OFF, kami menginstruksikan SQLite untuk menyerahkan data ke OS untuk ditulis dan kemudian melanjutkan. Ada kemungkinan file database menjadi rusak jika komputer mengalami crash katastropik (atau kegagalan daya) sebelum data ditulis ke 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);

Mengimpor 86.449 catatan dalam 12.41   detik

Peningkatannya sekarang lebih kecil, tetapi kami siap melakukannya 69.600 sisipan per detik.

PRAGMA journal_mode = MEMORY

Pertimbangkan menyimpan jurnal rollback di memori dengan mengevaluasi PRAGMA journal_mode = MEMORY. Transaksi Anda akan lebih cepat, tetapi jika Anda kehilangan daya atau program Anda macet selama transaksi, basis data Anda dapat dibiarkan dalam keadaan korup dengan transaksi yang diselesaikan sebagian:

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

Mengimpor 864913 catatan di 13,50   detik

Sedikit lebih lambat dari pengoptimalan sebelumnya pada 64.000 sisipan per detik.

PRAGMA sinkron = MATI dan PRAGMA journal_mode = MEMORY

Mari gabungkan dua optimisasi sebelumnya. Ini sedikit lebih berisiko (jika terjadi crash), tetapi kami hanya mengimpor data (bukan menjalankan bank):

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

Mengimpor 864913 catatan dalam 12.00   detik

Fantastis! Kami bisa melakukannya 72.000 sisipan per detik.

Menggunakan Database Dalam Memori

Hanya untuk tendangan, mari kita membangun semua optimasi sebelumnya dan mendefinisikan ulang nama file database jadi kita bekerja sepenuhnya dalam RAM:

#define DATABASE ":memory:"

Mengimpor 864913 catatan di 10.94   detik

Ini tidak super-praktis untuk menyimpan basis data kami dalam RAM, tetapi sangat mengesankan bahwa kami dapat melakukan 79.000 sisipan per detik.

Refactoring Kode C

Meskipun tidak secara khusus peningkatan SQLite, saya tidak suka ekstra char*operasi penugasan di while lingkaran. Mari kita cepat refactor kode itu untuk melewatkan output strtok() langsung ke sqlite3_bind_text(), dan biarkan compiler mencoba mempercepat untuk kami:

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

Catatan: Kami kembali menggunakan file database nyata. Database dalam memori cepat, tetapi tidak harus praktis

Impor 864913 catatan di 8.94   detik

Sedikit refactoring ke kode pengolah string yang digunakan dalam pengikatan parameter kami telah memungkinkan kami untuk melakukan 96.700 sisipan per detik. Saya pikir aman untuk mengatakan bahwa ini banyak cepat. Saat kami mulai mengubah variabel lain (yaitu ukuran laman, pembuatan indeks, dll.) Ini akan menjadi patokan kami.


Ringkasan (sejauh ini)

Aku harap kamu masih bersamaku! Alasan kami memulai jalan ini adalah bahwa kinerja penyisipan massal sangat bervariasi dengan SQLite, dan tidak selalu jelas perubahan apa yang perlu dilakukan untuk mempercepat operasi kami. Menggunakan kompiler yang sama (dan opsi kompilator), versi SQLite yang sama dan data yang sama kami telah mengoptimalkan kode kami dan penggunaan SQLite kami untuk pergi dari skenario terburuk 85 sisipan per detik ke lebih dari 96.000 sisipan per detik!


BUAT INDEX kemudian INSERT vs. INSERT kemudian BUAT INDEX

Sebelum kita mulai mengukur SELECT kinerja, kita tahu bahwa kita akan membuat indeks. Sudah disarankan di salah satu jawaban di bawah ini bahwa ketika melakukan insert massal, lebih cepat untuk membuat indeks setelah data telah dimasukkan (sebagai lawan untuk menciptakan indeks pertama kemudian memasukkan data). Mari mencoba:

Buat Indeks lalu Sisipkan Data

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

Impor 864913 catatan di 18.13   detik

Masukkan Data lalu Buat Indeks

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

Impor 864913 catatan di 13.66   detik

Seperti yang diharapkan, bulk-inserts lebih lambat jika satu kolom diindeks, tetapi itu membuat perbedaan jika indeks dibuat setelah data dimasukkan. Data dasar tanpa indeks kami adalah 96.000 sisipan per detik. Menciptakan indeks pertama kemudian memasukkan data memberi kita 47.700 sisipan per detik, sedangkan memasukkan data pertama kemudian membuat indeks memberi kita 63.300 sisipan per detik.


Saya dengan senang hati akan menerima saran untuk skenario lain untuk mencoba ... Dan akan mengkompilasi data serupa untuk query SELECT segera.


2568


asal


Jawaban:


Beberapa kiat:

  1. Masukkan sisipan / pembaruan dalam suatu transaksi.
  2. Untuk versi SQLite yang lebih lama - Pertimbangkan mode jurnal yang kurang paranoid (pragma journal_mode). Ada NORMAL, dan kemudian ada OFF, yang dapat secara signifikan meningkatkan kecepatan insert jika Anda tidak terlalu khawatir tentang database yang mungkin semakin rusak jika OS macet. Jika aplikasi Anda macet, data akan baik-baik saja. Perhatikan bahwa dalam versi yang lebih baru, OFF/MEMORY pengaturan tidak aman untuk tingkat aplikasi crash.
  3. Bermain dengan ukuran halaman membuat perbedaan juga (PRAGMA page_size). Memiliki ukuran halaman yang lebih besar dapat membuat membaca dan menulis menjadi sedikit lebih cepat karena halaman yang lebih besar disimpan dalam memori. Perhatikan bahwa lebih banyak memori akan digunakan untuk database Anda.
  4. Jika Anda memiliki indeks, pertimbangkan untuk menelepon CREATE INDEXsetelah melakukan semua sisipan Anda. Ini secara signifikan lebih cepat daripada membuat indeks dan kemudian melakukan penyisipan Anda.
  5. Anda harus cukup berhati-hati jika Anda memiliki akses bersamaan ke SQLite, karena seluruh basis data terkunci ketika penulisan selesai, dan meskipun banyak pembaca dimungkinkan, menulis akan dikunci. Ini telah diperbaiki agak dengan penambahan WAL dalam versi SQLite yang lebih baru.
  6. Manfaatkan ruang penyimpanan ... database yang lebih kecil lebih cepat. Misalnya, jika Anda memiliki pasangan nilai kunci, coba buat kunci itu INTEGER PRIMARY KEY jika memungkinkan, yang akan menggantikan kolom nomor baris tersirat unik dalam tabel.
  7. Jika Anda menggunakan beberapa utas, Anda dapat mencoba menggunakan cache halaman bersama, yang akan memungkinkan halaman yang dimuat untuk dibagikan antar utas, yang dapat menghindari panggilan I / O yang mahal.
  8. Jangan gunakan !feof(file)!

Saya juga menanyakan pertanyaan serupa sini dan sini.


672



Coba gunakan SQLITE_STATIC dari pada SQLITE_TRANSIENT untuk sisipan itu.

SQLITE_TRANSIENT akan menyebabkan SQLite menyalin data string sebelum kembali.

SQLITE_STATIC mengatakan bahwa alamat memori yang Anda berikan itu akan berlaku sampai permintaan telah dilakukan (yang dalam lingkaran ini selalu terjadi). Ini akan menghemat beberapa alokasi, menyalin, dan membatalkan operasi per loop. Mungkin ada peningkatan besar.


102



Hindari sqlite3_clear_bindings (stmt);

Kode dalam tes ini menetapkan binding setiap kali harus cukup.

Intro C API dari dokumen SQLite mengatakan

Sebelum memanggil sqlite3_step () untuk pertama kali atau segera   setelah sqlite3_reset (), aplikasi dapat memanggil salah satu dari   sqlite3_bind () interface untuk melampirkan nilai ke parameter. Setiap   panggilan ke sqlite3_bind () menimpa bindings sebelumnya pada parameter yang sama

(Lihat: sqlite.org/cintro.html). Tidak ada dalam dokumen untuk fungsi itu mengatakan Anda harus menyebutnya sebagai tambahan untuk hanya mengatur binding.

Lebih detail: http://www.hoogli.com/blogs/micro/index.html#Avoid_sqlite3_clear_bindings ()


80



Pada sisipan massal

Terinspirasi oleh posting ini dan oleh pertanyaan Stack Overflow yang menuntun saya ke sini - Apakah mungkin untuk memasukkan beberapa baris sekaligus dalam database SQLite? - Saya telah diposting pertama saya Git gudang:

https://github.com/rdpoor/CreateOrUpdate

yang memuat massal array dari ActiveRecords MySQL, SQLite atau PostgreSQL database. Ini termasuk opsi untuk mengabaikan rekaman yang ada, menimpa mereka atau menimbulkan kesalahan. Tolok ukur saya yang belum sempurna menunjukkan peningkatan kecepatan 10x dibandingkan dengan penulisan sekuensial - YMMV.

Saya menggunakannya dalam kode produksi di mana saya sering perlu mengimpor dataset besar, dan saya cukup senang dengan itu.


47



Impor massal tampaknya berkinerja terbaik jika Anda dapat memotongnya INSERT / UPDATE pernyataan. Nilai 10.000 atau lebih telah bekerja dengan baik untuk saya di atas meja dengan hanya beberapa baris, YMMV ...


40



Jika Anda hanya peduli tentang membaca, versi yang agak lebih cepat (tetapi mungkin membaca data basi) adalah membaca dari beberapa koneksi dari beberapa utas (sambungan per-utas).

Pertama temukan barangnya, di tabel:

 SELECT COUNT(*) FROM table

kemudian membaca di halaman (LIMIT / OFFSET)

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

di mana dan dihitung per-benang, seperti ini:

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

untuk setiap utas:

int offset = thread_index * limit

Untuk db kecil kami (200mb), ini membuat kecepatan 50-75% (3.8.0.2 64-bit pada Windows 7). Tabel kami sangat tidak normal (1000-1500 kolom, sekitar 100.000 atau lebih baris).

Terlalu banyak atau terlalu sedikit utas tidak akan melakukannya, Anda perlu melakukan benchmark dan membuat profil sendiri.

Juga bagi kami, SHAREDCACHE membuat kinerja lebih lambat, jadi saya menempatkan PRIVATECACHE secara manual (karena itu diaktifkan secara global untuk kami)


32



Saya tidak dapat memperoleh keuntungan apa pun dari transaksi hingga saya menaikkan cache_size ke nilai yang lebih tinggi yaitu PRAGMA cache_size=10000;


20



Setelah membaca tutorial ini, saya mencoba menerapkannya ke program saya.

Saya memiliki 4-5 file yang berisi alamat. Setiap file memiliki sekitar 30 juta rekaman. Saya menggunakan konfigurasi yang sama yang Anda sarankan tetapi jumlah INSERT saya per detik sangat rendah (~ 10.000 catatan per detik).

Di sinilah saran Anda gagal. Anda menggunakan satu transaksi untuk semua catatan dan satu insert tanpa kesalahan / gagal. Katakanlah Anda membagi setiap catatan menjadi beberapa sisipan pada tabel yang berbeda. Apa yang terjadi jika rekaman rusak?

Perintah ON CONFLICT tidak berlaku, karena jika Anda memiliki 10 elemen dalam catatan dan Anda perlu setiap elemen dimasukkan ke tabel yang berbeda, jika elemen 5 mendapat kesalahan CONSTRAINT, maka semua penyisipan 4 sebelumnya juga harus dimasukkan.

Jadi di sinilah tempat rollback datang. Satu-satunya masalah dengan rollback adalah Anda kehilangan semua sisipan dan mulai dari atas. Bagaimana Anda bisa menyelesaikan ini?

Solusi saya adalah menggunakan banyak transaksi. Saya memulai dan mengakhiri transaksi setiap 10.000 catatan (Jangan tanya mengapa nomor itu, itu adalah yang tercepat yang saya uji). Saya membuat array berukuran 10.000 dan memasukkan catatan sukses di sana. Ketika kesalahan terjadi, saya melakukan rollback, memulai transaksi, memasukkan catatan dari array saya, komit dan kemudian mulai transaksi baru setelah catatan rusak.

Solusi ini membantu saya melewati masalah yang saya hadapi ketika berurusan dengan file yang berisi catatan buruk / duplikat (saya memiliki hampir 4% catatan buruk).

Algoritma yang saya buat membantu saya mengurangi proses saya selama 2 jam. Proses pemuatan akhir file 1 jam 30m yang masih lambat tetapi tidak dibandingkan dengan 4 jam yang awalnya diambil. Saya berhasil mempercepat insert dari 10.000 / s ke ~ 14.000 / s

Jika ada yang punya ide lain tentang cara mempercepatnya, saya terbuka untuk saran.

MEMPERBARUI:

Selain jawaban saya di atas, Anda harus ingat bahwa menyisipkan per detik tergantung pada hard drive yang Anda gunakan juga. Saya mengujinya pada 3 PC yang berbeda dengan hard drive yang berbeda dan mendapat perbedaan besar dalam beberapa kali. PC1 (1 jam 30m), PC2 (6 jam) PC3 (14 jam), jadi saya mulai bertanya-tanya mengapa itu terjadi.

Setelah dua minggu penelitian dan memeriksa berbagai sumber daya: Hard Drive, Ram, Cache, saya menemukan bahwa beberapa pengaturan pada hard drive Anda dapat mempengaruhi tingkat I / O. Dengan mengklik properti pada drive output yang Anda inginkan, Anda dapat melihat dua opsi di tab umum. Opt1: Kompres drive ini, Opt2: Izinkan file dari drive ini untuk memiliki konten yang diindeks.

Dengan menonaktifkan kedua opsi ini, ketiga PC sekarang membutuhkan waktu yang kurang lebih sama (1 jam dan 20 hingga 40 menit). Jika Anda mengalami insers lambat, periksa apakah hard drive Anda dikonfigurasi dengan opsi-opsi ini. Ini akan menghemat banyak waktu dan sakit kepala mencoba mencari solusinya


11



Jawaban atas pertanyaan Anda adalah bahwa sqlite3 yang lebih baru telah meningkatkan kinerja, gunakan itu.

Jawaban ini Mengapa SQLAlchemy menyisipkan dengan sqlite 25 kali lebih lambat daripada menggunakan sqlite3 secara langsung? oleh SqlAlchemy Orm Penulis memiliki 100k sisipan dalam 0,5 detik, dan saya telah melihat hasil yang sama dengan python-sqlite dan SqlAlchemy. Yang membuat saya percaya bahwa kinerja telah membaik dengan sqlite3


5



Ada ceramah hebat dari Paul Betts tentang bagaimana dia membuat C # akavache sangat cepat: https://www.youtube.com/watch?v=j7WnQhwBwqA

Mungkin Anda bisa menemukan beberapa petunjuk untuk Anda. Terlalu panjang untuk membuat rangkuman singkat di sini


3



Sejak 3.24 SQLite mendukung pernyataan UPSERT.

Lihat "SQL As Understood By SQLite" 1 Ketika sebuah baris tidak ada, disisipkan jika tidak diperbarui. Mesin lain menyebut MERGE ini.


0