StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBDE vs ADO in Delphi
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload