title: Unidac 5.0 performance degradation
body: <p>We migrated from UniDAC 4.1 to 5.0 (simply rebuilded or app with new version) and catch huge degradation in our datapump routines.</p>
  
  <p>Running pgfouine catch many queries like:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>It executes more than 1,100,000 times for 210,000 data inserts.
  Why it had happened and how avoid it on current version?</p>
  
  <p>We does TUniQuery.Prepare once before insert loop and reassign query params inside.
  On UniDAC 4.1 it works fine.</p>
  
  <p>UPD.
  Searching minimal reproduceable application/database i found out, that it occurs if 
  we use domain datatypes in tables.</p>
  
  <pre><code>    CREATE DOMAIN id_dom AS integer NOT NULL;
      CREATE TABLE test_table (id id_dom);
  </code></pre>
  
  <p>Minimal application:</p>
  
  <pre><code>    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.
  </code></pre>
  
  <p>if used usual data types all is ok.</p>
  
  <p>UPD2:</p>
  
  <h2><a href="http://forums.devart.com/viewtopic.php?p=91773#p91773" rel="nofollow">Answer from DevArt:</a></h2>
  
  <blockquote>
    <p>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.</p>
  </blockquote>