disqlite3_performance_fmain.pas

来自「DELPHI 访问SQLITE3 数据库的VCL控件」· PAS 代码 · 共 563 行 · 第 1/2 页

{ DISQLite3 Demo Performance demo. }

unit DISQLite3_Performance_fMain;

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

interface

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

type
  TPerformanceThread = class(TThread)
  private
    FDb: TDISQLite3Database;
    FInsertCount: Integer;
    FLog: TStrings;
    FLogMessage: AnsiString;
    FTickCount: Cardinal;
  protected
    procedure DoWriteToLog;
    procedure StartTime;
    procedure ShowTime(const AMsg: AnsiString);
    procedure WriteToLog(const s: AnsiString = '');
  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 Demo: 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: AnsiString;
  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('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. }