Devart Forums

Posted: **Mon 01 Apr 2019 08:11**

Hello .
I managed a test application that consists on Creating oracle Table, inserting and retrieving Data with Unidac and ADO.
So I've mentionned that there is no difference at all or in certain cases ADO perform better.
So what's wrong ??

PS: Why I can not send you the exemple or even a screen shot ??

my code is :

Code: Select all


function MemoryUsed: Cardinal;
var
  st: TMemoryManagerState;
  sb: TSmallBlockTypeState;
begin
  GetMemoryManagerState(st);
  Result := st.TotalAllocatedMediumBlockSize + st.TotalAllocatedLargeBlockSize;
  for sb in st.SmallBlockTypeStates do
    Result := Result + sb.UseableBlockSize * sb.AllocatedBlockCount;
end;


procedure TForm1.Button2Click(Sender: TObject);
var
  i: integer;
  tc: TDateTime;
  mem: Cardinal;
  
begin

  mem := MemoryUsed;
  tc := Now;


  UniConnection1.LoginPrompt := False;
  UniConnection1.ConnectString := 'Provider Name=oracle;Direct=True;Host=host1;SID=J1X1;User ID=user1;Password=pass1;Login Prompt=False';
  UniConnection1.Open;

//  UniConnection1.ExecSQL('DROP TABLE TEST_PERF_UNIDAC');
  UniConnection1.ExecSQL('CREATE TABLE TEST_PERF_UNIDAC (ID  INTEGER PRIMARY KEY, VC  VARCHAR2(200), NM  NUMERIC(18,2), DT  DATE )');
  UniConnection1.ExecSQL('DELETE FROM TEST_PERF_UNIDAC');

  Randomize;

  UniQuery1.SQL.Text := 'insert into test_perf_UNIDAC values(:id, :vc, :nm, :dt)';
  UniQuery1.Params[0].DataType := ftInteger;
  UniQuery1.Params[1].DataType := ftString;
  UniQuery1.Params[2].DataType := ftFloat;
  UniQuery1.Params[3].DataType := ftDateTime;

  for i := 1 to 150000 do begin
    UniQuery1.Params[0].AsInteger := i;
    UniQuery1.Params[1].AsString := 'Test ' + IntToStr(i);
    UniQuery1.Params[2].AsFloat := Random(1000000) / 100;
    UniQuery1.Params[3].AsDateTime := Now;
    UniQuery1.Execute;
  end;

  UniQuery1.SQL.Text := 'select * from test_perf_UNIDAC';
  UniQuery1.SpecificOptions.Values['FetchAll'] := 'True';
  UniQuery1.Open;
  UniQuery1.Last;

  tc := Now - tc;
  mem := MemoryUsed - mem;
  Memo1.Lines.Add('UniDAC ORacle: Time: ' + FloatToStr(tc * 24 * 60 * 60) + ' sec; Memory used: ' + IntToStr(mem));
  UniConnection1.Close;

end;


procedure TForm1.Button3Click(Sender: TObject);
var    //bbb
  i: integer;
  tc: TDateTime;
  mem: Cardinal;
  rc: Integer;
begin

  mem := MemoryUsed;
  tc := Now;



  ADOConnection1.LoginPrompt := False;
  ADOConnection1.ConnectionString := 'Provider=OraOLEDB.Oracle.1;Password=pass1;Persist Security Info=True;User ID=user1;Data Source=J1X1';
  ADOConnection1.Connected := True;

//  ADOConnection1.Execute('DROP TABLE TEST_PERF_ADO');
  // CREATE TABLE TEST_PERF_ADO (ID  INTEGER PRIMARY KEY, VC  VARCHAR2(200), NM  NUMERIC(18,2), DT  DATE )
  ADOConnection1.Execute('CREATE TABLE TEST_PERF_ADO (ID  INTEGER PRIMARY KEY, VC  VARCHAR2(200), NM  NUMERIC(18,2), DT  DATE )');
  ADOConnection1.Execute('DELETE FROM TEST_PERF_ADO');

  Randomize;


  ADOQuery1.SQL.Text := 'insert into TEST_PERF_ADO values(:id, :vc, :nm, :dt)';
  ADOQuery1.Parameters[0].DataType := ftInteger;
  ADOQuery1.Parameters[1].DataType := ftString;
  ADOQuery1.Parameters[2].DataType := ftFloat;
  ADOQuery1.Parameters[3].DataType := ftDateTime;
  for i := 1 to 150000 do begin
    ADOQuery1.Parameters[0].Value := i;
    ADOQuery1.Parameters[1].Value := 'Test ' + IntToStr(i);
    ADOQuery1.Parameters[2].Value := Random(1000000) / 100;
    ADOQuery1.Parameters[3].Value := Now;
    ADOQuery1.ExecSQL;
  end;

  ADOQuery1.SQL.Text := 'select * from TEST_PERF_ADO';
  ADOQuery1.Open;
  ADOQuery1.Last;

  tc := Now - tc;
  mem := MemoryUsed - mem;
  Memo1.Lines.Add('ADO ORacle: Time: ' + FloatToStr(tc * 24 * 60 * 60) + ' sec; Memory used: ' + IntToStr(mem));
  ADOConnection1.Close;

end;

Posted: **Tue 02 Apr 2019 11:18**

Indeed, in the example you provided, there is no difference in the speed of the data access components. In your scenario, UniDAC and ADO perform the same scenario To speed up the loading of a large number of records with UNIDAC, you can use the Batch operation. For example, the data loading in your example will look as follows:

Code: Select all

   ...
   UniQuery1.Params.ValueCount := 150000;
   for i := 0 to UniQuery1.Params.ValueCount-1 do begin
     UniQuery1.Params[0][i].AsInteger := i;
     UniQuery1.Params[1][i].AsString := 'Test ' + IntToStr(i);
     UniQuery1.Params[2][i].AsFloat := Random(1000000) / 100;
     UniQuery1.Params[3][i].AsDateTime := Now;
   end;
   UniQuery1.Execute(UniQuery1.Params.ValueCount);
   ...

Please be advised to use the e-support form (https://www.devart.com/company/contactform.html) for sending any additional information on the issue in question (e.g. source codes of a test example, scripts for creating a database objects, screenshots, etc).

Devart Forums

Unidac vs ADO performence with ORacle.

Unidac vs ADO performence with ORacle.

Re: Unidac vs ADO performence with ORacle.