469,282 Members | 2,175 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

Filtering An Open Recordset?

Say I've got a RecordSet:

-----------------------------------------------------------
Set myRS = CurrentDB.OpenRecordset(SomeSQL, dbOpenDynaset)
-----------------------------------------------------------

Is there any way to dynamically filter/unfilter/re-filter that RS without
doing any .Opens or .Closes?
--
PeteCresswell
Nov 13 '05 #1
12 7837
Per (Pete Cresswell):
that RS


Access 2003.../JET 4.0.../ADO 2.8...

Been using JET. Maybe something with an ADO RS?

This is in regards to the app bloat problem I've been whining
about in a couple other threads. I'd put some money on it being
associated with tens of thousands of RS Opens/Closes in a batch job.

Come to think of it, maybe I should try feeding my SQL
just the way it is to ADO....
--
PeteCresswell
Nov 13 '05 #2
Per (Pete Cresswell):
Is there any way to dynamically filter/unfilter/re-filter that RS without
doing any .Opens or .Closes?


I think I've got an answer and the answer is "Yes"....

----------------------------------------------------------------------------
With myRS
.Filter = adFilterNone
.Filter = "ReturnDate BETWEEN #01/01/1997# AND #01/01/2003# AND FundID=24835"
End With
----------------------------------------------------------------------------

Sounds like another potential speed increase too....

--
PeteCresswell
Nov 13 '05 #3
Per (Pete Cresswell):
I think I've got an answer and the answer is "Yes"....


Nope.... turns out the .Filter only applies the next time sombody opens a
RecordSet from the .Filtered RS.... so we still have to open a new RS each time
we want a different subset of data.
--
PeteCresswell
Nov 13 '05 #4
"(Pete Cresswell)" <x@y.z.invalid> wrote in
news:7n********************************@4ax.com:
This is in regards to the app bloat problem I've been whining
about in a couple other threads. I'd put some money on it being
associated with tens of thousands of RS Opens/Closes in a batch
job.


Have you changed to avoiding stored querydefs with parameters? If
not, then I don't think you've actually identified the problem.

I've got code that opens tens of thousands of recordsets and the db
does not bloat.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #5
"(Pete Cresswell)" <x@y.z.invalid> wrote in
news:lh********************************@4ax.com:
Per (Pete Cresswell):
I think I've got an answer and the answer is "Yes"....


Nope.... turns out the .Filter only applies the next time sombody
opens a RecordSet from the .Filtered RS.... so we still have to
open a new RS each time we want a different subset of data.


I doubt this could contribute to any large amount of bloat, but are
you running this code in an MDE or an MDB? The direction I'm going
in is that perhaps there's something decompiling the VBA code and it
needs to recompile each time your code loop runs. An MDE would
eliminate prevent that problem from ever happening.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #6
Per David W. Fenton:
Have you changed to avoiding stored querydefs with parameters? If
not, then I don't think you've actually identified the problem.
I cut that routine over to inline SQL about 2:00 this morning and fired up a
volume test.

First thing I noticed was that it runs 25% faster with inline SQL. Tried a few
additional runs and they all supported that number.

I've got code that opens tens of thousands of recordsets and the db
does not bloat.


On my first low-volume tests, I thought I had the bloat licked - there was
some...but I expect that from the first time forms are opened and things like
that.

But on the full-volume test, the 5-meg app was coming right up on a hundred megs
by about 5 a.m.

So I fell back and regrouped. Instead of .Opening/.Closing the RS every time
I called the routine, I opened an RS into the entire Returns table in the
calling routine. Then, as the calling routine loops through another RS,
invoking the problem routine, I pass a pointer to the permanently-opened RS and
the problem routine does .FindFirst/.FindLast to identify the range of records
I'm intersted in and then loops through them.

In low-volume testing, it looked *really* good...like three times faster: .5
seconds per Return as opposed to 1.4 seconds with the inline SQL.

Each return involves many iterations of the problem routine as I calculate
rolling rates of return for 1, 2, 3, 4, 5, 6, 7, 8, 9, and 10 years. So, for
quarterly returns, the first one is four calcs; the second one is eight, the
third one is 12...and so on. Total per return: 220 calculations. There are
also monthlies....

For each calculation, I have to ensure that there is a return present for each
quarter within the period and build a return stream from them.... So that was
..Opening/.Closing a recordset 220 times for each return....
220 & 116,000 = Lotsa .Opens/.Closes.... (25 million+... And that's only for
quarterlies. Monthlies are worse with 12 per year....

When I went to a full-volume test of the always-open-sequential-processing
approach my little bubble burst: 4.1 seconds per return! This seems to
conflict with what I've heard about indexed searching - that if all fields
involved are indexed, the nature of binary searches makes speed relatively
independent of number of records.

But it *does* seem to have stopped the bloat totally/completely albeit at the
cost of bringing the app to it's knees.

For high-volume (i.e. production) we're talking 116,000 records instead of the
low-volume's 1000....and that ain't gonna fly....
(116,000 * 4) /3600 = 132 hours....

I'm going to try to come up with some kind of compromise - maybe loop through
the driver RS one fund at a time and re-create the Return RS with a single
fund's returns - which might put me back at .5 seconds/return or even faster.

If that doesn't work out, maybe I'll just fall back to bloatsville and refresh
the copy of the app every so often.

But it *would* be nice know what's causing the bloat.... I'm also logging
beaucoups error messages using the same kind of DAO IO: for each message, I open
up a recordset, append a record, and then close the RS. And I don't *think*
I'm getting any bloat out of that.

--
PeteCresswell
Nov 13 '05 #7
With myRS
.Filter = "ReturnDate BETWEEN #01/01/1997# AND #01/01/2003# AND
FundID=24835"
Set myRS2 = .OpenRecordset
End With
--
Terry Kreft
MVP Microsoft Access
"(Pete Cresswell)" <x@y.z.invalid> wrote in message
news:2q********************************@4ax.com...
Per (Pete Cresswell):
Is there any way to dynamically filter/unfilter/re-filter that RS without
doing any .Opens or .Closes?
I think I've got an answer and the answer is "Yes"....

--------------------------------------------------------------------------

-- With myRS
.Filter = adFilterNone
.Filter = "ReturnDate BETWEEN #01/01/1997# AND #01/01/2003# AND FundID=24835" End With
-------------------------------------------------------------------------- --
Sounds like another potential speed increase too....

--
PeteCresswell

Nov 13 '05 #8
"(Pete Cresswell)" <x@y.z.invalid> wrote in
news:68********************************@4ax.com:
But it *would* be nice know what's causing the bloat.... I'm also
logging beaucoups error messages using the same kind of DAO IO:
for each message, I open up a recordset, append a record, and then
close the RS. And I don't *think* I'm getting any bloat out of
that.


Where is the error data stored?

Have you tried commenting out the error logging?

And have you tried running this from an MDE rather than an MDB?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #9
Per David W. Fenton:
And have you tried running this from an MDE rather than an MDB?


That's the second time I've read that. It got lost in all the other stuff.
Maybe it's time to give it a try after the current test gets done.
What would it indicate if the bloat stopped?
--
PeteCresswell
Nov 13 '05 #10
"(Pete Cresswell)" <x@y.z.invalid> wrote in
news:si********************************@4ax.com:
Per David W. Fenton:
And have you tried running this from an MDE rather than an MDB?


That's the second time I've read that. It got lost in all the
other stuff. Maybe it's time to give it a try after the current
test gets done. What would it indicate if the bloat stopped?


That the bloat was being caused by VBA recompilation, not by your
SQL/data retrieval.

I find it *very* unlikely to result in bloat at that order of
magnitude, but VBA compiling always mystifies me as to how many
strange things can go wrong.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #11
Per David W. Fenton:
That the bloat was being caused by VBA recompilation, not by your
SQL/data retrieval.

I find it *very* unlikely to result in bloat at that order of
magnitude, but VBA compiling always mystifies me as to how many
strange things can go wrong.


I tried making the old version (the one that bloats) into a .MDE.

The bloat persists....apparently in 4 or 8k chunks.
--
PeteCresswell
Nov 13 '05 #12
"(Pete Cresswell)" <x@y.z.invalid> wrote in
news:qa********************************@4ax.com:
Per David W. Fenton:
That the bloat was being caused by VBA recompilation, not by your
SQL/data retrieval.

I find it *very* unlikely to result in bloat at that order of
magnitude, but VBA compiling always mystifies me as to how many
strange things can go wrong.


I tried making the old version (the one that bloats) into a .MDE.

The bloat persists....apparently in 4 or 8k chunks.


Have you tried it without the error logging?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Colleyville Alan | last post: by
2 posts views Thread by Sunil Korah | last post: by
reply views Thread by solar | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.