sqlite3_20_million_import.dpr

最新版本的sqlite封装for delphi

Program SQLite3_20_Million_Import;

{$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;

Function ProgressCallback(UserData: Pointer): Integer; Cdecl;
Begin
  Write('.');
  Result := 0;
End;

//------------------------------------------------------------------------------

Var
  CsvFileSize                           : Int64;
  DB                                    : TSQLiteDB;
  e                                     : Extended;
  Stmt                                  : TSQLiteStmt;
  Count                                 : Integer;
  Percent                               : Double;
  tc_start, tc_delta, tc_Index          : 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 table. }
          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)');

          { Prepare an SQL statement. Prepared statements are the fastes way to
            insert bulk data with DISQLtie3. We will bind values to this statement
            below and then step it once to perform the insert. }
          sqlite3_check(sqlite3_prepare(DB,
            'INSERT INTO t (t1,t2,i1,r1,r2,r3,r4,r5,r6)' +
            'VALUES (?,?,?,?,?,?,?,?,?)', -1, @Stmt, Nil), DB);
          Try
            Count := 0;
            { Start a transaction to speed up inserts. It will be committed and
              a new transaction 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
                  { Commit current transaction. }
                  sqlite3_exec_fast(DB, 'COMMIT');
                  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');
                  { Start new transaction. }
                  sqlite3_exec_fast(DB, 'BEGIN TRANSACTION');
                End;
              End;
            End;

            { Commit current transaction. }
            sqlite3_exec_fast(DB, 'COMMIT');

          Finally
            sqlite3_finalize(Stmt);
          End;
        Finally
          Parser.FreeSourceStream;
        End;

        WriteLn;
        totalsec := (GetTickCount - tc_start) Div 1000;
        WriteLn('Insertion Time: ', totalsec Div 60, ' min, ', totalsec Mod 60, ' sec');

        sqlite3_progress_handler(DB, 50000, ProgressCallback, Nil);

        tc_Index := GetTickCount;
        WriteLn;
        WriteLn('Creating index on date column ... this may take a while ... ');
        sqlite3_exec_fast(DB, 'CREATE INDEX t_i1 ON t (i1)');
        WriteLn;
        totalsec := (GetTickCount - tc_Index) Div 1000;
        WriteLn('Index created in ', totalsec Div 60, ' min, ', totalsec Mod 60, 'sec');

      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.