Program execution slows down in as iterations increase

[Mark]

Hi -

I posted this question over in the VSTO forum but thought I'd try it here too.

I am developing a Excel 2007 application using C# (VSTO).

So the program flow is populate the worksheet with numbers and text (these
come from a cube and SQL data tables in a SQL database), copy paste it to a
second worksheet in another workbook, go back to the original worksheet and
populate it with new numbers from the next contract, copy paste it to the
second worksheet but below the prior copy/paste section ... this is repeated
many times to as many as 1000 times or more.

The problem I'm having is that as the iteration increases into the 300 or
400's the program slows down to a virtual crawl, sometimes just stopping.

As a shot in the dark it seems like I'm using some resource without
releasing it which causes memory drain.

It works fine through 50 or 100 iterations but as I said it begins to slow
after that. In fact, I'm playing with a workaround which does 50 or so
copy/pastes at a time, then bulk copy the 50 onto a third worksheet. This
seems to work okay ... so that I avoid the "crawl", but it would be great to
understand why the slow down in the first place.

Any suggestions would be appreciated.

Mark

 

[Joel]

Try adding the equivalent of this statement to your code

Application.CutCopyMode = False


check you clipboard by opening up excel and going to menu

View - Task Pane and choose the option Clipboard.

The copying and pasting maybe going into the clipboard and using up memory.

 

[Mark]

Joel -

I'll give it a try and let you know how it works out.

Mark

 

[Mark]

Joel -

I gave it a go and the intial results show no benefit.

FYI, I used Application.CutCopyMode =
(Microsoft.Office.Interop.Excel.XlCutCopyMode)0; [since "= False" gave an
error, and this was the only thing I could ... I think I need to play with
this more]

In addition to the the slower execution, the Task Manager's Memory Usage
keeps increasing as the interations increase [which may be just because the
excel worksheet is growing with the iterations].

Per your suggestion, I'm not sure how to check the clipboard as the program
is running.

Anyways that is what I'm seeing, any other suggestions would be appreciated.

Mark

 

[Tim Zych]

Is it possibly a nested loop problem, where the inner loop takes longer for
each outer loop increment, e.g.

For X = 1 to 400
For Y = 1 to X

guessing since there is no code to check.

 

[Mark]

Tim -

I didn't add the code since it is written in C# and not VBA ... I didn't
want to scare anyone off from looking at it.

But maybe I should as it would give the viewer something to go on.

I'll try it in a new thread.

BTW, there is no inner loop.

Thanks for your comments.

Mark