sqlite3_20_million_import_mem_helper.dpr

最新版本的sqlite封装for delphi

program SQLite3_20_Million_Import_Mem_Helper;

{$APPTYPE CONSOLE}

{$I SQLite3.inc}

uses
  Windows, SysUtils,
  DICsvParser, // DIUnicode from http://www.yunqa.de/delphi/
  SQLite3Lib;

const
  CSV_FILE_NAME = '..\..\20_million.csv';
  DB_FILE_NAME = '..\..\20_million_import.db3';
  TRANSACTION_DELTA = 50000;

var
  CsvFileSize: Int64;
  DB: TSQLiteDB;
  e: Extended;
  Stmt: TSQLiteStmt;
  Count: Integer;
  Percent: Double;
  tc_start, tc_delta: Cardinal;
  totalsec: Cardinal;
  Parser: TDICsvParser;
begin
  try
    { Initialize the SQLite3 library prior to using any other SQLite3
      functionality. See also sqlite3_shutdown() below.}
    sqlite3_initialize;
    try
      tc_start := GetTickCount;
      DeleteFile(DB_FILE_NAME);
      Parser := TDICsvParser.Create{$IFNDEF DI_No_Unicode_Component}(nil){$ENDIF};
      try
        Parser.SourceFile := CSV_FILE_NAME;
        CsvFileSize := Parser.SourceStream.Size;
        DeleteFile(DB_FILE_NAME);
        sqlite3_check(sqlite3_open(DB_FILE_NAME, @DB));
        try
          { Tweak some DB settings to speed up inserts (SQLite3 Pro only). }

          { Prevent other users from accessing the database while we are
            inserting. }
          sqlite3_exec_fast(DB, 'PRAGMA locking_mode=EXCLUSIVE');

          { Set the number of pages the DB engine buffers in memory.
            400000 pages require about 450 MB of memory for this application.
            Increasing this value further will speed up insertions and index
            creation, but will also use more memory. Reduce if you have less
            memory available to avoid OS paging. }
          sqlite3_exec_fast(DB, 'PRAGMA cache_size=400000');

          { Noticably faster inserts at the cost of a little less ACID
            security. }
          sqlite3_exec_fast(DB, 'PRAGMA synchronous=OFF');

          { Create the main table and index. }
          sqlite3_exec_fast(DB, 'CREATE TABLE t (' +
            't1 TEXT,t2 TEXT,' +
            'i1 INTEGER,' +
            'r1 REAL,r2 REAL,r3 REAL,r4 REAL,r5 REAL,r6 REAL)');
          sqlite3_exec_fast(DB, 'CREATE INDEX t_i1 ON t (i1)');

          { Create helper :memory: database and table for fast batch inserts. }
          sqlite3_exec_fast(DB, 'ATTACH '':memory:'' AS m');
          sqlite3_exec_fast(DB, 'CREATE TABLE m.x(' +
            't1 TEXT,t2 TEXT,' +
            'i1 INTEGER,' +
            'r1 REAL,r2 REAL,r3 REAL,r4 REAL,r5 REAL,r6 REAL)');
          { Create an index for the in :memory: helper table. Without an index,
            SQLite3 will create an on-disk helper file for sorting the
            inserts. }
          sqlite3_exec_fast(DB, 'CREATE INDEX m.x_i1 ON x(i1)');

          { Prepare the helper insert SQL statement. A prepared statements it the
            fastest way to insert bulk data with DISQLtie3. Values will be bound
            to this statement below before it is executed. }
          sqlite3_check(sqlite3_prepare(DB,
            'INSERT INTO x(t1,t2,i1,r1,r2,r3,r4,r5,r6)' +
            'VALUES (?,?,?,?,?,?,?,?,?)', -1, @Stmt, nil), DB);
          try
            Count := 0;
            { Start a transaction to speed up inserts. This transaction will be
              committed and a new one started every TRANSACTION_DELTA inserts. }
            sqlite3_exec_fast(DB, 'BEGIN TRANSACTION');

            while Parser.ReadNextData do
              begin
                case Parser.DataCol of
                  0: sqlite3_bind_str(Stmt, 1, sqlite3_encode_utf8(Parser.DataAsStrW));
                  1: sqlite3_bind_str(Stmt, 2, sqlite3_encode_utf8(Parser.DataAsStrW));
                  2: sqlite3_bind_int(Stmt, 3, StrToInt(Parser.DataAsStrW));
                  3: if SqlStrToFloat(Parser.DataToStrA, e) > 0 then
                      sqlite3_bind_double(Stmt, 4, e);
                  4: if SqlStrToFloat(Parser.DataToStrA, e) > 0 then
                      sqlite3_bind_double(Stmt, 5, e);
                  5: if SqlStrToFloat(Parser.DataToStrA, e) > 0 then
                      sqlite3_bind_double(Stmt, 6, e);
                  6: if SqlStrToFloat(Parser.DataToStrA, e) > 0 then
                      sqlite3_bind_double(Stmt, 7, e);
                  7: if SqlStrToFloat(Parser.DataToStrA, e) > 0 then
                      sqlite3_bind_double(Stmt, 8, e);
                  8: if SqlStrToFloat(Parser.DataToStrA, e) > 0 then
                      sqlite3_bind_double(Stmt, 9, e);
                end;

                if Parser.RowComplete then
                  begin
                    Inc(Count);
                    sqlite3_check(sqlite3_step(Stmt), DB);
                    sqlite3_check(sqlite3_reset(Stmt), DB);

                    { Every TRANSACTION_DELTA inserts, commit the transaction and
                      start a new one. Also output some performance statistics. }
                    if Count mod TRANSACTION_DELTA = 0 then
                      begin
                        { Insert the current batch of records and commit. }
                        sqlite3_exec_fast(DB, 'INSERT INTO t ' +
                          'SELECT * FROM x ORDER by i1');
                        sqlite3_exec_fast(DB, 'DELETE FROM x');
                        sqlite3_exec_fast(DB, 'COMMIT');

                        { Print progress timings and statistics. }
                        tc_delta := GetTickCount - tc_start;
                        Percent := Parser.SourceStream.Position / CsvFileSize;
                        totalsec := Round(tc_delta / Percent / 1000);
                        WriteLn(Count: 8, ' - ',
                          Count / tc_delta * 1000: 7: 2, ' per sec - ',
                          Percent * 100: 3: 0, '% done - est. total time: ',
                          totalsec div 60, ' min, ', totalsec mod 60, ' sec');

                        { Begin new transaction and start over. }
                        sqlite3_exec_fast(DB, 'BEGIN TRANSACTION');
                      end;
                  end;
              end;

            { Insert the final batch and commit. }
            sqlite3_exec_fast(DB, 'INSERT INTO t SELECT * FROM x ORDER by i1');
            sqlite3_exec_fast(DB, 'DELETE FROM x');
            sqlite3_exec_fast(DB, 'COMMIT');

          finally
            sqlite3_finalize(Stmt);
          end;
        finally
          Parser.FreeSourceStream;
        end;

      finally
        Parser.Free;

        sqlite3_check(sqlite3_close(DB), DB);
        WriteLn;
        totalsec := (GetTickCount - tc_start) div 1000;
        WriteLn('Final Time: ',
          totalsec div 60, ' min, ', totalsec mod 60, 'sec');

        WriteLn;
        WriteLn('Max database memory usage: ',
          sqlite3_memory_highwater(0) div 1024 div 1024, ' MB');
      end;

    finally
      { Deallocate any resources that were allocated by
        sqlite3_initialize() above. }
      sqlite3_shutdown;
    end;

  except
    on e: Exception do
      WriteLn(e.Message);
  end;

  WriteLn;
  WriteLn('Done - Press ENTER to Exit');
  ReadLn;
end.