Unanswered: ADO Recordset speed: filter or close/reopen?
I'm looking for information on what is faster for changing the "focus" an ADO recordset. In general, is it faster to close the recordset, change the SQL source and reopen it, or should I instead use the .sort and filter methods of the ADO recordset?
I'm using an ADO recordset in VBA to query a table of dates flagged as working or non-working days. (1 or 0) I have a need to adjust the recordset often to specific date ranges or excluding one of the flag types or both. The table itself contains four years of dates and flags (1400-1500) records, but usually I only need to pull 1,2,8,10 or 42 days at a time.
So, which would be faster? To load the table as a recordset once and filter/sort as needed, or essentially create a new recordset whenever I need to pull 1-42 records? Or does it even matter with only 1400-1500 records?
I would (frankly) suck it and see, but if you forced my hand I would plump for opening and closing. It depends on a few things though:
Is this a FE\ BE set up?
What is the database engine?
Are the indexes & query correct, so data is retrieved optimally (rather than a table scan)?
How many FEs?
Thanks for the reply, pootle. I had considered testing this myself, but I wasn't sure how to test it. I mean, it runs fast enough one time through either way that I couldn't use a stopwatch to time it.
As for the other info:
1) No, this is not a FE/BE setup.
2) Database engine is Jet, I believe. (it's a table stored, not linked in any way, in the same MDB file as the VBA code, so I'm guessing that means Jet...)
3) I'm not sure what you're asking about optimal data retrieval versus a table scan. If you're asking if I'm looping through the recordset to check for matches, then no. The table is indexed by the date field.
Also, I forgot to mention that I'm using Access 2002. (Hooray for lazy corporate IT!) I could also change over and do DAO instead of ADO if that would be faster, but I was concerned about not being able to get the record count without doing a .movelast.
For something like this then I would go with the easiest means. The *proper* way to do this would be to requery each time but this depends a lot on the sorts of things I asked. TBH - if it is too fast to time for a single user app then good enough is good enough!
How long does the movelast take? That causes the recordset to get fully populated.