ConsoleAppSQLite.dpr

ConsoleAppSQLite.dpr

program ConsoleAppSQLite;         

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  DB,
  DBAccess,
  Uni,
  UniProvider,
  SQLiteUniProvider,
  Windows,
  System.Classes,
  FireDAC.DApt,
  FireDAC.Comp.Client,
  FireDAC.Stan.Def,
  FireDAC.Stan.Intf,
  FireDAC.Stan.Async,
  FireDAC.Stan.Option,
  FireDAC.Stan.Param,
  FireDAC.Phys.SQLite;

const      
  CountOperation: Integer = 100000;
  BatchCountOperation: Integer = 1000000;

  DropTableSQL: String = 'DROP TABLE IF EXISTS MY_TEST_TABLE';
  CreateTableSQL: String = 'CREATE TABLE IF NOT EXISTS MY_TEST_TABLE (ID INTEGER, F_FLOAT FLOAT, F_STRING VARCHAR(250), CONSTRAINT PK_MY_TEST_TABLE PRIMARY KEY (ID));';
  InsertSQL: String = 'INSERT INTO MY_TEST_TABLE (ID, F_FLOAT, F_STRING) VALUES (:ID, :F_FLOAT, :F_STRING)';
  LockModeSQL: String = 'PRAGMA locking_mode=EXCLUSIVE';
  SynchronousSQL: String = 'PRAGMA synchronous=0';

var
  UniConnection: TUniConnection;
  UniQuery: TUniQuery;
  TickCount: Cardinal;
  i: Integer;

  FDConnection: TFDConnection;
  FDQuery: TFDQuery;

begin
  WriteLn('Wait, please ...');
  UniConnection := TUniConnection.Create(nil);
  try
    UniConnection.ProviderName := 'SQLite';
    UniConnection.Database := ExtractFilePath(ParamStr(0)) + 'UniDACTestDB.db3';
    UniConnection.SpecificOptions.Values['Direct'] := 'True';
    UniConnection.SpecificOptions.Values['ForceCreateDatabase'] := 'True';
    UniConnection.Connect;

    UniConnection.ExecSQL(LockModeSQL);
    UniConnection.ExecSQL(SynchronousSQL);
    UniConnection.ExecSQL(DropTableSQL);
    UniConnection.ExecSQL(CreateTableSQL);

    UniQuery := TUniQuery.Create(nil);
    try
      UniQuery.Connection := UniConnection;
      UniQuery.SQL.Text := InsertSQL;
      UniQuery.Params[0].DataType := ftInteger;
      UniQuery.Params[1].DataType := ftFloat;
      UniQuery.Params[2].DataType := ftString;
      UniQuery.Prepare;

      TickCount := GetTickCount;
      for i := 1 to CountOperation do begin
        UniQuery.Params[0].AsInteger := i;
        UniQuery.Params[1].AsFloat := i / 12;
        UniQuery.Params[2].AsString := 'Values ' + IntToStr(i);
        UniQuery.Execute;
      end;

      TickCount := GetTickCount - TickCount;
      WriteLn('UniDAC : time executing is ' + FloatToStr(TickCount / 1000.0) + ' sec. (INSERT count = ' + IntToStr(CountOperation) + ')');
    finally
      UniQuery.Free;
    end;

    UniConnection.ExecSQL(DropTableSQL);
    UniConnection.ExecSQL(CreateTableSQL);

    UniQuery := TUniQuery.Create(nil);
    try
      UniQuery.Connection := UniConnection;
      UniQuery.SQL.Text := InsertSQL;
      UniQuery.Params.ValueCount := BatchCountOperation;

      TickCount := GetTickCount;
      for i := 1 to BatchCountOperation do begin
        UniQuery.Params[0][i - 1].AsInteger := i;
        UniQuery.Params[1][i - 1].AsFloat := i / 12;
        UniQuery.Params[2][i - 1].AsString := 'Values ' + IntToStr(i);
      end;
      UniQuery.Execute(BatchCountOperation);

      TickCount := GetTickCount - TickCount;
      WriteLn('UniDAC : BATCH time executing is ' + FloatToStr(TickCount / 1000.0) + ' sec. (INSERT count = ' + IntToStr(BatchCountOperation) + ')');
    finally
      UniQuery.Free;
    end;

  finally
    UniConnection.Free;
  end;

  FDConnection := TFDConnection.Create(nil);
  try
    FDConnection.DriverName := 'SQLite';
    FDConnection.Params.Add('Database=' + ExtractFilePath(ParamStr(0)) + 'FireDACTestDB.db3');
    FDConnection.Connected := True;

    FDConnection.ExecSQL(LockModeSQL);
    FDConnection.ExecSQL(SynchronousSQL);
    FDConnection.ExecSQL(DropTableSQL);
    FDConnection.ExecSQL(CreateTableSQL);

    FDQuery := TFDQuery.Create(nil);
    try
      FDQuery.Connection := FDConnection;
      FDQuery.SQL.Text := InsertSQL;
      FDQuery.Params[0].DataType := ftInteger;
      FDQuery.Params[1].DataType := ftFloat;
      FDQuery.Params[2].DataType := ftString;
      FDQuery.Prepare;

      TickCount := GetTickCount;
      for i := 1 to CountOperation do begin
        FDQuery.Params[0].AsInteger := i;
        FDQuery.Params[1].AsFloat := i / 12;
        FDQuery.Params[2].AsString := 'Values ' + IntToStr(i);
        FDQuery.Execute;
      end;

      TickCount := GetTickCount - TickCount;
      WriteLn('FireDAC : time executing is ' + FloatToStr(TickCount / 1000.0) + ' sec. (INSERT count = ' + IntToStr(CountOperation) + ')');
    finally
      FDQuery.Free;
    end;

    FDConnection.ExecSQL(DropTableSQL);
    FDConnection.ExecSQL(CreateTableSQL);

    FDQuery := TFDQuery.Create(nil);
    try
      FDQuery.Connection := FDConnection;
      FDQuery.SQL.Text := InsertSQL;
      FDQuery.Params.ArraySize := BatchCountOperation;

      TickCount := GetTickCount;
      for i := 1 to FDQuery.Params.ArraySize do begin
        FDQuery.Params[0].AsIntegers[i - 1] := i;
        FDQuery.Params[1].AsFloats[i - 1] := i / 12;
        FDQuery.Params[2].AsStrings[i - 1] := 'Values ' + IntToStr(i);
      end;
      FDQuery.Execute(BatchCountOperation);

      TickCount := GetTickCount - TickCount;
      WriteLn('FireDAC : BATCH time executing is ' + FloatToStr(TickCount / 1000.0) + ' sec. (INSERT count = ' + IntToStr(BatchCountOperation) + ')');
    finally
      FDQuery.Free;
    end;

  finally
    FDConnection.Free;
  end;

  WriteLn('Press <Enter> to exit');
  ReadLn;
end.

Comparing UniDAC and FireDAC SQLite connection performance.