oracle 德尔福中的 BDE 与 ADO

Question

提问by JosephStyons

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.

我们最近修改了一个大型 Delphi 应用程序以使用 ADO 连接和查询而不是 BDE 连接和查询。自从那次变化之后，性能变得很糟糕。

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.

我已经对应用程序进行了分析，瓶颈似乎出现在对TADOQuery.Open. 换句话说，从代码的角度来看，除了重构应用程序以减少实际使用数据库之外，我无能为力。

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.

有人对如何提高连接 ADO 的 Delphi 应用程序的性能有任何建议吗？我已经尝试了这里给出的两个建议，几乎没有任何影响。

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

在 BDE 下：11 秒
在 ADO 下：73 秒
在那篇文章中引用的更改后的 ADO 下：72 秒

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

我们在客户端-服务器环境中使用 Oracle 后端。每台本地机器都与数据库保持单独的连接。

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:

回答zendar提出的问题：

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

我在 Windows Vista 和 XP 上使用 Delphi 2007。

The back end is an Oracle 10g database.

后端是一个Oracle 10g 数据库。

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

如连接字符串所示，我们使用的是 OraOLEDB 驱动程序。

The MDAC version on my benchmark machine is 6.0.

我的基准机器上的 MDAC 版本是 6.0。

Edit:

编辑：

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

在 BDE 下，我们有很多看起来像这样的代码：

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.Addis actually very expensive under ADO, because the QueryChangedevent is fired every time you change the CommandText. So replacing the above with this was MUCH faster:

但是我们发现Sql.Add在 ADO 下调用 to其实开销很大，因为QueryChanged每次更改CommandText. 所以用这个替换上面的要快得多：

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 TADOQueryout of ADODB.pas, rename it under a new name, and rip out the QueryChangedevent, 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.

更好的是，您可以TADOQuery从 ADODB.pas 中复制出来，将其重命名为新名称，然后删除该QueryChanged事件，据我所知，这根本没有任何用处。然后使用新的、修改过的 TADOQuery 版本，而不是原生版本。

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

回答by zendar

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

我不知道 Delphi 2007，但我对 Delphi 7 和 Oracle 8 做了同样的事情。

Here are things I did:

以下是我做的事情：

Set TAdoDataSet.CursorLocationaccording to query:
- clUseClientif query fetches records for GUI and query is relatively "simple" - no grouping or sum
- clUseServerif query have some sort of aggregation (sum, grouping, counting)
Set TAdoDataSet.CursorTypeaccording to query:
- ctForwardOnlyfor reports where you don't need scroll back through dataset - works only with clUseServer
- ctStaticfor GUI. This is only mode that works with clUseClient
Set TAdoDataSet.LockTypeaccording to query:
- ltReadOnlyfor every dataset that is not used for editing (grids, reports)
- ltOptimisticwhen records are posted to database immediately after change (e.g. user editing data on form)
- ltBatchOptimisticwhen 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 TAdoQUerywith 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?

根据查询设置TAdoDataSet.CursorLocation：
- clUseClient如果查询为 GUI 获取记录并且查询相对“简单” - 没有分组或求和
- clUseServer如果查询有某种聚合（总和、分组、计数）
根据查询设置TAdoDataSet.CursorType：
- ctForwardOnly用于不需要回滚数据集的报告 - 仅适用于clUseServer
- GUI 的ctStatic。这是唯一适用于clUseClient 的模式
根据查询设置TAdoDataSet.LockType：
- ltReadOnly用于每个不用于编辑的数据集（网格、报告）
- ltOptimistic当记录在更改后立即发布到数据库时（例如用户在表单上编辑数据）
- ltBatchOptimistic当您更改大量记录时。这适用于您获取记录数量，然后对它们进行一些处理，然后将更新批量发送到数据库的情况。这与 clUseClient 和 ctStatic 结合使用效果最佳。
根据我的经验，用于 Oracle 的 Microsoft OLEDB 提供程序比 Oracle OleDb 提供程序工作得更好。你应该测试一下。
编辑：查看 Fabricio 对可能的 blob 问题的评论。
更换TAdoQUery与TAdoDataSet。TAdoQuery 是为将应用程序从 BDE 转换为 ADO 而创建的，但 Borland/Codegear 推荐使用 TAdoDataSet
重新检查 Oracle 连接字符串以确保您没有网络延迟。连接Oracle需要多长时间？TnsPing 是多长时间？

Answer 2

回答by Ian Boyd

i found the performance problems with ADOExpress years ago:

几年前我发现了 ADOExpress 的性能问题：

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

ADO 与 ADOExpress 计时赛。不适合 ADOExpress(6/7/2005)
ADO 与 ADO Express 计时赛 (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.

注意：在 ADO 成为 Delphi 的标准部分之前，Borland 将其作为名为ADOExpress的插件出售。它只是 Microsoft 的 ActiveX 数据对象 (ADO) COM 对象的对象包装器。

i had tested three scenarios

我测试了三个场景

using ADO directly (i.e. Microsoft's COM objects directly)
using ADOExpress (Borland's object wrappers around ADO)
specifying .DisableControlson the TADOQuerybefore calling Open

直接使用 ADO（即直接使用微软的 COM 对象）
使用 ADOExpress（Borland 围绕 ADO 的对象包装器）
指定.DisableControls在TADOQuery之前调用Open

i discovered

我发现

use Query.DisableControlsto make each call .Next50x faster
use Query.Recordset.Fields.Items['columnName'].Valuerather 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

用于Query.DisableControls使每次调用.Next速度提高 50 倍
使用Query.Recordset.Fields.Items['columnName'].Value而不是Query.FieldByName('columnName')使每个值查找速度提高 2.7 倍

使用TADODataSet(verses TADOQuery) 没有区别

                                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.

注意：这些值用于循环 20,881 行，并查找 21 列的值。

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:

使用 DisableControls 使循环速度提高 5000%：

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:

使用 Fields 集合使值查找速度提高 270%：

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

回答by Arnaud Bouchez

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

为了获得最佳性能，应该查看我们的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:

如果你正在处理大量的 TQuery，而不使用 DB 组件，我们有一个专用的伪类来使用直接 OCI 连接，例如：

 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:

我通过后期绑定 Variant 添加了一些独特的访问权限，以编写如下直接代码：

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...

请注意，所有 OleDB 提供程序在处理 BLOB 方面都有缺陷：Microsoft 的版本只是不处理它们，而 Oracle 的版本将随机返回 1/4 行的 null......

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 Clientprovided 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.

在实际数据库中，我发现我们的直接 OCI 类比 OleDB 提供程序快 2 到 5 倍，而无需安装此提供程序。您甚至可以使用Oracle提供的Oracle Instant Client，它允许您在不安装标准（大型）Oracle 客户端或没有 ORACLE_HOME 的情况下运行您的应用程序。只需将 dll 文件与您的应用程序放在同一目录中，它就会起作用。

oracle 德尔福中的 BDE 与 ADO

提问by JosephStyons

回答by zendar

回答by Ian Boyd

回答by Arnaud Bouchez

相关推荐

最近更新

标签

oracle 德尔福中的 BDE 与 ADO

提问by JosephStyons

回答by zendar

回答by Ian Boyd

回答by Arnaud Bouchez

相关推荐

为什么我的 Perl CGI 脚本找不到 Oracle DBD？

oracle 慢更新声明

Oracle SQL 中“@”符号的含义是什么？

如何从备份文件（ora 文件）重新创建 oracle 9i 数据库

相关推荐

最近更新

标签