title: BDE vs ADO in Delphi
body: <p><strong>Please note the Edit below for a lot more information, and a possible solution</strong></p>
  
  <p>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.</p>
  
  <p>I've profiled the application and the bottleneck seems to be at the actual call to <code>TADOQuery.Open</code>.  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.</p>
  
  <p>Does anyone have suggestions about how to improve the performance of an ADO-connected Delphi application?  I've tried both of the <a href="http://dn.codegear.com/print/27790" rel="noreferrer">suggestions given here</a>, with virtually no impact.</p>
  
  <p>To give an idea of the performance difference, I benchmarked the same large operation:</p>
  
  <ul>
  <li><p>Under BDE: 11 seconds</p></li>
  <li><p>Under ADO: 73 seconds</p></li>
  <li><p>Under ADO after the changes referenced by that article: 72 seconds</p></li>
  </ul>
  
  <p>We are using an Oracle back-end in a client-server environment. Local machines each maintain a separate connection to the database.</p>
  
  <p>For the record, the connection string looks like this:</p>
  
  <pre><code>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=*******';
  </code></pre>
  
  <p>To answer the questions posed by zendar:</p>
  
  <p>I'm using Delphi 2007 on Windows Vista and XP.</p>
  
  <p>The back end is an Oracle 10g database.</p>
  
  <p>As indicated by the connection string, we are using the OraOLEDB driver.</p>
  
  <p>The MDAC version on my benchmark machine is 6.0.</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>Under the BDE, we had a lot of code that looked like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But we found that the call to <code>Sql.Add</code> is actually very expensive under ADO, because the <code>QueryChanged</code> event is fired every time you change the <code>CommandText</code>. So replacing the above with this was MUCH faster:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Better yet, you can copy <code>TADOQuery</code> out of ADODB.pas, rename it under a new name, and rip out the <code>QueryChanged</code> 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.</p>
  
  <pre><code>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;
  </code></pre>