How to make a perfomance comparison?

[BavariaGIS]

Hello,

I want to compare the performance/speed of the devart pgDAC and some 3rd party components. Because the data-transfer-rate may be very similar, I want to test the performance by executing of a few hundred database queries. A single query looks like:

SELECT * FROM testtable WHERE testfield1 = value1 AND testfield2 = value2 ORDER BY id

wherein the testtable, testfield1 and testfield stay the same, but the values change from one query to another.

What devart components shall be used to get best performance results?

It seems that using PgQuery for one single query in the form

MyPgQuery.SQL.Text := 'SELECT * FROM testtable...
MyPgQuery.Open; // MyPgQuery.ExecSQL;
for i := 0 to MyPgQuery.1.RecordCount - 1 do
for j := 0 to 10 do
content := MyPgQuery.Fields[j].AsString;
PgQuery1.Next;
end;
MyPgQuery.Close;

will not be an efficient approach.

Can anyone give me some tips or advice?

[bork]

Hello

You can use the following code to check performance:

Code: Select all

var
  PgConnection: TPgConnection;
  PgQuery: TPgQuery;

  i, j, count: integer;
  insert_start, insert_finish: TDateTime;
  insert_ds_start, insert_ds_finish: TDateTime;
  select_start, select_finish: TDateTime;
  str1: WideString;
begin
  Randomize;
  count := 5000;

  PgConnection := TPgConnection.Create(nil);
  PgConnection.Server := ...;
  PgConnection.Port := ...;
  pgconnection.Database := ...;
  PgConnection.Username := ...;
  PgConnection.Password := ...;
  PgConnection.Open;

  PgQuery := TPgQuery.Create(nil);
  PgQuery.Connection := PgConnection;

  try
    PgConnection.ExecSQL('CREATE TABLE test_speed (' + #13 +
                         '  id integer NOT NULL, ' + #13 +
                         '  name character varying(250), ' + #13 +
                         '  value double precision, ' + #13 +
                         '  CONSTRAINT pk_test_speed PRIMARY KEY (id))', []);
  except
  end;

  PgConnection.ExecSQL('delete from test_speed', []);

  PgQuery.SQL.Text := 'insert into test_speed(id, name, value) values(:id, :name, :value)';
  PgQuery.Prepare;

  insert_start := Now;

  for i := 0 to count - 1 do
  begin
    str1 := '';
    for j := 0 to 199 do
      str1 := str1 + char(RandomRange(byte('a'), byte('z')));

    PgQuery.ParamByName('id').AsInteger := i;
    PgQuery.ParamByName('name').AsString := str1;
    PgQuery.ParamByName('value').AsInteger := i;
    PgQuery.ExecSQL;
  end;

  insert_finish := now;

  PgConnection.ExecSQL('delete from test_speed', []);

  PgQuery.Close;
  PgQuery.SQL.Text := 'select * from test_speed';
  PgQuery.KeyFields := 'ID';
  PgQuery.Options.PrepareUpdateSQL := True;
  PgQuery.Open;

  insert_ds_start := Now;

  for i := 0 to count - 1 do
  begin
    str1 := '';
    for j := 0 to 199 do
      str1 := str1 + char(RandomRange(byte('a'), byte('z')));

    PgQuery.Append;
    PgQuery.FieldByName('id').AsInteger := i;
    PgQuery.FieldByName('name').AsString := str1;
    PgQuery.FieldByName('value').AsInteger := i;
    PgQuery.Post;
  end;

  insert_ds_finish := now;

  PgQuery.Close;
  PgQuery.SQL.Text := 'select * from test_speed where id=:id';

  select_start := Now;

  for i := 0 to count - 1 do
  begin
    PgQuery.Close;
    PgQuery.ParamByName('id').AsInteger := i;
    PgQuery.Open;
  end;

  select_finish := now;

  ShowMessage('SQL insert: ' + FloatToStr((insert_finish - insert_start) * 24 * 60 * 60) + ' sec' + #13 +
              'Dataset insert: ' + FloatToStr((insert_ds_finish - insert_ds_start) * 24 * 60 * 60) + ' sec' + #13 +
              'Select: ' + FloatToStr((select_finish - select_start) * 24 * 60 * 60) + ' sec');

  PgQuery.Destroy;
  PgConnection.Destroy;            
end;

[/quote]

[BavariaGIS]

Thanks bork,

I tried your code an a compared it to pglib.dll and the ZEOS Lib. The results for devart PgDAC were really awful bad !!

Here is a result comparison for 10000 Select Queries on a local / remote PostgreSQL Server:

PostgreSQL PgLib.dll : 25s / 73s
devart PgDAC : 48s / 113s (Free Evaluation Version)
ZEOSLib : 27s / 77s

What may be the reason for such a bad result? Has SQL_ASCII coding of the SQL database any bad influence?

For the ZEOSLib have used exactly the correspondenting components and syntax!

[bork]

I am sorry, I forgot one very important line!

Please add query preparing here:

Code: Select all

  PgQuery.Close;
  PgQuery.SQL.Text := 'select * from test_speed where id=:id'; 
  PgQuery.Prepare; // <-- add this line !!!

With preparing PgDAC is faster then ZEOS.

Also you can set the AutoPrepare option of TPgQuery to True and preparing will be done automatically.

[bork]

Also please make sure that you are using the latest PgDAC version (2.00.0.3). In this version performance for simple queries that return a few records was highly improved. The latest trial version is always available at our site: http://www.devart.com/pgdac/download.html