{ ------------------------------------------------------------------------------

  DISQLite3 project to profile query performance on a 20 million records DB.
  Use DISQLite3 Pro for best performance.

  Visit the DISQLite3 Internet site for latest information and updates:

    http://www.yunqa.de

  Copyright (c) 2005-2021 Ralf Junker, Yunqa <delphi@yunqa.de>

------------------------------------------------------------------------------ }

program DISQLite3_20_Million_Query;

{$APPTYPE CONSOLE}
{$I DICompilers.inc}
{$I DISQLite3.inc}

uses
  {$IFDEF FastMM}{$I FastMM_uses.inc}{$ENDIF}
  {$IFDEF HAS_UNITSCOPE}
  System.SysUtils, Winapi.Windows,
  {$ELSE HAS_UNITSCOPE}
  SysUtils, Windows,
  {$ENDIF HAS_UNITSCOPE}
  DISystemCompat, DISQLite3Api;

{$R ..\..\Yunqa.res}

const
  DB_FILE_NAME = '..\20_million_generate.db3';
  QUERY_COUNT = $400;

var
  DB: sqlite3_ptr;
  Stmt: sqlite3_stmt_ptr;
  i, j: Integer;
  Matches, misses: Cardinal;
  rMax, rMin: Int64;
  s8: Utf8String = '';
  totalsec: Integer;
  tc_start: Cardinal;
begin
  {$IFDEF FastMM}{$I FastMM_init.inc}{$ENDIF}

  Randomize;

  try
    { Initialize the DISQLite3 library prior to using any other DISQLite3
      functionality. See also sqlite3_shutdown() below.}
    sqlite3_initialize;
    try
      sqlite3_check(sqlite3_open(DB_FILE_NAME, @DB));
      try
        // Min
        sqlite3_check(sqlite3_prepare_v2(DB,
          'SELECT min(rowid) FROM t', -1, @Stmt, nil), DB);
        try if sqlite3_check(sqlite3_step(Stmt), DB) = SQLITE_ROW then
            rMin := sqlite3_column_int64(Stmt, 0)
          else
            rMin := 0;
        finally
          sqlite3_check(sqlite3_finalize(Stmt), DB);
        end;

        // Max
        sqlite3_check(sqlite3_prepare_v2(DB,
          'SELECT max(rowid) FROM t', -1, @Stmt, nil), DB);
        try
          if sqlite3_check(sqlite3_step(Stmt), DB) = SQLITE_ROW then
            rMax := sqlite3_column_int64(Stmt, 0)
          else
            rMax := 0;
        finally
          sqlite3_check(sqlite3_finalize(Stmt), DB);
        end;

        tc_start := GetTickCount;
        Matches := 0;
        misses := 0;
        sqlite3_check(sqlite3_prepare_v2(DB,
          'SELECT * FROM t WHERE rowid = ?', -1, @Stmt, nil), DB);
        try
          i := 0;
          repeat
            j := Random(rMax - rMin) + rMin;
            sqlite3_check(sqlite3_bind_int(Stmt, 1, j), DB);

            if sqlite3_check(sqlite3_step(Stmt), DB) = SQLITE_ROW then
              Inc(Matches)
            else
              System.Inc(misses);

            sqlite3_check(sqlite3_reset(Stmt), DB);

            Inc(i);
            if i mod $10 = 0 then
              begin
                { Write dot progress. }
                Write('.');
              end;
          until i >= QUERY_COUNT;

        finally
          sqlite3_finalize(Stmt);
        end;

        WriteLn;
        totalsec := GetTickCount - tc_start;

        WriteLn('Milliseconds:       ', totalsec);
        WriteLn('Queries per second: ', FloatToStr(QUERY_COUNT / totalsec * 1000));

        WriteLn('Matches:            ', Matches);
        WriteLn('Misses:             ', misses);

      finally
        sqlite3_check(sqlite3_close(DB), DB);
      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.

