BDE vs ADO in Delphi

Question

Please note the Edit below for a lot more information, and a possible solution

We recently modified a large Delphi application to use ADO connections and queries instead of BDE connections and queries. Since that change, performance has become terrible.

I've profiled the application and the bottleneck seems to be at the actual call to TADOQuery.Open. In other words, there isn't much I can do from a code standpoint to improve this, other than restructuring the application to actually use the database less.

Does anyone have suggestions about how to improve the performance of an ADO-connected Delphi application? I've tried both of the suggestions given here, with virtually no impact.

To give an idea of the performance difference, I benchmarked the same large operation:

• Under BDE: 11 seconds

• Under ADO: 73 seconds

• Under ADO after the changes referenced by that article: 72 seconds

We are using an Oracle back-end in a client-server environment. Local machines each maintain a separate connection to the database.

For the record, the connection string looks like this:

const
  c_ADOConnString = 'Provider=OraOLEDB.Oracle.1;Persist Security Info=True;' +
                    'Extended Properties="plsqlrset=1";' +
                    'Data Source=DATABASE.DOMAIN.COM;OPTION=35;' +
                    'User ID=******;Password=*******';

To answer the questions posed by zendar:

I'm using Delphi 2007 on Windows Vista and XP.

The back end is an Oracle 10g database.

As indicated by the connection string, we are using the OraOLEDB driver.

The MDAC version on my benchmark machine is 6.0.

Edit:

Under the BDE, we had a lot of code that looked like this:

procedure MyBDEProc;
var
  qry: TQuery;
begin
  //fast under BDE, but slow under ADO!!
  qry := TQuery.Create(Self);
  try
    with qry do begin
      Database := g_Database;
      Sql.Clear;
      Sql.Add('SELECT');
      Sql.Add('  FIELD1');
      Sql.Add(' ,FIELD2');
      Sql.Add(' ,FIELD3');
      Sql.Add('FROM');
      Sql.Add('  TABLE1');
      Sql.Add('WHERE SOME_FIELD = SOME_CONDITION');
      Open;
      //do something
      Close;
    end;  //with
  finally
    FreeAndNil(qry);
  end;  //try-finally
end;  //proc

But we found that the call to Sql.Add is actually very expensive under ADO, because the QueryChanged event is fired every time you change the CommandText. So replacing the above with this was MUCH faster:

procedure MyADOProc;
var
  qry: TADOQuery;
begin
  //fast(er) under ADO
  qry := TADOQuery.Create(Self);
  try
    with qry do begin
      Connection := g_Connection;
      Sql.Text := ' SELECT ';
        + '   FIELD1 '
        + '  ,FIELD2 '
        + '  ,FIELD3 '
        + ' FROM '
        + '  TABLE1 '
        + ' WHERE SOME_FIELD = SOME_CONDITION ';
      Open;
      //do something
      Close;
    end;  //with
  finally
    FreeAndNil(qry);
  end;  //try-finally
end;  //proc

Better yet, you can copy TADOQuery out of ADODB.pas, rename it under a new name, and rip out the QueryChanged event, which as far as I can tell, is not doing anything useful at all. Then use your new, modified version of TADOQuery, instead of the native one.

type
  TADOQueryTurbo = class(TCustomADODataSet)
  private
    //
  protected
    procedure QueryChanged(Sender: TObject);
  public
    FSQL: TWideStrings;
    FRowsAffected: Integer;
    function GetSQL: TWideStrings;
    procedure SetSQL(const Value: TWideStrings);
    procedure Open;
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    function ExecSQL: Integer; {for TQuery compatibility}
    property RowsAffected: Integer read FRowsAffected;
  published
    property CommandTimeout;
    property DataSource;
    property EnableBCD;
    property ParamCheck;
    property Parameters;
    property Prepared;
    property SQL: TWideStrings read FSQL write SetSQL;
  end;
////////////////////////////////////////////////////////
////////////////////////////////////////////////////////
////////////////////////////////////////////////////////
constructor TADOQueryTurbo.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FSQL := TWideStringList.Create;
  TWideStringList(FSQL).OnChange := QueryChanged;
  Command.CommandText := 'SQL'; { Do not localize }
end;

destructor TADOQueryTurbo.Destroy;
begin
  inherited;
 inherited Destroy;
  FreeAndNil(FSQL);
end;

function TADOQueryTurbo.ExecSQL: Integer;
begin
  CommandText := FSQL.Text;
  inherited;
end;

function TADOQueryTurbo.GetSQL: TWideStrings;
begin
  Result := FSQL;
end;

procedure TADOQueryTurbo.Open;
begin
  CommandText := FSQL.Text;
  inherited Open;
end;

procedure TADOQueryTurbo.QueryChanged(Sender: TObject);
begin
// if not (csLoading in ComponentState) then
//    Close;
// CommandText := FSQL.Text;
end;

procedure TADOQueryTurbo.SetSQL(const Value: TWideStrings);
begin
  FSQL.Assign(Value);
  CommandText := FSQL.Text;
end;

Answer 1

I don't know about Delphi 2007, but I did same thing with Delphi 7 and Oracle 8.

Here are things I did:

• Set TAdoDataSet.CursorLocation according to query:
  • clUseClient if query fetches records for GUI and query is relatively "simple" - no grouping or sum
  • clUseServer if query have some sort of aggregation (sum, grouping, counting)
• Set TAdoDataSet.CursorType according to query:
  • ctForwardOnly for reports where you don't need scroll back through dataset - works only with clUseServer
  • ctStatic for GUI. This is only mode that works with clUseClient
• Set TAdoDataSet.LockType according to query:
  • ltReadOnly for every dataset that is not used for editing (grids, reports)
  • ltOptimistic when records are posted to database immediately after change (e.g. user editing data on form)
  • ltBatchOptimistic when you change large number of records. This is for situations where you fetch number of records, then do some processing on them and then send updates to database in batch. This works best combined with clUseClient and ctStatic.
• In my experience, Microsoft OLEDB provider for Oracle worked better than Oracle OleDb provider. You should test that.
  Edit: Check Fabricio's comment about possible blob problems.
• Replace TAdoQUery with TAdoDataSet. TAdoQuery was created for conversion of apps from BDE to ADO, but Borland/Codegear recomendation was to use TAdoDataSet
• Recheck Oracle connection string to be sure that you do not have network latency. How long it lasts to connect to Oracle? How long is TnsPing?

Answer 2

i found the performance problems with ADOExpress years ago:

• ADO vs ADOExpress time trials. Not good for ADOExpress (6/7/2005)
• ADO vs ADO Express Time Trials (redux) (12/30/2007)

Note: Before ADO became a standard part of Delphi, Borland was selling it as an addon called ADOExpress. It was simply object wrappers around Microsoft's ActiveX Data Objects (ADO) COM objects.

i had tested three scenarios

• using ADO directly (i.e. Microsoft's COM objects directly)
• using ADOExpress (Borland's object wrappers around ADO)
• specifying .DisableControls on the TADOQuery before calling Open

i discovered

• use Query.DisableControls to make each call .Next 50x faster
• use Query.Recordset.Fields.Items['columnName'].Value rather than Query.FieldByName('columnName') to make each value lookup 2.7x faster

• using TADODataSet (verses TADOQuery) makes no difference

                                  Loop Results        Get Values 
  ADOExpress:                         28.0s              46.6s 
  ADOExpress w/DisableControls:        0.5s              17.0s 
  ADO (direct use of interfaces):      0.2s               4.7s 
  

Note: These values are for looping 20,881 rows, and looking up the values of 21 columns.

Baseline Bad Code:

var
   qry: TADOQuery;
begin
   qry := TADOQuery.Create(nil);
   try
      qry.SQL.Add(CommandText);
      qry.Open;
      while not qry.EOF do
      begin
         ...
         qry.Next;
      end;

Use DisableControls to make looping 5000% faster:

var
   qry: TADOQuery;
begin
   qry := TADOQuery.Create(nil);
   try 
      qry.DisableControls;
      qry.SQL.Add(CommandText);
      qry.Open;
      while not qry.EOF do
      begin
         ...
         qry.Next;
      end;

Use Fields collection to make value lookups 270% faster:

var
   qry: TADOQuery;
begin
   qry := TADOQuery.Create(nil);
   try 
      qry.DisableControls;
      qry.SQL.Add(CommandText);
      qry.Open;
      while not qry.EOF do
      begin
         value1 := VarAsString(qry.Recordset.Fields['FieldOne'].Value);
         value2 := VarAsInt(qry.Recordset.Fields['FieldTwo'].Value);
         value3 := VarAsInt64(qry.Recordset.Fields['FieldTwo'].Value);
         value4 := VarAsFloat(qry.Recordset.Fields['FieldThree'].Value);
         value5 := VarAsWideString(qry.Recordset.Fields['FieldFour'].Value);
         ...
         value56 := VarAsMoney(qry.Recordset.Fields['FieldFive'].Value);
         qry.Next;
      end;

---

Since it is a common enough problem, we created a helper method to solve the issue:

class function TADOHelper.Execute(const Connection: TADOConnection; 
       const CommandText: WideString): TADOQuery;
var
   rs: _Recordset;
   query: TADOQuery;
   nRecords: OleVariant;
begin
   Query := TADOQuery.Create(nil);
   Query.DisableControls; //speeds up Query.Next by a magnitude
   Query.Connection := Connection;
   Query.SQL.Text := CommandText;
   try
      Query.Open();
   except
      on E:Exception do
      begin
         Query.Free;
         raise;
      end;
   end;
   Result := Query;
end;

Answer 3

For best performance, should get a look at our Open Source direct access to Oracle.

If you are processing a lot of TQuery, without using the DB components, we have a dedicated pseudo-class to use direct OCI connection, as such:

 Q := TQuery.Create(aSQLDBConnection);
 try
   Q.SQL.Clear; // optional
   Q.SQL.Add('select * from DOMAIN.TABLE');
   Q.SQL.Add('  WHERE ID_DETAIL=:detail;');
   Q.ParamByName('DETAIL').AsString := '123420020100000430015';
   Q.Open;
   Q.First;    // optional
   while not Q.Eof do begin
     assert(Q.FieldByName('id_detail').AsString='123420020100000430015');
     Q.Next;
   end;
   Q.Close;    // optional
 finally
   Q.Free;
 end;

And I've added some unique access via a late-binding Variant, to write direct code as such:

procedure Test(Props: TOleDBConnectionProperties; const aName: RawUTF8);
var I: ISQLDBRows;
    Customer: Variant;
begin
  I := Props.Execute('select * from Domain.Customers where Name=?',[aName],@Customer);
  while I.Step do
    writeln(Customer.Name,' ',Customer.FirstName,' ',Customer.Address);
end;

var Props: TOleDBConnectionProperties;
begin
  Props := TSQLDBOracleConnectionProperties.Create(
    'TnsName','UserName','Password',CODEPAGE_US);
  try
    Test(Props,'Smith');
  finally
    Props.Free;
  end;
end;

Note that all OleDB providers are buggy for handling BLOBs: Microsoft's version just do not handle them, and Oracle's version will randomly return null for 1/4 of rows...

On real database, I found out our direct OCI classes to be 2 to 5 times faster than the OleDB provider, without the need to install this provider. You can even use the Oracle Instant Client provided by Oracle which allows you to run your applications without installing the standard (huge) Oracle client or having an ORACLE_HOME. Just deliver the dll files in the same directory than your application, and it will work.