vba 使用过滤和聚合函数查询预先存在的内存中 ADODB 记录集（高级 ADODB 库？）

Querying a pre-existing in-memory ADODB recordset, using filtering and aggregate functions (advanced ADODB library?)

提问by tbone

I have some existing code that is querying a SQL database repeatedly with different parameters and I thought it would likely perform better if I changed it to select one big chunk of data into an ADODB.Recordset at the start, and then within the loop query this recordset rather than the database itself.

我有一些现有代码使用不同的参数重复查询 SQL 数据库，我认为如果我将其更改为在开始时将一大块数据选择到 ADODB.Recordset 中，然后在循环中查询它，它的性能可能会更好记录集而不是数据库本身。

One additional caveat is that I need to use aggregate functions (SUM,MIN,MAX,AVG) when I am performing these sub-queries.

另外一个警告是，当我执行这些子查询时，我需要使用聚合函数（SUM、MIN、MAX、AVG）。

Coding this wouldn't be too terribly difficult, but something this obvious seems like it would have been done thousands of times before, making me wonder if there might be an open source library of some sort out there that contains this type of functionality? I swear I encountered one a few years back but am unable to track it down on google.

对此进行编码不会太困难，但这种显而易见的事情似乎在之前已经完成了数千次，这让我想知道是否有某种开源库包含这种类型的功能？我发誓几年前我遇到过一个，但我无法在谷歌上找到它。

EDIT:
A good suggestion (by TimW) in the comments was to do all the aggregation on the database server and pass back to the client, and then just do the filtering on the client. (Although, in this case it won't work, as 2 of the columns with filtering being applied are DateTime columns)

编辑：评论中的
一个很好的建议（由 TimW 提出）是在数据库服务器上进行所有聚合并传递回客户端，然后在客户端上进行过滤。（尽管在这种情况下它不起作用，因为应用了过滤的 2 列是 DateTime 列）

UPDATE

更新

Here is the library I previously encountered:
http://code.google.com/p/ado-dataset-tools/

这是我之前遇到的库：http:
//code.google.com/p/ado-dataset-tools/

Not sure if the author has abandoned it or not (his plan seemed to be to update it and convert to c#), but the VBA versions of the various libraries seem to be available here:
http://code.google.com/p/ado-dataset-tools/source/browse/trunk/ado-recordset-unit-tests.xls?spec=svn8&r=8#ado-recordset-unit-tests.xls

不确定作者是否已经放弃它（他的计划似乎是更新它并转换为 c#），但各种库的 VBA 版本似乎可以在这里找到：http:
//code.google.com/p /ado-dataset-tools/source/browse/trunk/ado-recordset-unit-tests.xls?spec=svn8&r=8#ado-recordset-unit-tests.xls

The specific ADO library I was interested in is here:
http://code.google.com/p/ado-dataset-tools/source/browse/trunk/ado-recordset-unit-tests.xls/SharedRecordSet.bas

我感兴趣的特定 ADO 库在这里：http:
//code.google.com/p/ado-dataset-tools/source/browse/trunk/ado-recordset-unit-tests.xls/SharedRecordSet.bas

See specifically the GroupRecordSet()function.
Only SUM,MIN,MAX aggregate functions seem to be supported.

具体参见GroupRecordSet()函数。
似乎只支持 SUM、MIN、MAX 聚合函数。

Another possible alternative (if running within Excel)

另一种可能的选择（如果在 Excel 中运行）

Writing SQL Queries Against Virtual Tables in Excel VBA
http://www.vbaexpress.com/forum/showthread.php?t=260
Not sure how this would perform, but pulling the raw data (with partial pre-aggregation) into a local worksheet in Excel, and then using that worksheet as a datasource in subsequent queries might be a viable option.

在 Excel VBA 中针对虚拟表编写 SQL 查询
http://www.vbaexpress.com/forum/showthread.php?t=260
不确定这将如何执行，但将原始数据（带有部分预聚合）提取到本地Excel 中的工作表，然后将该工作表用作后续查询中的数据源可能是一个可行的选择。

采纳答案by tbone

From my research into this subject, there is no easy solution or existing libraries or commercial products. The only viable solution from what I can tell is to bite the bullet and hand code a solution, which is more work than it's worth to me.

从我对这个主题的研究来看，没有简单的解决方案或现有的库或商业产品。据我所知，唯一可行的解​​决方案是咬紧牙关，手动编写一个解决方案，这对我来说比它的价值要多。

So I am marking this as the correct answer despite it not being the solution to the problem. :)

因此，尽管它不是问题的解决方案，但我仍将其标记为正确答案。:)

回答by HK1

My own experience is that it's actually far more efficient to make many small calls to the database than it is to load large amounts of data into a recordset and then try to filter/query that data.

我自己的经验是，与将大量数据加载到记录集中然后尝试过滤/查询该数据相比，对数据库进行多次小调用实际上要高效得多。

I'm also under the impression that your ability to filter/query data in an existing ADO recordset is fairly limited in comparison to making individual calls to the database. Back when I was trying to do this I thought it should be as simple as creating a second ADO recordset by querying the first one using SQL. I never did find a way to do that; I'm pretty sure it isn't possible.

我还认为，与对数据库进行单独调用相比，您在现有 ADO 记录集中过滤/查询数据的能力相当有限。当我尝试这样做时，我认为它应该像通过使用 SQL 查询第一个 ADO 记录集来创建第二个 ADO 记录集一样简单。我从来没有找到办法做到这一点；我很确定这是不可能的。

Edit1
To help you understand the difference, I wrote some code that read in new price data from a text file and updated prices in a Visual Foxpro database using ADO and the VFP OLE driver. The table I was querying had about 650,000 records. I thought it would be best to load all the records in a recordset and then use ADO's filter method. When I did this it took my code three to four hours to run. I changed my code to just look up each record, one at a time, and my code then ran in one minute and two seconds. I posted about this problem on SO. You can take a look at the various responses I received: Speed up this Find/Filter Operation - (VB6, TextFile, ADO, VFP 6.0 Database)

Edit1
为了帮助您理解差异，我编写了一些代码，这些代码使用 ADO 和 VFP OLE 驱动程序从文本文件中读取新价格数据并在 Visual Foxpro 数据库中更新价格。我查询的表有大约 650,000 条记录。我认为最好加载记录集中的所有记录，然后使用 ADO 的过滤方法。当我这样做时，我的代码运行了三到四个小时。我将代码更改为只查找每条记录，一次一个，然后我的代码在一分两秒内运行。我在 SO 上发布了有关此问题的信息。你可以看看我收到的各种回复：加速这个查找/过滤操作 - (VB6, TextFile, ADO, VFP 6.0 Database)

回答by Bob77

If your performance issue stems from a remote SQL Server database over a slow connection then local caching might make a certain amount of sense if you have to work with the data intensively.

如果您的性能问题源于通过慢速连接的远程 SQL Server 数据库，那么如果您必须密集地处理数据，本地缓存可能会有一定的意义。

One way to get a lot of versatility would be to use a local Jet MDB as your cache.

获得多种功能的一种方法是使用本地 Jet MDB 作为缓存。

You could do the initial "caching" query using Jet to do a SELECT from your remote external SQL Server database INTO a local table, then CREATE indexes on it. From there you could perform any number of subsequent queries against the local table. When you need to work with another subset just DROP the local table and indexes, and requery the remote database.

您可以使用 Jet 执行初始“缓存”查询，从远程外部 SQL Server 数据库执行 SELECT 到本地表，然后在其上创建索引。从那里您可以对本地表执行任意数量的后续查询。当您需要使用另一个子集时，只需删除本地表和索引，然后重新查询远程数据库。

But unless your remote connection path is slow this usually doesn't buy you so much.

但是除非您的远程连接路径很慢，否则这通常不会给您带来太多好处。