Make persistent a DAO recordset in a class module

0
votes

I am filtering a DAO recordset for sub-results as part of a set of recursive tasks. I'm trying to speed the routine, and I can see the recordset is being opened fresh every time the class object is instantiated. This step happens many hundreds of times. Isn't there a way to re-use it? The keyword here is persistence, isn't it?

I've tried setting the recordset in the Instantiate event, alternatively from within the functions. I've tried using static (instead of dim or private) to declare the recordset. I've also fiddled also with how the class object is declared and set.

I know a common solution is to change to a specific SQL source for each call, but the query that produces the recordset is itself slow so I don't see that as helpful. And yes, the base tables are optimally indexed.

I'm happy to post code, but is this enough for you to offer any tips?

1
ms-accessvbaclass
Note that indexing Yes/No aka Booklean fields can also help with performance. For example indexing a Yes/No field on a job table with 2000 records made a big differnece in performance in a query which also contained a table with 800K records. – Tony Toews
I place indexes where there will be sort/filter .... so if a Y/N field will be sorted or filtered, okay it gets an index. Do you mean that it helps to index a Y/N even if it doesn't enter into the query crunching? Or do you just mean that a Y/N needs indexing just like any other field, if it is part of the query crunching? – Smandoli

1 Answers

2
votes

Is the recordset itself only needing to be created once and then filtered many times?

If so, can you pass the recordset as a parameter into the classes method/function that does the filtering on it? That way the recordset can be created once.