Interesting performance "black hole"

[Peter Ostermann]

Hi folks,
the first run takes 2 to 3 seconds, the next
(similar) runs are taking up to 2 minutes. I have no
idea why.
(I do not know how to release the primary
storage after each present run - do not even know
if it has to do with the RAM "storage")

Description:
My VBA-routine checks in a loop for a text-string in about
5000 cells of a certain column. Depending of the sting I search
for, the result is between 50 and 500 hits. From that row, where
I found a similar string, the contents of a few cells is transferred
to a new sheet. That's all. But only the first time it is fast.

My System is: Excel 2000, WinXP,
processor speed 1,5 GHz,
storage 512 MB RAM


Greetings from Germany
Peter
www.pkf-ostermann.de

[Tom Ogilvy]

Close and open excel between the runs. Then it will always be the "first
time"

--
Regards,
Tom Ogilvy

"Peter Ostermann" <info@pkf-ostermann.de> wrote in message
news:434ea5ff$0$24170$9b4e6d93@newsread4.arcor-online.net...
> Hi folks,
> the first run takes 2 to 3 seconds, the next
> (similar) runs are taking up to 2 minutes. I have no
> idea why.
> (I do not know how to release the primary
> storage after each present run - do not even know
> if it has to do with the RAM "storage")
>
> Description:
> My VBA-routine checks in a loop for a text-string in about
> 5000 cells of a certain column. Depending of the sting I search
> for, the result is between 50 and 500 hits. From that row, where
> I found a similar string, the contents of a few cells is transferred
> to a new sheet. That's all. But only the first time it is fast.
>
> My System is: Excel 2000, WinXP,
> processor speed 1,5 GHz,
> storage 512 MB RAM
>
>
> Greetings from Germany
> Peter
> www.pkf-ostermann.de
>
>
>
>
>
>
>
>
>
>
>

[NoSpam@aol.com]

On Thu, 13 Oct 2005 15:45:21 -0400, "Tom Ogilvy" <twogilvy@msn.com> wrote:

>Close and open excel between the runs. Then it will always be the "first
>time"

I posted a similar question (greyhound to turtle speed) regarding queries.
Is thery ANY OTHER WAY to resolve these issues. I am running my Sub about
17,600 times. I am actually closing and opening Excel between every 1000
times, but it seems like a lot of drugery to do something like that when
there sould be some way to clear whatever needs to be cleared in code.

Thanks for any help.

[Vic Eldridge]

Hi Peter,

We won't be able to help debug your code if you don't show us your code.
Having said that, I would suggest you look at the help files (and the
examples) for the Find and FindNext methods. It's a bit trickier to code
than a simple loop, but if done properly, your first (and subsequent) runs
should take less than 0.1 seconds.

Regards,
Vic Eldridge



"Peter Ostermann" wrote:

> Hi folks,
> the first run takes 2 to 3 seconds, the next
> (similar) runs are taking up to 2 minutes. I have no
> idea why.
> (I do not know how to release the primary
> storage after each present run - do not even know
> if it has to do with the RAM "storage")
>
> Description:
> My VBA-routine checks in a loop for a text-string in about
> 5000 cells of a certain column. Depending of the sting I search
> for, the result is between 50 and 500 hits. From that row, where
> I found a similar string, the contents of a few cells is transferred
> to a new sheet. That's all. But only the first time it is fast.
>
> My System is: Excel 2000, WinXP,
> processor speed 1,5 GHz,
> storage 512 MB RAM
>
>
> Greetings from Germany
> Peter
> www.pkf-ostermann.de
>
>
>
>
>
>
>
>
>
>
>
>

[Peter Ostermann]

Hi, Vic,

> We won't be able to help debug your code if you don't show us your code.

No prob. Here is my simple code:

While Ako_Liste.Cells(I, 1) <> "" Or Ako_Liste.Cells(I, 3) <> ""
If Trim(Ako_Liste.Cells(I, 10)) <> AKO_Ref Then
Else
If Ako_Kd_Auswahl.Cells(3, 2) <> "" And Ako_Kd_Auswahl.Cells(3,
4) <> "" Then
Else
Ako_Kd_Auswahl.Cells(3, 2) = AKO_Ref
Ako_Kd_Auswahl.Cells(3, 4) = Trim(Ako_Liste.Cells(I, 17))
End If
Ako_Kd_Auswahl.Cells(II, 1) = Trim(Ako_Liste.Cells(I, 4))
Ako_Kd_Auswahl.Cells(II, 2) = Trim(Ako_Liste.Cells(I, 5))
Ako_Kd_Auswahl.Cells(II, 3) = Trim(Ako_Liste.Cells(I, 6)) & ", "
_
& Trim(Ako_Liste.Cells(I, 7))

Dat_Feld = Trim(Ako_Liste.Cells(I, 8))
If Len(Dat_Feld) < 10 _
Then
Ako_Kd_Auswahl.Cells(II, 4) = "0000.00.00"
Else
Ako_Kd_Auswahl.Cells(II, 4) = Mid(Dat_Feld, 7, 4) & _
Mid(Dat_Feld, 3, 4) & _
Mid(Dat_Feld, 1, 2)
End If
Ako_Kd_Auswahl.Cells(II, 5) = Trim(Ako_Liste.Cells(I, 9))
II = II + 1
End If
I = I + 1
Wend

> Having said that, I would suggest you look at the help files (and the
> examples) for the Find and FindNext methods. It's a bit trickier to code
> than a simple loop, but if done properly, your first (and subsequent) runs
> should take less than 0.1 seconds.

Sounds good. I am eager to learn a new methode and
will study the "Find" function. In the meantime - do you have
a short example of that code? Must not really fit into my
routine.

Greetings
Peter

[Peter Ostermann]

Hi Vic,

> We won't be able to help debug your code if you don't show us your code.

I did. In my reply on this reply. I am still waiting
for your announcement of the bugs in that code. If there are
any, anyhow.

> Having said that, I would suggest you look at the help files (and the
> examples) for the Find and FindNext methods. It's a bit trickier to code

My German help files are not too helpful in that respect.
I suggested in my reply that you may post an example.
You really would do me a favor if you do that.

An example from anybody else is also very appreciated!
Thanks in advance.

Regards
Peter Ostermann

> than a simple loop, but if done properly, your first (and subsequent) runs
> should take less than 0.1 seconds.
>
> Regards,
> Vic Eldridge

[Vic Eldridge]

Hi Peter,

I see nothing in your code that might cause it to slow down on subsequent
executions. (Unless "Ako_Kd_Auswahl" is a worksheet that runs more code in
it's Worksheet_Change event.)

Allocation of memory is handled entirely by Excel and there is no way to
control it.

The following code is the help file's example for the Find method. It is
typically much faster than looping through every cell in a range. However,
your code shows you are searching for cells that *don't* contain something,
whereas the Find method searches for cells that *do* contain something. With
this is mind, perhaps you might be better off using Excel's Autofilter or
advanced filter methods to perform your search.

Here is the example from the help file...

This example finds all cells in the range A1:A500 on worksheet one that
contain the value 2 and changes it to 5.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With




"Peter Ostermann" wrote:

> Hi Vic,
>
> > We won't be able to help debug your code if you don't show us your code.
>
> I did. In my reply on this reply. I am still waiting
> for your announcement of the bugs in that code. If there are
> any, anyhow.
>
> > Having said that, I would suggest you look at the help files (and the
> > examples) for the Find and FindNext methods. It's a bit trickier to code
>
> My German help files are not too helpful in that respect.
> I suggested in my reply that you may post an example.
> You really would do me a favor if you do that.
>
> An example from anybody else is also very appreciated!
> Thanks in advance.
>
> Regards
> Peter Ostermann
>
> > than a simple loop, but if done properly, your first (and subsequent) runs
> > should take less than 0.1 seconds.
> >
> > Regards,
> > Vic Eldridge
>
>

[Peter Ostermann]

Hi Vic,
many thanks to you. for providing the "Find" example!

> I see nothing in your code that might cause it to slow down on subsequent
> executions. (Unless "Ako_Kd_Auswahl" is a worksheet that runs more code
in
> it's Worksheet_Change event.)

OK, but "Ako_Kd_Auswahl" does not contain any code at all.

> Allocation of memory is handled entirely by Excel and there is no way to
> control it.

That's what I feared. ;-)

>
> The following code is the help file's example for the Find method. It is
> typically much faster than looping through every cell in a range.
However,
> your code shows you are searching for cells that *don't* contain
something,

May be it looks like that on the first glimpse, but I am searching for
"content"
But no matter anyway, my new solution based on "Find" looks like this:

With ActiveSheet.Range("J1:J" & Tab_Ende)
Set C = .Find(AKO_Ref, LookIn:=xlValues)
If Not C Is Nothing _
Then
firstAddress = C.Address
Do
nextAddress = C.Address

I = Mid(nextAddress, InStr(3, nextAddress, "$") + 1)
Call Daten_in_Ako_Kd_Auswahl_einstellen(I)

Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With

The funny thing is, that the plain "Find"-loop to search for
the given textstring isn't significant faster, than my old way of iteration.
The time-consuming part is the code in the "Call"

"Call Daten_in_Ako_Kd_Auswahl_einstellen(I)"

where I transfer the data of the relevant rows to the other sheet.
Here you see that code:

Public Sub Daten_in_Ako_Kd_Auswahl_einstellen(I)
Dim Dat_Feld
If II > 7 Then
Else
Ako_Kd_Auswahl.Cells(3, 2) = AKO_Ref
Ako_Kd_Auswahl.Cells(3, 4) = Trim(Ako_Liste.Cells(I, 17))
End If

Ako_Kd_Auswahl.Cells(II, 1).Value = Ako_Liste.Cells(I, 4).Value
Ako_Kd_Auswahl.Cells(II, 2).Value = Ako_Liste.Cells(I, 5).Value
Ako_Kd_Auswahl.Cells(II, 3).Value = Trim(Ako_Liste.Cells(I, 6).Value) &
_
", " & Trim(Ako_Liste.Cells(I, 7).Value)
Dat_Feld = Trim(Ako_Liste.Cells(I, 8).Value)
If Len(Dat_Feld) < 10 _
Then
Ako_Kd_Auswahl.Cells(II, 4).Value = "0000.00.00"
Else
Ako_Kd_Auswahl.Cells(II, 4).Value = Mid(Dat_Feld, 7, 4) & _
Mid(Dat_Feld, 3, 4) & _
Mid(Dat_Feld, 1, 2)
End If
Ako_Kd_Auswahl.Cells(II, 5).Value = Trim(Ako_Liste.Cells(I, 9).Value)
II = II + 1
End Sub

And here is still that problem that the first run is extremly faster than
the following, even if it is searched for the same textstring in that
sheet with its 5000 rows. May be we do not find the cause
of the slowdown, but I would appreciate it, if the matter could still
be discussed.

Peter

[Peter Ostermann]

Gotcha !!!!

Hello Vic,

I found the cause for the low speed respectively
the solution for the high speed:
The textstring I search for, is given from the
selection of a combobox that is located in a form.

Everytime the change-event from the combobox
is released, the form has to be "kind of initialized"
which I found out can be done by assignment of
the form to a certain position on the screen like
for example:

form.left = 18
form.top = 86

That's all! Every preceding run now is as fast
as the first one.
Thanks anyway for your assistance.

Weekend greetings from Germany
Peter Ostermann