Query a pre-existing in-memory ADODB recordset using filtering and aggregation functions (extended ADODB library?)
I have some existing code that repeatedly queries the SQL database with different parameters and I thought it would most likely work better if I changed it to select one big chunk of data in ADODB.Recordset at the beginning and then within the loop it queries that recordset, not the database itself.
Another caveat is that I need to use aggregate functions (SUM, MIN, MAX, AVG) when I execute these subqueries.
Coding it wouldn't be too terribly difficult, but something so obvious seems like it would have been done thousands of times before, making me wonder if there might be some open source library that contains this type of functionality? I swear I ran into it years ago but can't 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 pipe back to the client, and then just do the filtering on the client. (Although it won't work in this case, since the 2 filtered columns apply DateTime columns)
UPDATE
Here is the library I came across before:
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 it to C #), but VBA versions of 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
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
See in particular the GroupRecordSet () function .
It seems only the aggregated functions SUM, MIN, MAX are supported.
Another possible alternative (if done in Excel)
Writing SQL queries against virtual tables in Excel VBA
http://www.vbaexpress.com/forum/showthread.php?t=260
Not sure how this would be done, but pull raw data (with partial pre-aggregation) into local worker a worksheet in Excel and then using that worksheet as a data source in subsequent queries might be a viable option.
source to share
From my research on this, there is no simple solution or existing libraries or commercial products. The only viable solution from what I can say is to bite the bullet and hand-code a solution that's more than it's costing me.
So I consider this to be the correct answer, even though it is not a solution to the problem. :)
source to share
My own experience is that it is actually much more efficient to make many small calls to the database than to load large amounts of data into a recordset and then try to filter / query that data.
I also get the impression that your ability to filter / query data in an existing ADO recordset is rather limited when compared to individual calls to the database. When I tried to do this, I thought it should be as simple as creating a second ADO Recordset by querying the first using SQL. I've never found a way to do this; I'm sure this is not possible.
Edit1
To help you understand the difference, I wrote code that reads new pricing data from a text file and updated pricing 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 the ADO filter method. When I did it, it took me three to four hours for my code. I changed my code to just search for each entry, one at a time, and my code ran after a minute and two seconds. I posted about this issue on SO. You can take a look at the various answers I received: Speed up search / filtering operation - (VB6, TextFile, ADO, VFP 6.0 database)
source to share
If the performance issue is with a remote SQL Server database over a slow connection, then local caching might make some sense if you need to be data intensive.
One way to get more versatility would be to use the local Jet MDB as the cache.
You can run an initial "cache" query using Jet to SELECT from your remote external SQL Server database to a local table and then CREATE indexes on it. From there, you can perform any number of subsequent queries against the local table. When you need to work with a different subset, just DROP the local table and indexes and query the remote database.
But if your dial-up path is slow, it usually doesn't buy you that much.
source to share