incrementally slower routine -- vba

[bill shockley]

Excel 2010 - vba/Access 97
I have a vba routine that runs nice and quick at the start but then gets slower and slower
as it loops. I am parsing downloaded documents from the internet. Each document is similar --
only very minor changes in text from one doc to the next.



For each document, the parser will consult a database of html tags about a thousand times. I use
the command rs.seek to find a corresponding substitution for each html tag. It is this "seek" operation
that is slowing down. I put the line

T1 = TIMER

before the "seek" command, and the line

T2 = TIMER

afterwards. Then I sum up the thousand time measurements for each document to find out
how much time is spent by the cpu executing that one line of code. This is where it is spending
most of its time, and the time spent on that line grows in parallel with total processing time.

Here is a picture of time spent on the "seek" line of code for the first 30 documents that were
parsed.http://www.frontiernet.net/~wily11/seekTime.GIF

I'm really drawing a blank as to what sort of mechanism is involved in this slowdown. My only
thought was bad management of variables, but I did what troubleshooting I could think of in that
regard. Tried closing the recordset and database after each document and setting their variables to
Nothing. That's about it. Closing the spreadsheet and reopening does not help. It will resume at its
most recent speed. Closing Excel will reset it to optimal speed.

Thanks for looking.
Shockley

[June7]

Have you tested the procedure without the timer code? Maybe it is the timer that is causing the slowdown. Try just Debug.Print Now before and after the loop.

[bill shockley]

Have you tested the procedure without the timer code? Maybe it is the timer that is causing the slowdown. Try just Debug.Print Now before and after the loop.

I was chasing a bad lead. I tried debugging by stripping out code step by step. It turned out the slowdown occurred without the seek command even in the program. I kept thinking it had to be a variable containing a mushrooming amount of text. And that's what it was. Each document that I parse is about 30K characters. One variable that I wasn't using anymore and therefore not paying attention to, but was nevertheless active, was receiving the entire text of the document every time there was a new document, and I neglected to zero that variable for each new document, so it was growing rapidly in size and, I presume, eating up memory. The program slowed down gradually and if left to run long enough, eventually generated an error and crashed. It was just a hunch of mine that the seek code was causing the problem, but the seek line was slowing only because everything was slowing because of the diminishing memory.

Faith restored -- for now...

Thanks for your ears. It really helps!

Shockley