sqlite3_20_million_generate.dpr

最新版本的sqlite封装for delphi

program SQLite3_20_Million_Generate;

{$APPTYPE CONSOLE}

{$I SQLite3.inc}

uses
  Windows, SysUtils, SQLite3Lib;

const
  FILE_NAME = '..\..\20_million_generate.db3';
  TOTAL_COUNT = 20000000;
  TRANSACTION_DELTA = 50000;
  DATE_START = 19770101;
  DATE_END = 20071231;
  DATE_DIFF = (DATE_END - DATE_START) / TOTAL_COUNT;

function ProgressCallback(UserData: Pointer): Integer;cdecl; 
begin
  Write('.');
  Result := 0;
end;

var
  DB: TSQLiteDB;
  Stmt: TSQLiteStmt;
  Date: Double;
  ProductCode: array[0..6] of AnsiChar;
  ProductLetter: AnsiChar;
  i, j: Integer;
  MemUsed, MemHighWater: Integer;
  persec: Double;
  totalsec: Integer;
  tc_start, tc_Index: Cardinal;
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(FILE_NAME);
      sqlite3_check(sqlite3_open(FILE_NAME, @DB));
      try
        { Tweak some DB settings to speed up inserts. They show effect with
          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. This is 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

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

          Date := DATE_START;
          for i := 1 to TOTAL_COUNT do
            begin
              { Bind a random product code. }
              for j := Low(ProductCode) to High(ProductCode) do
                ProductCode[j] := AnsiChar(Ord('A') + Random(26));
              sqlite3_bind_text(Stmt, 1,
                PAnsiChar(@ProductCode), SizeOf(ProductCode), SQLITE_STATIC);
              { Bind a random product letter. }
              ProductLetter := AnsiChar(Ord('A') + Random(26));
              sqlite3_bind_text(Stmt, 2,
                @ProductLetter, SizeOf(ProductLetter), SQLITE_STATIC);
              { Bind a date, increasing from DATE_START. }
              Date := Date + DATE_DIFF;
              sqlite3_bind_int(Stmt, 3, Trunc(Date));
              { Bind random floats, range 0 to 1000. }
              sqlite3_bind_double(Stmt, 4, Random * 1000);
              sqlite3_bind_double(Stmt, 5, Random * 1000);
              sqlite3_bind_double(Stmt, 6, Random * 1000);
              sqlite3_bind_double(Stmt, 7, Random * 1000);
              sqlite3_bind_double(Stmt, 8, Random * 1000);
              sqlite3_bind_double(Stmt, 9, Random * 1000);
              { Step the statement to perform the insert. }
              sqlite3_check(sqlite3_step(Stmt), DB);
              { Reset the statement prepares it for the next insert. }
              sqlite3_check(sqlite3_reset(Stmt), DB);

              { Every TRANSACTION_DELTA inserts, commit the transaction and
                start a new one. Also output some performance statistics. }
              if i mod TRANSACTION_DELTA = 0 then
                begin
                  { Commit current transaction. }
                  sqlite3_exec_fast(DB, 'COMMIT');
                  { Display insert speed. }
                  persec := i / ((GetTickCount - tc_start) / 1000);
                  totalsec := Round(TOTAL_COUNT / persec);
                  WriteLn(i: 8, ' - ',
                    persec: 7: 2, ' per sec - ',
                    i * 100 div TOTAL_COUNT: 3, '% done - est. total time: ',
                    totalsec div 60, ' min, ', totalsec mod 60, ' sec');
                  { Start new transaction. }
                  sqlite3_exec_fast(DB, 'BEGIN TRANSACTION');
                end;
            end;

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

        finally
          sqlite3_finalize(Stmt);
        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
        sqlite3_check(sqlite3_close(DB), DB);
        WriteLn;
        totalsec := (GetTickCount - tc_start) div 1000;
        WriteLn('Total Time: ',
          totalsec div 60, ' min, ', totalsec mod 60, 'sec');

        WriteLn;
        sqlite3_status(SQLITE_STATUS_MEMORY_USED, @MemUsed, @MemHighWater, 0);
        WriteLn('Max database memory usage: ', MemHighWater 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.