Unidac 4.1->5.0 performance degradation with PostgreSQL

[v.beresnev]

We migrated from UniDAC 4.1 to 5.0 (simply rebuilded our app with new version) and catch huge degradation in our datapump routines.

Running pgfouine catch many queries like:

Code: Select all

	
        SELECT current_database() AS DATATYPE_CATALOG, 
	n.nspname AS DATATYPE_SCHEMA,
	t.typname AS DATATYPE_NAME,
	t.oid AS DATATYPE_OID,
	t.typlen AS DATATYPE_LENGTH,
	CASE WHEN t.typtype = 'b' THEN 'base' 
		WHEN t.typtype = 'c' THEN 'composite' 
		WHEN t.typtype = 'd' THEN 'domain' 
		WHEN t.typtype = 'e' THEN 'enum' 
		WHEN t.typtype = 'p' THEN 'pseudo' 
	END::varchar(9) AS DATATYPE_TYPE,
	t.typrelid AS TABLE_OID, 
	t.typbasetype AS DATATYPE_BASETYPE 
	FROM pg_type t 
	INNER JOIN pg_namespace n ON n.oid = t.typnamespace 
	WHERE t.oid = '' AND t.typtype = '' 
	ORDER BY n.nspname, t.typname;

It executes more than 1,100,000 times for 210,000 data inserts. (on each field assignment, I think).
Why it had happened and how avoid it on current version?

We does TUniQuery.Prepare once before insert loop and reassign query params inside like this:

Code: Select all

    ParCtr := Math.Min(qu.Params.Count - 1, High(VarParams));
    for i := 0 to ParCtr do
      if not VarIsEmpty(VarParams[i]) then
          qu.Params[i].Value := VarParams[i]
      else qu.Params[i].Clear;
    qu.Execute

On UniDAC 4.1 it works fine.

[DemetrionQ]

Hello.

I couldn't reproduce the problem. I used the following code for the check:

Table Create SQL Script:

Code: Select all

CREATE TABLE Test_Table (
  EMPNO INTEGER,
  ENAME VARCHAR(10),
  JOB VARCHAR(9),
  PRIMARY KEY (EMPNO)
);

Delphi code:

Code: Select all

{$APPTYPE CONSOLE}

uses
  SysUtils, PostgreSQLUniProvider, Uni;

var
  UConn: TUniConnection;
  UQuery: TUniQuery;
  i: integer;
begin
  randomize;
  UConn := TUniConnection.Create(Nil);
  try
    UQuery := TUniQuery.Create(Nil);
    try
      UConn.ProviderName := 'PostgreSQL';
      UConn.Server := 'db';
      UConn.Port := 5439;
      UConn.Username := 'postgres';
      UConn.Password := 'postgres';
      UConn.Database := 'deimos_db';
      UConn.Open;
      UQuery.Connection := UConn;
      UQuery.SQL.Text := 'delete from Test_Table where empno > 100 and empno < 201';
      UQuery.ExecSQL;

      UQuery.SQL.Text := 'insert into Test_Table(empno, ename, job) values(:id, :name, :job)';
      UQuery.Prepare;
      for i := 101 to 150 do begin
        UQuery.ParamByName('id').AsInteger := i;
        UQuery.ParamByName('name').AsString := IntToHex(random(999999999), 8);
        UQuery.ParamByName('job').AsString := IntToHex(random(999999999), 8);
        UQuery.ExecSQL;
      end;

      UQuery.SQL.Text := 'select * from Test_Table';
      UQuery.Prepare;
      UQuery.Open;
      for i := 151 to 200 do begin
        UQuery.Append;
        UQuery.FieldByName('empno').AsInteger := i;
        UQuery.FieldByName('ename').AsString := IntToHex(random(999999999), 8);
        UQuery.FieldByName('job').AsString := IntToHex(random(999999999), 8);
        UQuery.Post;
      end;
    finally
      UQuery.Free;
    end;
  finally
    UConn.Free;
  end;
end.

Metadata retrieving query was called only once.

Please test this sample. If the problem is not reproduced, edit the code, so that the problem repeats.

[v.beresnev]

ok. Already did it.
I found out, problem was in domain data types.
With usual types is all ok.

Code: Select all

CREATE DOMAIN id_dom AS integer NOT NULL;
CREATE TABLE test_table (id id_dom);

Code: Select all

program minProject;

{$APPTYPE CONSOLE}
uses
  Messages, SysUtils, Variants,
  DB, DBAccess, Uni, UniProvider, PostgreSQLUniProvider;

const text = 'insert into test_table (id) values (:id)';

var
  qu:TUniQuery;
  PostgreSQL: TPostgreSQLUniProvider;
  UniConnection1: TUniConnection;
  i: Integer;
  ids: Variant;

begin
  { TODO -oUser -cConsole Main : Insert code here }
  UniConnection1 := TUniConnection.Create(nil);
  PostgreSQL := TPostgreSQLUniProvider.Create(UniConnection1);
  UniConnection1.ProviderName := 'PostgreSQL';
  UniConnection1.Username := '';
  UniConnection1.Password := '';
  UniConnection1.Server := '';
  UniConnection1.Database := 'test';
  UniConnection1.SpecificOptions.Values['ProtocolVersion'] := 'pv30';
  ids := VarArrayOf([41750, 41751, 41752]);
  qu := TUniQuery.Create(UniConnection1);
  qu.Connection := UniConnection1;
  qu.SQL.Text := text;
  qu.Prepare;
  for I := 0 to 2 do
  begin
    qu.ParamByName('id').AsInteger := ids[i];
    qu.Execute;
  end;
  qu.Close();
end.

[DemetrionQ]

Thank you for the information. We have reproduced the problem and are investigating it. We will inform you as soon as we have any results.

[v.beresnev]

thank you.

[DemetrionQ]

Hello.

We have fixed the problem. We will add the fix in the next build.