Program execution slows down as iterations increase

M

Mark

Hi -

I posted this question yesterday without code because it is written in C#
for a 2007 Excel applicaition and therefore thought it may not get many
looks. But what the heck we'll try it again with code.

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.

private void btPrintReport_Click(object sender, EventArgs e)
{

Object oMissing = System.Reflection.Missing.Value;

//Setup Workbook that holds "printed" pages
Excel.Workbook printWB =
Globals.ThisWorkbook.Application.Workbooks.Add(oMissing);
Excel.Sheets sheets = printWB.Worksheets;

// Rename current sheet to Dummy
Excel.Worksheet reportSheet =
(Excel.Worksheet)printWB.ActiveSheet;
reportSheet.Name = "Report";

// Delete other sheets from workbook
foreach (Excel.Worksheet nameWS in sheets)
if (nameWS.Name != "Report")
nameWS.Delete();

//Set worksheet and page counters
int p = 0;
int n = 0;
int rowCnt = 70;
int scale = 51;

//Get first contract
Globals.Sheet2.tblUserInterface_MasterBindingSource.MoveFirst();
n++;

do
{

//Print Contract to Worksheet
p++;
PrintContractToWorksheet2(printWB, p, rowCnt);

//Print Ceded and Net contracts
if (Globals.Sheet2.commonAccountNamedRange.Value2.ToString()
== "Y")
{
rbACNCeded.Checked = true;
p++;
PrintContractToWorksheet2(printWB, p, rowCnt);
rbACNNet.Checked = true;
p++;
PrintContractToWorksheet2(printWB, p, rowCnt);

//To reset the ACN check box to Assumed ... needed when
the reset checkbox is set to unchecked.
rbACNAssumed.Checked = true;
}

//Move to next contract and increment

Globals.Sheet2.tblUserInterface_MasterBindingSource.MoveNext();
n++;

}
while (n <=
Globals.Sheet2.tblUserInterface_MasterBindingSource.Count);


// Remove the workbooks connection that got copied over with
the worksheet.paste command above
//printWB.Connections[1].Delete();

// Move the cursor back to cell a1
Excel.Range printRange =
(Excel.Range)reportSheet.get_Range("a1", oMissing);
printRange.Select();

// Remove the range names from the workbook
// Started at the last name and worked down to the first name
.... if working up the names collection, we wouldn't have an item i to delete
after half of the items are deleted.
for (int i = printWB.Names.Count; i >= 1; i--)
if (printWB.Names.Item(i, oMissing,
oMissing).Name.Contains("Print_Area") == false && printWB.Names.Item(i,
oMissing, oMissing).Name.Contains("_") == false) //Keeps the print area
ranges and a couple of other (Hidden) cube related ranges that caused a crash
when they were deleted
printWB.Names.Item(i, oMissing, oMissing).Delete();

//Save the print worksheet
printWB.SaveAs(xlsPath, oMissing, oMissing, oMissing, oMissing,
oMissing, Excel.XlSaveAsAccessMode.xlNoChange, oMissing, oMissing, oMissing,
oMissing, oMissing);
printWB.Close(oMissing, oMissing, oMissing);

}


private void
PrintContractToWorksheet2(Microsoft.Office.Interop.Excel.Workbook printWB,
int p, int rowCnt)
{

Object oMissing = System.Reflection.Missing.Value;

//Method is used to refresh the data linked to the data cube.
// In theory the CalculateUntilAsyncQueriesDone should work
alone but does not and is prone to hang
// Showing the form "frmWorkingForm" with the sleep thread
method afferted the hanging.
// I stumpled onto this workaround by wanting to have the
computer "do something else" until the Aysnc was done; but I really don't
know why it works.
Globals.ThisWorkbook.Application.CalculateUntilAsyncQueriesDone();
frmWorkingForm workingForm = new frmWorkingForm();
workingForm.Show();

System.Threading.Thread.Sleep(0);

workingForm.Close();

//Copy the results from the template
Globals.Sheet1.Sheet1_Print_Area.Copy(oMissing);

// Set a reference to the sheet named "Report"
Excel.Worksheet reportSheet =
(Excel.Worksheet)printWB.ActiveSheet;

//Activate the print worksheet and select the cell that we are
pasting the results to
((Excel._Worksheet)reportSheet).Activate();
Excel.Range printRange = (Excel.Range)reportSheet.get_Range("a"
+ Convert.ToString((p - 1) * rowCnt + 1), oMissing);
printRange.Select();

//For the first iteration we have to paste the column widths ...
after that we have to put in a page break
if (p == 1)

printRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteColumnWidths,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, oMissing, oMissing);

//Paste results then paste values the results
// The first paste results is used to get the text box to copy
.... and I'm not aware if any other way to bring them over; it also brings
number formats.
reportSheet.Paste(oMissing, oMissing);

printRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, oMissing, oMissing);

//Add Solid black line and PageBreak
printRange = (Excel.Range)reportSheet.get_Range("a" +
Convert.ToString((p) * rowCnt), "w" + Convert.ToString((p) * rowCnt));
printRange.Select();

printRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;

printRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlMedium;

printRange = (Excel.Range)reportSheet.get_Range("a" +
Convert.ToString((p) * rowCnt + 1), "ag" + Convert.ToString((p) * rowCnt +
1));
printRange.Select();
reportSheet.HPageBreaks.Add(printRange);

}
 
C

Charles Williams

I assume you have turned off ScreenUpdating, Calculation and Events before
doing this operation.

You are probably either having trouble with Excel's string table or the
database connection or the C# interop layer: I have not played enough with
C# code to tell.

If I were doing this using VBA I would make 1 connection, get a recordset
for 1 contract, use copyfromrecordset to copy the recordset directly to the
correct place in the second worksheet (eliminate the first worksheet and
copypaste etc), then get the next recordset without dropping the connection.

That's assuming you can't get multiple contracts at a time into the
recordset, which would be much better.

I also just spotted some PageBreak code (and formatting): avoid doing this
until AFTER you have put ALL the data onto the worksheet.

If the above approach is still slow and you cannot get multiple contracts
per recordset then I would build a large array from the individual contract
recordsets and dump it to the worksheet in 1 write operation.

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

Mark said:
Hi -

I posted this question yesterday without code because it is written in C#
for a 2007 Excel applicaition and therefore thought it may not get many
looks. But what the heck we'll try it again with code.

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.

private void btPrintReport_Click(object sender, EventArgs e)
{

Object oMissing = System.Reflection.Missing.Value;

//Setup Workbook that holds "printed" pages
Excel.Workbook printWB =
Globals.ThisWorkbook.Application.Workbooks.Add(oMissing);
Excel.Sheets sheets = printWB.Worksheets;

// Rename current sheet to Dummy
Excel.Worksheet reportSheet =
(Excel.Worksheet)printWB.ActiveSheet;
reportSheet.Name = "Report";

// Delete other sheets from workbook
foreach (Excel.Worksheet nameWS in sheets)
if (nameWS.Name != "Report")
nameWS.Delete();

//Set worksheet and page counters
int p = 0;
int n = 0;
int rowCnt = 70;
int scale = 51;

//Get first contract

Globals.Sheet2.tblUserInterface_MasterBindingSource.MoveFirst();
n++;

do
{

//Print Contract to Worksheet
p++;
PrintContractToWorksheet2(printWB, p, rowCnt);

//Print Ceded and Net contracts
if
(Globals.Sheet2.commonAccountNamedRange.Value2.ToString()
== "Y")
{
rbACNCeded.Checked = true;
p++;
PrintContractToWorksheet2(printWB, p, rowCnt);
rbACNNet.Checked = true;
p++;
PrintContractToWorksheet2(printWB, p, rowCnt);

//To reset the ACN check box to Assumed ... needed when
the reset checkbox is set to unchecked.
rbACNAssumed.Checked = true;
}

//Move to next contract and increment

Globals.Sheet2.tblUserInterface_MasterBindingSource.MoveNext();
n++;

}
while (n <=
Globals.Sheet2.tblUserInterface_MasterBindingSource.Count);


// Remove the workbooks connection that got copied over with
the worksheet.paste command above
//printWB.Connections[1].Delete();

// Move the cursor back to cell a1
Excel.Range printRange =
(Excel.Range)reportSheet.get_Range("a1", oMissing);
printRange.Select();

// Remove the range names from the workbook
// Started at the last name and worked down to the first
name
... if working up the names collection, we wouldn't have an item i to
delete
after half of the items are deleted.
for (int i = printWB.Names.Count; i >= 1; i--)
if (printWB.Names.Item(i, oMissing,
oMissing).Name.Contains("Print_Area") == false && printWB.Names.Item(i,
oMissing, oMissing).Name.Contains("_") == false) //Keeps the print area
ranges and a couple of other (Hidden) cube related ranges that caused a
crash
when they were deleted
printWB.Names.Item(i, oMissing, oMissing).Delete();

//Save the print worksheet
printWB.SaveAs(xlsPath, oMissing, oMissing, oMissing, oMissing,
oMissing, Excel.XlSaveAsAccessMode.xlNoChange, oMissing, oMissing,
oMissing,
oMissing, oMissing);
printWB.Close(oMissing, oMissing, oMissing);

}


private void
PrintContractToWorksheet2(Microsoft.Office.Interop.Excel.Workbook printWB,
int p, int rowCnt)
{

Object oMissing = System.Reflection.Missing.Value;

//Method is used to refresh the data linked to the data cube.
// In theory the CalculateUntilAsyncQueriesDone should work
alone but does not and is prone to hang
// Showing the form "frmWorkingForm" with the sleep thread
method afferted the hanging.
// I stumpled onto this workaround by wanting to have the
computer "do something else" until the Aysnc was done; but I really don't
know why it works.

Globals.ThisWorkbook.Application.CalculateUntilAsyncQueriesDone();
frmWorkingForm workingForm = new frmWorkingForm();
workingForm.Show();

System.Threading.Thread.Sleep(0);

workingForm.Close();

//Copy the results from the template
Globals.Sheet1.Sheet1_Print_Area.Copy(oMissing);

// Set a reference to the sheet named "Report"
Excel.Worksheet reportSheet =
(Excel.Worksheet)printWB.ActiveSheet;

//Activate the print worksheet and select the cell that we are
pasting the results to
((Excel._Worksheet)reportSheet).Activate();
Excel.Range printRange = (Excel.Range)reportSheet.get_Range("a"
+ Convert.ToString((p - 1) * rowCnt + 1), oMissing);
printRange.Select();

//For the first iteration we have to paste the column widths
...
after that we have to put in a page break
if (p == 1)

printRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteColumnWidths,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
oMissing, oMissing);

//Paste results then paste values the results
// The first paste results is used to get the text box to copy
... and I'm not aware if any other way to bring them over; it also brings
number formats.
reportSheet.Paste(oMissing, oMissing);

printRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
oMissing, oMissing);

//Add Solid black line and PageBreak
printRange = (Excel.Range)reportSheet.get_Range("a" +
Convert.ToString((p) * rowCnt), "w" + Convert.ToString((p) * rowCnt));
printRange.Select();

printRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle
= Excel.XlLineStyle.xlContinuous;

printRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight
= Excel.XlBorderWeight.xlMedium;

printRange = (Excel.Range)reportSheet.get_Range("a" +
Convert.ToString((p) * rowCnt + 1), "ag" + Convert.ToString((p) * rowCnt +
1));
printRange.Select();
reportSheet.HPageBreaks.Add(printRange);

}
Click to expand...
 
M

Mark

Charles -

Thanks for your comments.

I simplefied and removed some code ... and its working. [It sometimes takes
me a while to come back to that basic premise of KIS ... Keep it Simple]

I'm not sure of the offending code, and don't have the energy to test it
right now.

I followed you're advice on the page breaks and formatting ... thanks for
these and the other tips.

Mark


Charles Williams said:
I assume you have turned off ScreenUpdating, Calculation and Events before
doing this operation.

You are probably either having trouble with Excel's string table or the
database connection or the C# interop layer: I have not played enough with
C# code to tell.

If I were doing this using VBA I would make 1 connection, get a recordset
for 1 contract, use copyfromrecordset to copy the recordset directly to the
correct place in the second worksheet (eliminate the first worksheet and
copypaste etc), then get the next recordset without dropping the connection.

That's assuming you can't get multiple contracts at a time into the
recordset, which would be much better.

I also just spotted some PageBreak code (and formatting): avoid doing this
until AFTER you have put ALL the data onto the worksheet.

If the above approach is still slow and you cannot get multiple contracts
per recordset then I would build a large array from the individual contract
recordsets and dump it to the worksheet in 1 write operation.

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

Mark said:
Hi -

I posted this question yesterday without code because it is written in C#
for a 2007 Excel applicaition and therefore thought it may not get many
looks. But what the heck we'll try it again with code.

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.

private void btPrintReport_Click(object sender, EventArgs e)
{

Object oMissing = System.Reflection.Missing.Value;

//Setup Workbook that holds "printed" pages
Excel.Workbook printWB =
Globals.ThisWorkbook.Application.Workbooks.Add(oMissing);
Excel.Sheets sheets = printWB.Worksheets;

// Rename current sheet to Dummy
Excel.Worksheet reportSheet =
(Excel.Worksheet)printWB.ActiveSheet;
reportSheet.Name = "Report";

// Delete other sheets from workbook
foreach (Excel.Worksheet nameWS in sheets)
if (nameWS.Name != "Report")
nameWS.Delete();

//Set worksheet and page counters
int p = 0;
int n = 0;
int rowCnt = 70;
int scale = 51;

//Get first contract

Globals.Sheet2.tblUserInterface_MasterBindingSource.MoveFirst();
n++;

do
{

//Print Contract to Worksheet
p++;
PrintContractToWorksheet2(printWB, p, rowCnt);

//Print Ceded and Net contracts
if
(Globals.Sheet2.commonAccountNamedRange.Value2.ToString()
== "Y")
{
rbACNCeded.Checked = true;
p++;
PrintContractToWorksheet2(printWB, p, rowCnt);
rbACNNet.Checked = true;
p++;
PrintContractToWorksheet2(printWB, p, rowCnt);

//To reset the ACN check box to Assumed ... needed when
the reset checkbox is set to unchecked.
rbACNAssumed.Checked = true;
}

//Move to next contract and increment

Globals.Sheet2.tblUserInterface_MasterBindingSource.MoveNext();
n++;

}
while (n <=
Globals.Sheet2.tblUserInterface_MasterBindingSource.Count);


// Remove the workbooks connection that got copied over with
the worksheet.paste command above
//printWB.Connections[1].Delete();

// Move the cursor back to cell a1
Excel.Range printRange =
(Excel.Range)reportSheet.get_Range("a1", oMissing);
printRange.Select();

// Remove the range names from the workbook
// Started at the last name and worked down to the first
name
... if working up the names collection, we wouldn't have an item i to
delete
after half of the items are deleted.
for (int i = printWB.Names.Count; i >= 1; i--)
if (printWB.Names.Item(i, oMissing,
oMissing).Name.Contains("Print_Area") == false && printWB.Names.Item(i,
oMissing, oMissing).Name.Contains("_") == false) //Keeps the print area
ranges and a couple of other (Hidden) cube related ranges that caused a
crash
when they were deleted
printWB.Names.Item(i, oMissing, oMissing).Delete();

//Save the print worksheet
printWB.SaveAs(xlsPath, oMissing, oMissing, oMissing, oMissing,
oMissing, Excel.XlSaveAsAccessMode.xlNoChange, oMissing, oMissing,
oMissing,
oMissing, oMissing);
printWB.Close(oMissing, oMissing, oMissing);

}


private void
PrintContractToWorksheet2(Microsoft.Office.Interop.Excel.Workbook printWB,
int p, int rowCnt)
{

Object oMissing = System.Reflection.Missing.Value;

//Method is used to refresh the data linked to the data cube.
// In theory the CalculateUntilAsyncQueriesDone should work
alone but does not and is prone to hang
// Showing the form "frmWorkingForm" with the sleep thread
method afferted the hanging.
// I stumpled onto this workaround by wanting to have the
computer "do something else" until the Aysnc was done; but I really don't
know why it works.

Globals.ThisWorkbook.Application.CalculateUntilAsyncQueriesDone();
frmWorkingForm workingForm = new frmWorkingForm();
workingForm.Show();

System.Threading.Thread.Sleep(0);

workingForm.Close();

//Copy the results from the template
Globals.Sheet1.Sheet1_Print_Area.Copy(oMissing);

// Set a reference to the sheet named "Report"
Excel.Worksheet reportSheet =
(Excel.Worksheet)printWB.ActiveSheet;

//Activate the print worksheet and select the cell that we are
pasting the results to
((Excel._Worksheet)reportSheet).Activate();
Excel.Range printRange = (Excel.Range)reportSheet.get_Range("a"
+ Convert.ToString((p - 1) * rowCnt + 1), oMissing);
printRange.Select();

//For the first iteration we have to paste the column widths
...
after that we have to put in a page break
if (p == 1)

printRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteColumnWidths,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
oMissing, oMissing);

//Paste results then paste values the results
// The first paste results is used to get the text box to copy
... and I'm not aware if any other way to bring them over; it also brings
number formats.
reportSheet.Paste(oMissing, oMissing);

printRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
oMissing, oMissing);

//Add Solid black line and PageBreak
printRange = (Excel.Range)reportSheet.get_Range("a" +
Convert.ToString((p) * rowCnt), "w" + Convert.ToString((p) * rowCnt));
printRange.Select();

printRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle
= Excel.XlLineStyle.xlContinuous;

printRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight
= Excel.XlBorderWeight.xlMedium;

printRange = (Excel.Range)reportSheet.get_Range("a" +
Convert.ToString((p) * rowCnt + 1), "ag" + Convert.ToString((p) * rowCnt +
1));
printRange.Select();
reportSheet.HPageBreaks.Add(printRange);

}
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Save Method Of Workbook Class Failed 0
Program execution slows down in as iterations increase 5

Top