[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] SQLite3 database slowdown at 1GB
From:       Quan Yong Zhai <qydi () msn ! com>
Date:       2016-09-27 7:30:19
Message-ID: CO2PR04MB6978382E4DDEB0CFF551EB3C0CC0 () CO2PR04MB697 ! namprd04 ! prod ! outlook ! com
[Download RAW message or body]

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


[prev in list] [next in list] [prev in thread] [next in thread]