BDE与ADO在德尔福 [英] BDE vs ADO in Delphi
问题描述
请注意下面的编辑更多信息和可能的解决方案
Please note the Edit below for a lot more information, and a possible solution
我们最近修改了一个大型Delphi应用程序来使用ADO连接和查询，而不是BDE连接和查询。因为这个变化，性能已经变得可怕了。
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.
我已经分析了应用程序，并且瓶颈似乎是在实际调用 TADOQuery.Open 。换句话说，除了重组应用程序以实际使用数据库之外，从代码角度来看，我不能做很多事情。
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.
有没有人有关于如何提高ADO连接的Delphi应用程序的性能的建议？我已经尝试了这里给出的建议，几乎没有任何影响。
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:
- p>在BDE下：11秒
Under BDE: 11 seconds
在ADO下：73秒
在该文章引用的更改后：72秒
Under ADO after the changes referenced by that article: 72 seconds
我们正在使用客户端服务器中的Oracle后端环境。本地机器每个都保持与数据库的单独连接。
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=*******';
回答zendar提出的问题：
To answer the questions posed by zendar:
我在Windows Vista和XP上使用Delphi 2007。
I'm using Delphi 2007 on Windows Vista and XP.
后端是一个Oracle 10g数据库。
The back end is an Oracle 10g database.
如连接字符串所示，我们使用OraOLEDB驱动程序。
As indicated by the connection string, we are using the OraOLEDB driver.
我的基准计算机上的MDAC版本为6.0。
The MDAC version on my benchmark machine is 6.0.
编辑：
在BDE下，我们有很多代码如下：
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
但是我们发现调用 Sql.Add 在ADO下实际上非常昂贵，因为每次更改 CommandText 时， QueryChanged 事件被触发。所以用这个代替上面这个更快：
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
更好的是，您可以复制 TADOQuery out of ADODB.pas，将其重新命名为一个新的名称，并撕掉 QueryChanged 事件，据我所知，这是没有任何有用的东西。然后使用您的新的修改版本的TADOQuery，而不是原生的。
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;
推荐答案
我不知道Delphi 2007，在Delphi 7和Oracle 8中也做了同样的事情。
I don't know about Delphi 2007, but I did same thing with Delphi 7 and Oracle 8.
这是我做的事情：
- 根据查询设置 TAdoDataSet.CursorLocation ：
- clUseClient 如果查询提取用于GUI和查询的记录是相对简单的 - 没有分组或总和
- clUseServer 如果查询有某种排序汇总（sum，grouping，counting）
- 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)
- ctForwardOnly 对于不需要滚动浏览数据集的报表，仅适用于 clUseServer
- ctStatic 这只适用于 clUseClient
- 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
- ltReadOnly
- ltOptimistic 当更改后立即将数据发布到数据库（例如用户在表单上编辑数据） $ b $当您更改大量记录时，b
- ltBatchOptimistic 。这是为了获取记录数量的情况，然后对它们进行一些处理，然后批量发送更新到数据库。这与clUseClient和ctStatic相结合。
- 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.
这篇关于BDE与ADO在德尔福的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
