disqlite3_performance_fmain.pas

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

      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
      if e.ErrorCode <> SQLITE_INTERRUPT then
        WriteToLog(e.Message);
    on e: Exception do
      WriteToLog(e.Message);
  end;
end;

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

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

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

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

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

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

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

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

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

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

end.