sqlite3_20_million_generate_mem_helper.dpr

最新版本的sqlite封装for delphi

program SQLite3_20_Million_Generate_Mem_Helper;

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

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

          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
                  { Insert the current batch of records from the :memory: helper
                    table to the main database table 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. }
                  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');

                  { Begin new transaction and start over. }
                  sqlite3_exec_fast(DB, 'BEGIN TRANSACTION');
                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;

        WriteLn;
        totalsec := (GetTickCount - tc_start) div 1000;
        WriteLn('Insertion Time: ',
          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.