Final .MoveNext in recordset is very slow

[otterbyte]

I have two recordsets which are looping one inside the other. The
'inner', small recordset is based on a simple select query and opened
as dbOpenForwardOnly. As it loops, it updates values (using DAO) in the
outer recordset. The outer recordset has almost 200k records, but the
inner one will only have 3 to 7 records. My problem is this: just after
the last record of the inner recordset has processed, once the inner
recordset is at the EOF, it takes forever to process the .MoveNext
statement. This is the only line that slows down. Considering the outer
recordset has so many records, I can't wait for a 20-30 second hang on
each one. What could be happening? I have tried changing the options on
the inner recordset to dbOpenSnapshot and even leaving the defaults,
with no change. I tried changing from 'Do Until .EOF' to 'Do While Not
..EOF' with no change in speed. I've checked the newsgroups and I found
one post that mentions this same problem, but it's from 1996 and there
were no replies. Here is the skeleton of my code:

************
With rstOuter
Do Until .EOF
Set rstInner= db.OpenRecordset("SELECT * FROM tblTable " & _
"WHERE Field1= '" & !Field1 & "'", dbOpenForwardOnly)
.Edit
Do While Not rstInner.EOF
rstOuter!Field2 = rstInner!Field2
rstOuter!Field3 = rstInner!Field3
rstInner.MoveNext '<--hangs on the final iteration of the
loop
Loop
rstInner.Close
.Update
.MoveNext
Loop
.Close
End With
*************

I am using DAO 3.6 and Access 2k. Any clues on what I might be doing
wrong? And what the heck I can do to make it better?

TIA,
Erika

 

[strive4peace]

Hi Erika,

what is probably taking the time is

rstInner.Close

in your loop

Since you are not updating rstInner, you only need this to
be a snapshot of the data -- might be quicker if you specify
that it is not a dynamic recordset

replace --> dbOpenForwardOnly --> dbOpenSnapshot

at the end of your code, release object variables

On Error Resume Next
rstInner.Close
set rstInner = nothing
rstOuter.Close*
set rstOuter = nothing

db.close --> if you actually opened it, not if you used
Currentdb

set db = nothing

*for rstOuter, since you are already closing it, you can
skip that line

As for the logic of what you are doing... I see no ORDER BY
clause in rstInner ... each time, you are updating the SAME
record in rstOuter with the current record in
rstInner...what are your trying to get?

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[John Spencer]

I would think it would be a lot faster to use a query to update the data. WIth
the little data available in your posting it is impossible to say what that
query might look like.


Your code seems to set two fields in the Outer Record set to the last value in
the Inner record set. If you stick with a vba code solution, I don't
understand why you don't use rstInner.MoveLast. I would rewrite that portion of
your code as follows. Also note that you are more or less selecting a random
record from rstInner since you have not specified an order by clause.


Set rstInner= db.OpenRecordset("SELECT * FROM tblTable " & _
"WHERE Field1= '" & !Field1 & "'", dbOpenForwardOnly)

If rstInner.RecordCount >0 then
rstInner.MoveLast
.Edit
rstOuter!Field2 = rstInner!Field2
rstOuter!Field3 = rstInner!Field3
.Update
'Don't bother to close rstInner at this point
'since you are going to reuse it.
End If
.MoveNext

 

[otterbyte]

Thank you both for your replies! I was trying to keep my code short,
but I may have been too brief to be useful. Let me try to add some more
salient information:

I am denormalizing a table which lists blood test results for anywhere
from 3 to 20 tests done on individual units of blood. I'm not happy
about having to do this, but it's a backward compatibility issue and
until we overhaul several other databases, I need to get the new
table's data into the format of the old table, which - you guessed it -
has one column per test and one record per unit of blood. The new
table, from which I get the data, has one test per record, multiple
records per unit. The new table has well over one million records.

So: rstOuter is the old denormalized table, which has already been
populated with one record per unit, and needs the test results dropped
into appropriate columns. I loop through this one by one. rstInner is
taken from the new, normalized table and contains all the tests results
for the current unit of rstOuter. I don't need an OrderBy clause
because I don't care what order I populate the columns in. However, if
this would improve performance, I will surely add it! The part where in
the example code I am setting values, does not actually exist - I was
just adding filler code to replace the big 'ol select case statement
that looks at which test result is in the current record of rstInner
and then sets the appropriate column of the denormalized table to the
test result in the normalized table. When rstInner has finished looping
through all the test results, I update rstOuter, move to its next
record (blood unit), recreate rstInner using the new blood unit number,
and go through it all over again. There are about 200k units to go
through.

John, I did try using queries, without much luck. The normalized table
is in a (dbase, i think?) external table which I am linking to with
ODBC. It has well over one million records. There are no indices in the
external tables, and I have no control over that. Each update query
(one per test type) took 30-40 minutes. With 20 columns to update, this
just wasn't going to work. The code needs to run overnight, every
night. Four or five hours is fine, 20 hours (there are other tables I
have to create) may be pushing it. If a pass-through query would
speed things up, I will learn to write one...I guess it would help if I
can find out what program is being used in the external database.

Crystal, I thought ForwardOnly was the fastest type of recordset? Like
a snapshot that can only move in one direction - or am I confused?

I've tried taking out the rstInner.Close and it doesn't seem to help
the speed issue. Switching to dbOpenSnapsot also didn't seem to make
much difference. Adding an Order By query took care of the .MoveNext
problem completely, but now instead of hanging for 30-60 seconds at
that line, it takes 30-60 seconds to create the rstInner each time.
Arrgh.

Thank you again for your help - and any other advice you might be able
to give me.

Erika

 

[david epsom dot com dot au]

In your sample code, you are overwriting the
values in rsOuter. If this is true, you don't
need the inner loop at all: just select one
value.

Also, I see that movenext is followed by
Close and Update: are you sure that your
trace timing is correct?

(david)

 

[otterbyte]

David,

Yes, I threw some really screwy code inside that loop as a sample - I
realize that now! In the real code, there is a select case structure
that updates the appropriate field in the outer loop's record. It is a
different field for each iteration of the inner loop.

In the code, the first MoveNext is for the inner loop, then when the
inner do...loop is done, it closes rstInner, updates rstOuter, then
does MoveNext for rstOuter. Then it starts all over again.

I've been trying different things and have found that the lack of an
Order By clause in the creation of rstInner causes the hang in the last
iteration of the inner loop - however, adding the Order By clause slows
down the creation of the recordset so much that there is no gain made
by making the change. So, I keep trying things...

Thanks for the advice!

Erika

 

[TC]

Your existing code does not make sense. It is repeatedly setting the
two outer fields, to new values of the inner fields. You are
essentially doing something like this:

for each inner record
set outer.F1 = ...
next

If you want to set the outer fields to the values from the "last" of
the multiple matching inner records - it's not doing that either - at
least, not properly - because the inner recordset does not have an
ORDER BY, so the irder of the records (returned in that recordset) is
in theory, unpredictable.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[otterbyte]

TC,

You're right. It makes no sense - because it is *not* the real code - I
just put that in as 'filler' to replace the very long code that I
didn't want to clutter up the group with...and now because I put it
really dumb filler code, I keep having to explain code that isn't even
real!

So, here is a new code sample:

************
With rstOuter
Do Until .EOF
Set rstInner= db.OpenRecordset("SELECT * FROM tblTable " & _
"WHERE Field1= '" & !Field1 & "'", dbOpenForwardOnly)
.Edit
Do While Not rstInner.EOF
Select Case rstInner!Field2
'code snipped for space
'chooses correct field in rstOuter to update
'and updates it to one of the fields in rstInner
End Select
rstInner.MoveNext '<--hangs on final iteration of the loop
Loop
rstInner.Close
.Update
.MoveNext
Loop
.Close
End With
*************

Thanks!
Erika

 

[TC]

Now I'm confused. Are you copying an inner loop field to an outer loop
one, or an outer loop field to an inner loop one?

If the former - my previous comment still holds. If the latter - you
could do it with an UPDATE statement on the inner table.

Apart from all that, it's really inefficient to open a recordset for
every record in the outer loop. It would be much more efficient to do
the whole thing with a single UPDATE query. No loops - no recordsets.

We could show you how to write that query, but you'd need to tell us
the primary key field(s) of each table.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[otterbyte]

TC,

Thanks for replying. I am copying an inner loop field into an outer
loop. The explanation for the whole thing is in a previous post I made
on the thread - perhaps what's happening is that my posts are showing
up out of order? Here's the link to the explanation:

http://groups.google.com/group/microsoft.public.access.modulesdaovba/msg/00d4ae1219529e2a

The gist is this: I am resorting to DAO instead of UPDATE queries
because my source data has no indices, no keys, is linked through ODBC
from a database I have no control over, and is *huge* (1.3M records). I
am denormalizing the source data into a legacy table which means either
a crosstab query on the source data (eek!) or an update query on the
legacy table for each of its many (about 20) columns. Because of the
size of the source table and its lack of indices, each of these update
queries (I tried this method first) takes anywhere from 30 minutes to
an hour. This code has to run every night.

Doing it with DAO has been *much* faster. I was able to rip through 25%
of the source table in about 40 minutes. At which point I ran into the
4k per record issue, my database was 2Gb, and it locked up. I found a
recent post in another ng that suggested prefilling all fields with
placeholder data using update queries, which prevents the bloating
problem - and that works fine! So now, I use a pair of update queries
to (1) prefill the destination table (the denormalized one) with its
primary key data (this one does have one, because it's local and I
control it) and (2) a bunch of placeholder data in columns. Then I run
my code: the outer loop goes through the legacy table record by record,
the inner pulls all the data related to that record from the source
table. Then the inner loop goes through each of its records, plugging
in a value into a different column of the legacy table in each loop.
When the data for the (outer) record are all filled in, the inner loop
is finished, the outer loop moves to the next record in the legacy
table, and it all starts over again.

All of this is working beautifully and quickly - although if there are
any measures I can take to speed it up more I would be even happier, of
course! - *except* the last iteration of the inner loop. Which is also
perfectly fine until it reaches the MoveNext line, which takes 30-60
seconds to run. If I add an Order By clause to the OpenRecordset
statement in the inner loop, this hang goes away - replaced by a 30-60
second delay while the recordset is created.

So, my immediate problem has nothing to do with the code *inside* the
inner loop - which is why I put that dumb filler code in there - I
figured it wasn't relevant and didn't want to clutter up the ng with
it. Now, that doesn't mean however that I may still be chosing a dumb
way to do this! It's just that I have tried all the reasonable ways I
can think of and it's just too slow. This is the fastest I've gotten it
to work and I just need to figure out how to get past this frustrating
issue.

Thanks again for your time and your help, and for ploughing through my
confusing explanations - I hope I've been clearer this time!

Erika

 

[david epsom dot com dot au]

You should try using an ODBCdirect connection for the
inner loop.

Which is fast, and runs independently in parallel to
your other code. It should be caching your next record
while you work on this one. (you will have to use
extra code to handle that)

Using 'dbOpenForwardOnly' is fast, but it means that
it only moves forward under explicit instruction. So
you will always get a delay while it finds the next
record.

Depending on the number and order of the records, it
may take a long time to find the first record, or a
long time to get to the end of the table after finding
the record which turns out to be last.

Given that there are no indexes, it will be doing
the 'where' clause by scanning the table, one record
at a time.


(david)