To avoid unnecessary rebalance and split operation of b-tree, sort the data before insert it to table.
Create temp table bf_temp(id integer, offset); -- careful, no primary key Begin; Insert into bf_temp(id, offset) values(?,?); … create table if not exists bf_fav_nis(id integer primary key, offset integer); insert into bf_fav_nis select * from bf_temp order by id; commit; slight modified version of your program, take 1min19sec to import 22M rows, #include <string> #include <stdio.h> #include <sys/timeb.h> #include "sqlite3.h" using namespace std; namespace os { int getmillicount() { // Something like GetTickCount but portable // It rolls over every ~ 12.1 days (0x100000/24/60/60) // Use GetMilliSpan to correct for rollover timeb tb; ftime( &tb ); int nCount = tb.millitm + (tb.time & 0xfffff) * 1000; return nCount; } }; struct SQLite3Statement { sqlite3_stmt *stmt; SQLite3Statement(){stmt=NULL;} ~SQLite3Statement() { if(stmt) sqlite3_finalize(stmt); } int bind_exec(std::string &p1, sqlite3_int64 p2) { if(stmt) { sqlite3_bind_text(stmt, 1, p1.c_str(), p1.size(), NULL); sqlite3_bind_int64(stmt, 2, p2); int rc = sqlite3_step(stmt); sqlite3_reset(stmt); return rc; } return -1; } }; struct SQLite3 { sqlite3 *db; SQLite3(const char *dbname) { db = NULL; sqlite3_open_v2(dbname, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL); } ~SQLite3() { sqlite3_close(db); } int exec_dml(const char *szSql) { return sqlite3_exec(db, szSql, NULL, NULL, NULL); } int prepare(SQLite3Statement &stmt, const char *szSql) { return sqlite3_prepare_v2(db, szSql, -1, &stmt.stmt, NULL); } }; struct file { FILE *fp; file(const char *fname, const char *mode) { fp = fopen(fname, mode); } int tell() { return ftell(fp); } std::string read_line() { char line[32]; if(!fgets(line, sizeof(line),fp)) line[0] = '\0';; return line; } }; int main(int argc, char *argv[]) { int total_milli_param = 0; if(argc > 1) { total_milli_param = atoi(argv[1]); } SQLite3 db("bf-favorecidos-nis.db"); db.exec_dml("PRAGMA synchronous = 0;"); db.exec_dml("drop table if exists bf_fav_nis;"); db.exec_dml("create temp table if not exists bf_temp(id integer, offset integer);"); db.exec_dml("create table if not exists bf_fav_nis(id integer primary key, offset integer);"); SQLite3Statement stmt; db.prepare(stmt, "insert into bf_temp(id, offset) values(?,?)"); file fd("favorecidos-nis.txt","r"); db.exec_dml("begin;"); std::string line; auto count = 0, offset = 0, total_count = 22501426, //total number of line in fd total_milli = (total_milli_param ? total_milli_param : 190020); //total miliseconds spent to process all lines auto step_count = 100000, start_milli = os::getmillicount(), last_milli = os::getmillicount(); printf("rec_count\tcount_rate\tmilli_rate\tstep_rate\tstep_count\tspent_milli\ttill_now_milli\n"); while( (line = fd.read_line()).size() ) { stmt.bind_exec(line, offset); ++count; offset = fd.tell(); if( (count % step_count) == 0) { auto now_milli = os::getmillicount(); auto till_now_milli = now_milli - start_milli; auto spent_milli = now_milli - last_milli; auto step_rate = step_count / spent_milli; auto total_count_rate = ((count + 0.0) / total_count) * 100; auto total_milli_rate = ((till_now_milli + 0.0) / total_milli) * 100; last_milli = now_milli; printf("%d\t%f\t%f\t%d\t%d\t%d\t%d\n", count, total_count_rate, total_milli_rate, step_rate * 1000, step_count, spent_milli, till_now_milli); db.exec_dml("commit;begin;"); } } db.exec_dml("insert into bf_fav_nis select * from bf_temp order by id"); db.exec_dml("commit;"); auto milli_spent = os::getmillicount() - start_milli; printf("%d\t%d\t%d\t%f\n", count, (count / milli_spent) * 1000, milli_spent, milli_spent / 60.0); return 0; } Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users