{ DISQLite3 Demo Performance demo. }

unit DISQLite3_Performance_fMain;

{$I DI.inc}
{$I DISQLite3.inc}

interface

uses
  DISystemCompat, Windows, SysUtils, Classes, Controls, Forms, StdCtrls, ExtCtrls,
  DISQLite3Database;

type
  TPerformanceThread = class(TThread)
  private
    FDb: TDISQLite3Database;
    FInsertCount: Integer;
    FLog: TStrings;
    FLogMessage: String;
    FTickCount: Cardinal;
  protected
    procedure DoWriteToLog;
    procedure StartTime;
    procedure ShowTime(const AMsg: String);
    procedure WriteToLog(const s: String = '');
  public
    constructor Create(const AInsertCount: Integer; const ALog: TStrings);
    procedure Execute; override;
    procedure Interrupt;
  end;

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

  TfrmPerformance = class(TForm)
    memoLog: TMemo;
    pnlLeft: TPanel;
    btnStartTests: TButton;
    cbxRecordCount: TComboBox;
    lblRecordCount: TLabel;
    btnClearLog: TButton;
    btnStopTests: TButton;
    procedure btnStartTestsClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure btnClearLogClick(Sender: TObject);
    procedure btnStopTestsClick(Sender: TObject);
  private
    FPerformanceThread: TPerformanceThread;
    procedure EnableControls(const AValue: Boolean);
    procedure ThreadTerminate(ASender: TObject);
  end;

const
  APP_TITLE = 'DISQLite3' + {$IFDEF DISQLite3_Personal} ' Personal' + {$ENDIF} ': Performance Test';

var
  frmPerformance: TfrmPerformance;

implementation

{$R *.dfm}

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

uses
  DISQLite3Api;

//------------------------------------------------------------------------------
// TfrmPerformance class
//------------------------------------------------------------------------------

procedure TfrmPerformance.FormCreate(Sender: TObject);
var
  i: Integer;
begin
  Caption := APP_TITLE;
  i := 10;
  repeat
    cbxRecordCount.Items.Add(IntToStr(i));
    i := i * 10;
  until i > 1000000;
  cbxRecordCount.ItemIndex := 3;
  EnableControls(True);
end;

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

procedure TfrmPerformance.ThreadTerminate(ASender: TObject);
begin
  FPerformanceThread := nil;
  EnableControls(True);
end;

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

procedure TfrmPerformance.btnStartTestsClick(Sender: TObject);
begin
  EnableControls(False);
  with cbxRecordCount do
    FPerformanceThread := TPerformanceThread.Create(StrToInt(Items[ItemIndex]), memoLog.Lines);
  FPerformanceThread.OnTerminate := ThreadTerminate;
  FPerformanceThread.Resume;
end;

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

procedure TfrmPerformance.btnClearLogClick(Sender: TObject);
begin
  memoLog.Clear;
end;

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

procedure TfrmPerformance.btnStopTestsClick(Sender: TObject);
begin
  FPerformanceThread.Interrupt;
end;

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

procedure TfrmPerformance.EnableControls(const AValue: Boolean);
begin
  btnStartTests.Enabled := AValue;
  btnStopTests.Enabled := not AValue;
  btnClearLog.Enabled := AValue;
end;

//------------------------------------------------------------------------------
// TPerformanceThread class
//------------------------------------------------------------------------------

constructor TPerformanceThread.Create(const AInsertCount: Integer; const ALog: TStrings);
begin
  FInsertCount := AInsertCount;
  FLog := ALog;
  FreeOnTerminate := True;
  inherited Create(True);
end;

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

procedure TPerformanceThread.Execute;
var
  Stmt, StmtUpdate: TDISQLite3Statement;
  i: Integer;
  s: Utf8String;
  r: Double;
begin
  try
    WriteToLog;
    WriteToLog(Format('Running Tests with %d records:', [FInsertCount]));
    WriteToLog;

    { Since this test contains ASCII text data exclusively, so the DISQLite3
      UTF-8 interface is used for best performance. Internally, all text
      is still stored as Unicode. The DISQLite3 WideString / UTF-16 interface
      is available by appending ...16 to the relevant function calls.}

    //------------------------------------------------------------------------------
    // Create database and table
    //------------------------------------------------------------------------------

    FDb := TDISQLite3Database.Create(nil);
    try
      { Create a new database with a new table:

        Table t:

          IdName - INTEGER PRIMARY KEY -- a unique Integer ID to identify each
                                          record. DISQLite3 will automatically index
                                          PRIMARY KEY columns and make sure that
                                          they do not contain duplicate values.
          Name   - TEXT                -- a string / text field to store name information.
          Random - REAL                -- a double to store any random number. }

      StartTime;

      FDb.DatabaseName := 'Performance.db3';
      FDb.CreateDatabase;

      FDb.Execute('PRAGMA locking_mode=exclusive;');

      FDb.Execute('CREATE TABLE t (IdName INTEGER PRIMARY KEY, Name TEXT, Random REAL);');

      ShowTime('Create database and table');

      //------------------------------------------------------------------------------
      // Creating INSERT_COUNT records
      //------------------------------------------------------------------------------

      { DISQLite3 easily handles 50000 inserts per second, but only about 60
        transactions per second. Normally, DISQLite3 puts each insert into
        a separate transaction, which then limits you to about 60 inserts per
        second. By using StartTransaction ... Commit we can group multiple inserts
        into the same transaction thus increasing insert rate.

        Transactions are slow due to limitations of computer disk hardware. When
        DISQLite3 writes to the disk, it has to stop and wait at a couple of places
        for all of the data to actually be written to the disk surface. This is
        necessary in case a power failure or OS crash occurs - so that the data can
        be recovered. It is this stopping and waiting that takes so long.

        Therefore we wrap the following bulk insert into a transaction for best
        performance. }

      StartTime;

      FDb.StartTransaction;
      try
        Stmt := FDb.Prepare('INSERT INTO t VALUES (?, ?, ?);');
        try
          for i := 1 to FInsertCount do
            begin
              Stmt.Bind_Int(1, i);
              Stmt.Bind_Str(2, 'John');
              Stmt.Bind_Double(3, Random * 1000);
              Stmt.Step;
              Stmt.Reset;
              if Terminated then Exit;
            end;
        finally
          Stmt.Free;
        end;
        FDb.Commit;
      except
        FDb.Rollback;
        raise;
      end;

      ShowTime(Format('Inserting %d records', [FInsertCount]));

      //------------------------------------------------------------------------------
      // Reading all floating point and string values
      //------------------------------------------------------------------------------

      { Reading the database does not require a transaction for speedup. Only a
        prepared statement and a simple while loop around Stmt.Step are needed. }

      StartTime;

      Stmt := FDb.Prepare('SELECT Name, Random FROM t;');
      try
        while Stmt.Step = SQLITE_ROW do
          begin
            s := Stmt.Column_Str(0);
            r := Stmt.Column_Double(1); // Warning can be ignored - this is just a test!
            if Terminated then Exit;
          end;
      finally
        Stmt.Free;
      end;

      ShowTime('Reading all float and string values');

      //------------------------------------------------------------------------------
      // Reading all floating point and string values, ORDERed by IdName
      //------------------------------------------------------------------------------

      { Reading the database does not require a transaction for speedup. Only a
        prepared statement and a simple while loop around Stmt.Step are needed. }

      StartTime;

      Stmt := FDb.Prepare('SELECT Name, Random FROM t ORDER BY IdName;');
      try
        while Stmt.Step = SQLITE_ROW do
          begin
            s := Stmt.Column_Str(0);
            r := Stmt.Column_Double(1); // Warning can be ignored - this is just a test!
            if Terminated then Exit;
          end;
      finally
        Stmt.Free;
      end;

      ShowTime('Reading all float and string values ORDERed by IdName');

      //------------------------------------------------------------------------------
      // Locate a record based on an integer
      //------------------------------------------------------------------------------

      { To locate a record with DISQLite3, simple add a WHERE statement to any
        SELECT. DISQLite3 will automatically choose from the available indexes to
        optimize the query and speed up performance. }

      StartTime;

      Stmt := FDb.Prepare('SELECT Name, Random FROM t WHERE IdName = 5;');
      try
        if Stmt.Step = SQLITE_ROW then
          begin
            s := Stmt.Column_Str(0);
            r := Stmt.Column_Double(1); // Warning can be ignored - this is just a test!
            if Terminated then Exit;
          end
        else
          begin
            // Record not found.
          end;
      finally
        Stmt.Free;
      end;

      ShowTime('Locate a record based on an integer');

      //------------------------------------------------------------------------------
      // Locating 50 random records based on an integer
      //------------------------------------------------------------------------------

      { This is basically the same procedure as above, but wrapped in a for loop.
        The WHERE automatically uses the IdName's PRIMARY KEY index for very fast
        lookups.

        Notice that we prepare the statement ahead of the loop. This is critical to
        performance because preparing a statement is quite complicated and time-
        consuming (parsing the SQL creating the virtual machine, etc.). Reusing
        an already prepared statement can increase performance by 100% and more.

        See the next test for even faster lookups. }

      StartTime;

      Stmt := FDb.Prepare('SELECT Name, Random FROM t WHERE IdName=?;');
      try
        for i := 1 to 50 do
          begin
            { Bind the search value. }
            Stmt.Bind_Int(1, Random(FInsertCount));
            if Stmt.Step = SQLITE_ROW then
              begin
                s := Stmt.Column_Str(0);
                r := Stmt.Column_Double(1); // Warning can be ignored - this is just a test!
                if Terminated then Exit;
              end
            else
              begin
                // Record not found.
              end;
            Stmt.Reset;
          end;
      finally
        Stmt.Free;
      end;

      ShowTime('Locate 50 random values based on an integer');

      //------------------------------------------------------------------------------
      // Locate 5000 records based on an integer.
      //------------------------------------------------------------------------------

      { This is like the previous test but with a much increased the number of
        lookups. It uses the same loop, but wraps it in an exclusive transaction
        to avoid frequent database locks and unlocks between the individual locates.

        An exclusive transaction can speed up database operations which frequently
        change the database locking state. To lock the database, multi-user database
        engines like DISQLite3 usually set a flag in the database file. This
        requires disk write operations for each lookup and is therefore quite slow.
        The exclusive transaction saves us at least 5000 lock and unlock operation
        and usually speeds up this test by a factor of 2 and more. }

      StartTime;

      FDb.StartTransaction(ttExclusive);
      try
        Stmt := FDb.Prepare('SELECT Name, Random FROM t WHERE IdName = ?;');
        try
          for i := 1 to 5000 do
            begin
              Stmt.Bind_Int(1, Random(FInsertCount));
              if Stmt.Step = SQLITE_ROW then
                begin
                  s := Stmt.Column_Str(0);
                  r := Stmt.Column_Double(1); // Warning can be ignored - this is just a test!
                  if Terminated then Exit;
                end
              else
                begin
                  // Record not found.
                end;
              Stmt.Reset;
            end;
        finally
          Stmt.Free;
        end;
        FDb.Commit;
      except
        FDb.Rollback;
        raise;
      end;

      ShowTime('Locate 5000 random values based on an integer');

      //------------------------------------------------------------------------------
      // Rewriting records
      //------------------------------------------------------------------------------

      { Loop through the entire table to replace values. Make sure that you always
        use the RowID column in the UPDATE's WHERE clause: It is an auto-generated
        column which is always indexed and automatically guarantees best performance. }

      StartTime;

      StmtUpdate := FDb.Prepare('UPDATE t SET Name=?, Random=? WHERE RowID=?;');
      try
        Stmt := FDb.Prepare('SELECT Name, Random, RowID FROM t');
        try
          while Stmt.Step = SQLITE_ROW do
            begin
              StmtUpdate.Bind_Str(1, Stmt.Column_Str(0));
              StmtUpdate.Bind_Double(2, Stmt.Column_Double(1));
              StmtUpdate.Bind_Int(3, Stmt.Column_Int(2));
              StmtUpdate.Step;
              StmtUpdate.Reset;
              if Terminated then Exit;
            end;
        finally
          Stmt.Free;
        end;
      finally
        StmtUpdate.Free;
      end;

      ShowTime('Rewriting Records');

      //------------------------------------------------------------------------------
      // Creating an index on Name
      //------------------------------------------------------------------------------

      StartTime;

      { DISQLite3 creates an index by just executing a single SQL command. }
      FDb.Execute('CREATE INDEX First ON t (Name);');

      ShowTime('Creae an index on Name');

      //------------------------------------------------------------------------------
      // Locate a record based on a string
      //------------------------------------------------------------------------------

      { To locate a record with DISQLite3, simple add a WHERE statement to any
        SELECT. DISQLite3 will automatically choose from the available indexes to
        optimize the query and speed up performance. }

      StartTime;

      Stmt := FDb.Prepare('SELECT Name, Random FROM t WHERE Name = ''John'';');
      try
        if Stmt.Step = SQLITE_ROW then
          begin
            s := Stmt.Column_Str(0);
            r := Stmt.Column_Double(1); // Warning can be ignored - this is just a test!
            if Terminated then Exit;
          end
        else
          begin
            // Record not found.
          end
      finally
        Stmt.Free;
      end;

      ShowTime('Locating a record based on a string');

      //------------------------------------------------------------------------------
      // Filtering data
      //------------------------------------------------------------------------------

      { DISQLite3 filters data using the SQL WHERE clause. It can be followd by
        any expression, subquery, or user-defined function. The WHERE clause
        will automatically use existing indexes to speed up performance.
        Notice: There is no index defined for the "Random" column. }

      StartTime;

      Stmt := FDb.Prepare('SELECT Name, Random FROM t WHERE Random < 100;');
      try
        { Do whatever you need to do with the filtered. }
      finally
        Stmt.Free;
      end;

      ShowTime('Filtering data');

      //------------------------------------------------------------------------------
      // Start an exclusive transaction
      //------------------------------------------------------------------------------

      FDb.StartTransaction(ttExclusive);
      try
        // Do whatever you need to do with exclusive access to the database

        // When done modiying the database, commit your changes.
        FDb.Commit;
      except
        // If something went wrong, rollback changes to restore the previous database state.
        FDb.Rollback;
        raise;
      end;

      //------------------------------------------------------------------------------
      // Delete all records from table
      //------------------------------------------------------------------------------

      StartTime;

      FDb.Execute('DELETE FROM t;');

      ShowTime('Delete all records');

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

      WriteToLog('All tests finished');
    finally
      FDb.Free;
    end;

  except
    on e: ESQLite3 do
      case e.ErrorCode of
        SQLITE_INTERRUPT:
          WriteToLog('Interrupted by User');
      else
        WriteToLog(e.Message);
      end;
    on e: Exception do
      WriteToLog(e.Message);
  end;
end;

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

procedure TPerformanceThread.Interrupt;
begin
  FDb.Interrupt;
  Terminate;
end;

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

procedure TPerformanceThread.ShowTime(const AMsg: String);
begin
  WriteToLog(AMsg + ':  ' + IntToStr(GetTickCount - FTickCount) + ' ms');
end;

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

procedure TPerformanceThread.StartTime;
begin
  FTickCount := GetTickCount;
end;

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

procedure TPerformanceThread.WriteToLog(const s: String = '');
begin
  FLogMessage := s;
  Synchronize(DoWriteToLog);
end;

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

procedure TPerformanceThread.DoWriteToLog;
begin
  FLog.Add(FLogMessage);
end;

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

initialization
  { Initialize the DISQLite3 library prior to using any other DISQLite3
    functionality. See also sqlite3_shutdown() below.}
  sqlite3_initialize;

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

end.

