Delphi性能:读取数据集中的所有值。

We're trying to find out some performance fixes reading from a TADOQuery. Currently, we loop through the records using 'while not Q.eof do begin ... Q.next method. For each, we read ID and Value of each record, and add each to a combobox list.

我们正在尝试从TADOQuery中找到一些性能修正。目前，我们使用“while not Q”循环遍历记录。eof开始…Q。下一个方法。对于每个记录，我们读取每个记录的ID和值，并将它们添加到一个combobox列表中。

Is there a way to convert all values of a specified field into a list in one shot? Rather than looping through the dataset? It would be really handy if I can do something like...

是否有一种方法可以将指定字段的所有值转换成一个列表?而不是通过数据集循环?如果我能做一些像……

TStrings(MyList).Assign(Q.ValuesOfField['Val']);

I know that's not a real command, but that's the concept I'm looking for. Looking for a fast response and solution (as always but this is to fix a really urgent performance issue).

我知道这不是真正的命令，但这就是我要找的概念。寻找快速响应和解决方案(一如既往，这是解决一个非常紧急的性能问题)。

8 个解决方案

#1

There are some great performance suggestions made by other folks that you should implement in Delphi. You should consider them. I will focus on ADO.

在Delphi中，有一些很棒的性能建议，你应该在Delphi中实现。你应该考虑他们。我将专注于ADO。

You haven't specified what the back end database server is, so I can't be too specific, but there are some things that you should know about ADO.

您还没有指定后端数据库服务器是什么，所以我不能太具体，但是您应该知道一些关于ADO的事情。

ADO RecordSet

ADO记录集

In ADO, there is a RecordSet object. That RecordSet object is basically your ResultSet in this case. The interesting thing about iterating through the RecordSet is that it's still coupled with the provider.

在ADO中，有一个RecordSet对象。这个RecordSet对象基本上就是这个例子中的结果集。在RecordSet中迭代的有趣之处是它仍然与提供者耦合。

Cursor Type

指针类型

If your cursor type is Dynamic or Delphi's default Keyset, then each time the RecordSet requests a new row from the provider, the provider will check to see if there were any changes before it returns the record.

如果您的游标类型是动态的或Delphi的默认密钥集，那么每次记录集从提供者请求一个新的行时，提供者将检查是否在它返回记录之前有任何更改。

So, for the TADOQuery where all you're doing is reading the result set to populate the combobox, and it's not likely to have changed, you should use the Static cursor type to avoid checking for updated records.

因此，对于TADOQuery，您所做的所有工作就是读取用于填充combobox的结果集，并且它不太可能发生更改，您应该使用静态游标类型来避免检查更新的记录。

In case you don't know what a cursor is, when you call a function like Next, you are moving the cursor, which represents the current record.

如果您不知道游标是什么，当您调用一个类似Next的函数时，您将移动游标，它表示当前的记录。

Not every provider supports all of the cursor types.

不是每个提供者都支持所有的游标类型。

CacheSize

CacheSize

Delphi's and ADO's default cache size for a RecordSet is 1. That's 1 record. This works in combination with the cursor type. The cachesize tells the RecordSet how many records to fetch and store at a time.

对于一个记录集，Delphi和ADO的默认缓存大小是1。这是1记录。这与游标类型相结合。cachesize告诉记录集一次获取和存储的记录数。

When you issue a command like Next (really MoveNext in ADO) with a cache size of 1, the RecordSet only has the current record in memory, so when it fetches that next record, it must request it from the provider again. If the cursor is not Static, that gives the provider the opportunity to get the latest data before returning the next record. So, a size of 1 makes sense for Keyset or Dynamic, because you want the provider to be able to get you the updated data.

当您发出类似Next的命令(实际上是ADO中的MoveNext)，缓存大小为1时，记录集只在内存中有当前记录，因此当它获取下一个记录时，它必须再次从提供者请求它。如果游标不是静态的，则提供给提供程序在返回下一个记录之前获取最新数据的机会。因此，对于Keyset或Dynamic来说，1的大小是有意义的，因为您希望提供者能够为您提供更新的数据。

Obviously, with a value of 1, there's communication between the provider and RecordSet each time move the cursor. Well, that's overhead that we don't want if the cursor type is static. So, increasing your cache size will reduce the number of round trips between the RecordSet and the provider. This also increases your memory requirements, but it should be faster.

显然，在值为1的情况下，提供者和记录集之间的通信每次都移动光标。如果游标类型是静态的，我们就不需要这样的开销。因此，增加缓存大小将减少记录集和提供者之间的往返次数。这也增加了您的内存需求，但是应该更快。

Also note that with a cache size greater than 1 for Keyset cursors, if the record that you want is in the cache, it won't request it from the provider again, which means that you won't see the updates.

还要注意，对于Keyset游标的缓存大小大于1，如果您想要的记录在缓存中，它不会再次从提供者请求它，这意味着您不会看到更新。

#2

Looking at your comment, here are a few suggestions:

看看你的评论，以下是一些建议:

There are a few things that are likely to be a bottleneck in this situation. The first is looking up the fields repeatedly. If you're calling FieldByName or FindField inside your loop, you're wasting CPU time recomputing a value that's not going to change. Call FieldByName once for each field you're reading from and assign them to local variables instead.

在这种情况下，有一些事情可能会成为瓶颈。第一个是反复查找字段。如果您在循环中调用FieldByName或FindField，那么您就是在浪费CPU时间来重新计算一个不会发生变化的值。为您正在读取的每个字段调用FieldByName，并将它们分配给局部变量。

When retrieving values from the fields, call AsString or AsInteger, or other methods that return the data type you're looking for. If you're reading from the TField.Value property, you're wasting time on variant conversions.

当从字段中检索值时，调用AsString或AsInteger，或其他返回您正在查找的数据类型的方法。如果你正在读TField。值属性，你在变转换上浪费时间。

If you're adding a bunch of items to a Delphi combo box, you're probably dealing with a string list in the form of the Items property. Set the list's Capacity property and make sure to call BeginUpdate before you start updating, and call EndUpdate at the end. That can enable some internal optimizations that makes loading large amounts of data faster.

如果将一堆条目添加到Delphi组合框中，那么您可能会以items属性的形式处理字符串列表。设置列表的容量属性，并确保在开始更新之前调用BeginUpdate，并在最后调用endpdate。这可以使一些内部优化能够更快地加载大量数据。

Depending on the combo box you're using, it could have some trouble dealing with large numbers of items in its internal list. See if it has a "virtual" mode, where instead of you loading everything up-front, you simply tell it how many items it needs, and when it gets dropped down, it calls an event handler for each item that's supposed to be shown on screen, and you give it the right text to display. This can really speed up certain UI controls.

根据您正在使用的组合框，在处理其内部列表中的大量项时可能会遇到一些麻烦。看看它是否有一个“虚拟”模式,而不是你加载所有的前期,你只要告诉它它需要多少项目,当它会掉下来,它调用事件处理程序为每个项目应该是显示在屏幕上,你给它正确的文本显示。这可以真正加快某些UI控件的速度。

Also, you should make sure your database query itself is fast, of course, but SQL optimization is beyond the scope of this question.

当然，您应该确保数据库查询本身是快速的，但是SQL优化超出了这个问题的范围。

And finally, Mikael Eriksson's comment is definitely worthy of attention!

最后，Mikael Eriksson的评论绝对值得关注!

#3

You can use Getrows. You specify the column(s) you are interested in and it will return an array with values. The time it takes to add 22.000 rows to a combo box goes from 7 seconds with the while not ADOQuery1.Eof ... loop to 1.3 seconds in my tests.

您可以使用Getrows。您指定感兴趣的列，它将返回一个具有值的数组。在一个组合框中添加22.000行的时间从7秒开始，而不是ADOQuery1。Eof……在我的测试中循环到1.3秒。

Sample code:

示例代码:

var
  V: Variant;
  I: Integer;
begin
  V := ADOQuery1.Recordset.GetRows(adGetRowsRest, EmptyParam, 'ColumnName');

  for I:= VarArrayLowBound(V, 2) to VarArrayHighBound(V, 2) do
    ComboBox1.Items.Add(V[0, I]));
end;

If you want more than one column in the array you should use a variant array as the third parameter.

如果您想要在数组中有多个列，那么应该使用变体数组作为第三个参数。

V := ADOQuery1.Recordset.GetRows(adGetRowsRest, EmptyParam, 
       VarArrayOf(['ColumnName1', 'ColumnName2']);

#4

You can't avoid looping. "Very long time" is relative but if retrieving 20000 records takes too long there's something wrong.

你不能避免循环。“很长时间”是相对的，但是如果检索20000条记录的时间太长，就会有问题。

• Check your query; perhaps the SQL can be improved (missing index?)
• 检查您的查询;也许SQL可以改进(缺失索引?)
• Show the code of your loop where you add items to the combobox. Maybe it can be optimized. (calling FieldByName repeatedly in a loop? using variants to retrieve field values?)
• 显示您的循环代码，在其中向combobox添加项。也许它可以被优化。(在循环中反复调用FieldByName) ?使用变量来检索字段值?
• Make sure to call ComboBox.Items.BeginUpdate; before the loop and ComboBox.Items.EndUpdate after.
• 确保调用ComboBox.Items.BeginUpdate;在循环和ComboBox.Items之前。EndUpdate之后。
• Use a profiler to find the bottleneck.
• 使用分析器来查找瓶颈。

#5

You could try pushing all data into a ClientDataSet and iterating this, but I think copying the data to the CDS does exactly what you are currently doing - looping and assigning.

您可以尝试将所有数据推入ClientDataSet并对其进行迭代，但我认为将数据复制到cd上正是您目前正在做的——循环和分配。

What I did once was concatenating values on the server, transmitting it in one bulk to the client and splitting it again. This actually made the system faster because it reduced the communication necessary between client and server.

我所做的是将服务器上的值连接起来，将其发送给客户机，然后再将其拆分。这实际上使系统更快，因为它减少了客户机和服务器之间的通信。

You have to look careful where the performance bottleneck is. It could as well be the combobox if you don't block GUI updates while adding values (ecpecially when we are talking about 20K values - that's a lot to scroll).

您必须注意性能瓶颈所在的位置。如果您不阻止GUI更新，同时添加值(当我们讨论20K值时(这是非常重要的)，那么它也可以是combobox。

Edit: When you cannot change the communication then you perhaps could make it asynchronous. Request new data in a thread, keep the GUI responsive, fill the combobox when the data is there. It means the user sees an empty combobox for 5 seconds, but at least he can do something else in the meantime. Doesn't change the amount of time needed, though.

编辑:当您不能更改通信时，您可能会使它异步。在线程中请求新数据，保持GUI响应，当数据存在时填充combobox。这意味着用户可以看到一个空的combobox 5秒，但至少他可以同时做些别的事情。不过，并没有改变所需的时间。

#6

Is your query also tied to some data aware controls or a TDataSource? If so, do your looping inside an DisableControls and EnableControls block so your visual controls don't update each time you move to a new record.

您的查询是否与某些数据感知控件或TDataSource绑定?如果是这样，则在DisableControls和EnableControls块中进行循环，这样您的可视化控件在每次移动到新记录时都不会更新。

Is the list of items fairly static? If so, consider creating a non-visual instance of a combobox at application startup, maybe inside a separate thread, and then assign your non-visual combobox to the visual combobox when your form is created.

项目列表是否相当静态?如果是这样，考虑在应用程序启动时创建一个combobox的非可视实例，可能在一个单独的线程中，然后在创建表单时将非可视的combobox分配给可视化的combobox。

#7

try use DisableControls and EnableControls for increase performance of linear process in dataset.

尝试使用DisableControls和EnableControls来提高数据集中的线性过程的性能。

   var
  SL: TStringList;
  Fld: TField;
begin
  SL := TStringList.Create;
  AdoQuery1.DisableControls;
  Fld := AdoQuery1.FieldByName('ListFieldName'); 
  try
    SL.Sorted := False; // Sort in the query itself first
    SL.Capacity := 25000; // Some amount estimate + fudge factor

    SL.BeginUpdate;  
    try
      while not AdoQuery1.Eof do
      begin
        SL.Append(Fld.AsString);
        AdoQuery1.Next;
      end;
    finally
      SL.EndUpdate;
    end;

    YourComboBox.Items.AddStrings(SL);

  finally
    SL.Free;
    AdoQuery1.EnableControls;
  end;
end;

#8

Not sure if this will help, but my suggestion would be not to add directly to the ComboBox. Load to a local TStringList instead, make that as fast as possible, and then use TComboBox.Items.AddStrings to add them all at once:

不确定这是否会有所帮助，但我的建议是不要直接添加到ComboBox。相反，要加载到本地TStringList，尽可能快地使用它，然后使用TComboBox.Items。AddStrings将它们同时添加:

var
  SL: TStringList;
  Fld: TField;
begin
  SL := TStringList.Create;
  Fld := AdoQuery1.FieldByName('ListFieldName'); 
  try
    SL.Sorted := False; // Sort in the query itself first
    SL.Capacity := 25000; // Some amount estimate + fudge factor
    SL.BeginUpdate;  
    try
      while not AdoQuery1.Eof do
      begin
        SL.Append(Fld.AsString);
        AdoQuery1.Next;
      end;
    finally
      SL.EndUpdate;
    end;
    YourComboBox.Items.BeginUpdate;
    try
      YourComboBox.Items.AddStrings(SL);
    finally
      YourComboBox.Items.EndUpdate;
    end;
  finally
    SL.Free;
  end;
end;