Controlling Threads

I am having a problem with QueueUserWorkItem. I thought there was a maximum
number of worker threads a threadpool had available, 25 per processor, or
something like that. However, when executing the following code, after about
125 or so Excel processes are running I get:

"An unhandled exception of type
'System.Runtime.InteropServices.COMException' occurred in Fiscal Year
End.exe"

Additional information: Server execution failed

When another thread starts to execute. I would expect at most 50 concurrent
Excel threads on a P4 hyperthreading system. Is there something wrong in my
code that keeps the system from recognizing the maximum thread count?

Private ExcelPool As System.Threading.ThreadPool

<Some Code>

For Each AccountRow In ReportingDS.Tables("Ledger Accounts").Rows
SummaryRows = AccountRow.GetChildRows("Account Summary")
For Each SummaryRow In SummaryRows
TargetRange = WS.Cells(CurrentRow, 1)
TargetRange.Resize(1, _
SummaryRow.ItemArray.GetLength(0)).Value = SummaryRow.ItemArray
TargetRange = WS.Cells(CurrentRow, 1)
WS.Hyperlinks.Add(TargetRange, SaveSubDirectoryString & _
WS.Cells(CurrentRow, 1).Value & ".xls", , _
"Click on account number to open account activity.", _
WS.Cells(CurrentRow, 1).Value)
CurrentRow += 1
Next
Dim StObj As New StateObj
StObj.DataRowArg = AccountRow.GetChildRows("Account Detail")
StObj.DataTableArg = ReportingDS.Tables("Ledger Detail").Clone
StObj.SavePath = SaveSubDirectoryString & AccountRow(0) + ".xls"
StObj.DSName = AccountRow(0)
ExcelPool.QueueUserWorkItem(New System.Threading.WaitCallback _
(AddressOf SaveExcelData), StObj)
Next

<More Code>

Private Sub SaveExcelData(ByVal StateObj As Object)
Dim StObj As StateObj
StObj = CType(StateObj, StateObj)
Dim ThreadExcel As New Excel.Application
Dim ThreadWorkbook As Excel.Workbook = ThreadExcel.Workbooks.Add
While ThreadWorkbook.Worksheets.Count > 1
ThreadWorkbook.Worksheets(ThreadWorkbook.Worksheets.Count).Delete()
End While
Dim ThreadWorksheet As Excel.Worksheet = ThreadWorkbook.Worksheets(1)
Dim DR As DataRow
Dim DC As DataColumn
For Each DC In StObj.DataTableArg.Columns
ThreadWorksheet.Cells(1, DC.Ordinal + 1) = DC.ColumnName
Next
ThreadWorksheet.Rows(1).Font.Bold = True
ThreadWorksheet.Rows(1).Font.Underline =
Excel.XlUnderlineStyle.xlUnderlineStyleSingle
Dim CurrentRow As Integer = 2
Try
For Each DR In StObj.DataRowArg
For Each DC In StObj.DataTableArg.Columns
ThreadWorksheet.Cells(CurrentRow, DC.Ordinal + 1).Value =
DR(DC.Ordinal)
Next
CurrentRow += 1
Next
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "Thread Excel")
ThreadWorksheet = Nothing
ThreadWorkbook = Nothing
ThreadExcel.Quit()
ThreadExcel = Nothing
End Try
ThreadWorkbook.SaveAs(StObj.SavePath)
ThreadWorkbook.Close()
ThreadExcel.Quit()
ThreadExcel = Nothing
StObj = Nothing
End Sub


--
Richard A. Welch
IT Manager
House of Raeford Farms, Inc. - Raeford Division

likwoka
Fri Jun 24 23:31:08 CDT 2005

I think it is about 25 thread per process (you can have a few processes
running on a single processor machine, and each process can have at
most 25 concurrent threads using the default ThreadPool.

Would this help?

RichardWelch
Sat Jun 25 00:01:02 CDT 2005

So, the program I wrote can have 25 threads in the threadpool? I am a bit
confused as to what constitues a process. As you can probably tell I am new
at multithreaded applications.
--
Richard A. Welch
IT Manager
House of Raeford Farms, Inc. - Raeford Division



"likwoka@gmail.com" wrote:

> I think it is about 25 thread per process (you can have a few processes
> running on a single processor machine, and each process can have at
> most 25 concurrent threads using the default ThreadPool.
>
> Would this help?
>
>

Willy
Sat Jun 25 05:21:46 CDT 2005

Why do you have 125 Excel processes running in the first place? Do you mean
you create an instance of Excel in each worker thread you start? Well this
is wrong, you should only create a single Excel instance in your main thread
and you should not use any other thread at all.

Willy.


"Richard Welch" <RichardWelch@discussions.microsoft.com> wrote in message
news:DA8BAC21-6B2A-4858-92EB-51AC07D63573@microsoft.com...
>I am having a problem with QueueUserWorkItem. I thought there was a maximum
> number of worker threads a threadpool had available, 25 per processor, or
> something like that. However, when executing the following code, after
> about
> 125 or so Excel processes are running I get:
>
> "An unhandled exception of type
> 'System.Runtime.InteropServices.COMException' occurred in Fiscal Year
> End.exe"
>
> Additional information: Server execution failed
>
> When another thread starts to execute. I would expect at most 50
> concurrent
> Excel threads on a P4 hyperthreading system. Is there something wrong in
> my
> code that keeps the system from recognizing the maximum thread count?
>
> Private ExcelPool As System.Threading.ThreadPool
>
> <Some Code>
>
> For Each AccountRow In ReportingDS.Tables("Ledger Accounts").Rows
> SummaryRows = AccountRow.GetChildRows("Account Summary")
> For Each SummaryRow In SummaryRows
> TargetRange = WS.Cells(CurrentRow, 1)
> TargetRange.Resize(1, _
> SummaryRow.ItemArray.GetLength(0)).Value =
> SummaryRow.ItemArray
> TargetRange = WS.Cells(CurrentRow, 1)
> WS.Hyperlinks.Add(TargetRange, SaveSubDirectoryString & _
> WS.Cells(CurrentRow, 1).Value & ".xls", , _
> "Click on account number to open account activity.", _
> WS.Cells(CurrentRow, 1).Value)
> CurrentRow += 1
> Next
> Dim StObj As New StateObj
> StObj.DataRowArg = AccountRow.GetChildRows("Account Detail")
> StObj.DataTableArg = ReportingDS.Tables("Ledger Detail").Clone
> StObj.SavePath = SaveSubDirectoryString & AccountRow(0) + ".xls"
> StObj.DSName = AccountRow(0)
> ExcelPool.QueueUserWorkItem(New System.Threading.WaitCallback _
> (AddressOf SaveExcelData), StObj)
> Next
>
> <More Code>
>
> Private Sub SaveExcelData(ByVal StateObj As Object)
> Dim StObj As StateObj
> StObj = CType(StateObj, StateObj)
> Dim ThreadExcel As New Excel.Application
> Dim ThreadWorkbook As Excel.Workbook = ThreadExcel.Workbooks.Add
> While ThreadWorkbook.Worksheets.Count > 1
> ThreadWorkbook.Worksheets(ThreadWorkbook.Worksheets.Count).Delete()
> End While
> Dim ThreadWorksheet As Excel.Worksheet = ThreadWorkbook.Worksheets(1)
> Dim DR As DataRow
> Dim DC As DataColumn
> For Each DC In StObj.DataTableArg.Columns
> ThreadWorksheet.Cells(1, DC.Ordinal + 1) = DC.ColumnName
> Next
> ThreadWorksheet.Rows(1).Font.Bold = True
> ThreadWorksheet.Rows(1).Font.Underline =
> Excel.XlUnderlineStyle.xlUnderlineStyleSingle
> Dim CurrentRow As Integer = 2
> Try
> For Each DR In StObj.DataRowArg
> For Each DC In StObj.DataTableArg.Columns
> ThreadWorksheet.Cells(CurrentRow, DC.Ordinal + 1).Value =
> DR(DC.Ordinal)
> Next
> CurrentRow += 1
> Next
> Catch ex As Exception
> MsgBox(ex.Message, MsgBoxStyle.Critical, "Thread Excel")
> ThreadWorksheet = Nothing
> ThreadWorkbook = Nothing
> ThreadExcel.Quit()
> ThreadExcel = Nothing
> End Try
> ThreadWorkbook.SaveAs(StObj.SavePath)
> ThreadWorkbook.Close()
> ThreadExcel.Quit()
> ThreadExcel = Nothing
> StObj = Nothing
> End Sub
>
>
> --
> Richard A. Welch
> IT Manager
> House of Raeford Farms, Inc. - Raeford Division
>

RichardWelch
Sat Jun 25 08:58:01 CDT 2005

Willy,
I agree, it seems definately wrong to have to do multiple instances of
Excel to accomplish what I need to do. If there is a way to create between 3
and 4 thousand different workbooks quicker, I would definately like to know.
I am trying to multithread to take advantage of the hyperthreading availalbe.
The process I am trying to write takes over 30 hours to accomplish in a
single Excel instance. I would like to be able to hold it down to a smaller
number of threads, but am unsure how to accomplish this.
--
Richard A. Welch
IT Manager
House of Raeford Farms, Inc. - Raeford Division



"Willy Denoyette [MVP]" wrote:

> Why do you have 125 Excel processes running in the first place? Do you mean
> you create an instance of Excel in each worker thread you start? Well this
> is wrong, you should only create a single Excel instance in your main thread
> and you should not use any other thread at all.
>
> Willy.
>
>
> "Richard Welch" <RichardWelch@discussions.microsoft.com> wrote in message
> news:DA8BAC21-6B2A-4858-92EB-51AC07D63573@microsoft.com...
> >I am having a problem with QueueUserWorkItem. I thought there was a maximum
> > number of worker threads a threadpool had available, 25 per processor, or
> > something like that. However, when executing the following code, after
> > about
> > 125 or so Excel processes are running I get:
> >
> > "An unhandled exception of type
> > 'System.Runtime.InteropServices.COMException' occurred in Fiscal Year
> > End.exe"
> >
> > Additional information: Server execution failed
> >
> > When another thread starts to execute. I would expect at most 50
> > concurrent
> > Excel threads on a P4 hyperthreading system. Is there something wrong in
> > my
> > code that keeps the system from recognizing the maximum thread count?
> >
> > Private ExcelPool As System.Threading.ThreadPool
> >
> > <Some Code>
> >
> > For Each AccountRow In ReportingDS.Tables("Ledger Accounts").Rows
> > SummaryRows = AccountRow.GetChildRows("Account Summary")
> > For Each SummaryRow In SummaryRows
> > TargetRange = WS.Cells(CurrentRow, 1)
> > TargetRange.Resize(1, _
> > SummaryRow.ItemArray.GetLength(0)).Value =
> > SummaryRow.ItemArray
> > TargetRange = WS.Cells(CurrentRow, 1)
> > WS.Hyperlinks.Add(TargetRange, SaveSubDirectoryString & _
> > WS.Cells(CurrentRow, 1).Value & ".xls", , _
> > "Click on account number to open account activity.", _
> > WS.Cells(CurrentRow, 1).Value)
> > CurrentRow += 1
> > Next
> > Dim StObj As New StateObj
> > StObj.DataRowArg = AccountRow.GetChildRows("Account Detail")
> > StObj.DataTableArg = ReportingDS.Tables("Ledger Detail").Clone
> > StObj.SavePath = SaveSubDirectoryString & AccountRow(0) + ".xls"
> > StObj.DSName = AccountRow(0)
> > ExcelPool.QueueUserWorkItem(New System.Threading.WaitCallback _
> > (AddressOf SaveExcelData), StObj)
> > Next
> >
> > <More Code>
> >
> > Private Sub SaveExcelData(ByVal StateObj As Object)
> > Dim StObj As StateObj
> > StObj = CType(StateObj, StateObj)
> > Dim ThreadExcel As New Excel.Application
> > Dim ThreadWorkbook As Excel.Workbook = ThreadExcel.Workbooks.Add
> > While ThreadWorkbook.Worksheets.Count > 1
> > ThreadWorkbook.Worksheets(ThreadWorkbook.Worksheets.Count).Delete()
> > End While
> > Dim ThreadWorksheet As Excel.Worksheet = ThreadWorkbook.Worksheets(1)
> > Dim DR As DataRow
> > Dim DC As DataColumn
> > For Each DC In StObj.DataTableArg.Columns
> > ThreadWorksheet.Cells(1, DC.Ordinal + 1) = DC.ColumnName
> > Next
> > ThreadWorksheet.Rows(1).Font.Bold = True
> > ThreadWorksheet.Rows(1).Font.Underline =
> > Excel.XlUnderlineStyle.xlUnderlineStyleSingle
> > Dim CurrentRow As Integer = 2
> > Try
> > For Each DR In StObj.DataRowArg
> > For Each DC In StObj.DataTableArg.Columns
> > ThreadWorksheet.Cells(CurrentRow, DC.Ordinal + 1).Value =
> > DR(DC.Ordinal)
> > Next
> > CurrentRow += 1
> > Next
> > Catch ex As Exception
> > MsgBox(ex.Message, MsgBoxStyle.Critical, "Thread Excel")
> > ThreadWorksheet = Nothing
> > ThreadWorkbook = Nothing
> > ThreadExcel.Quit()
> > ThreadExcel = Nothing
> > End Try
> > ThreadWorkbook.SaveAs(StObj.SavePath)
> > ThreadWorkbook.Close()
> > ThreadExcel.Quit()
> > ThreadExcel = Nothing
> > StObj = Nothing
> > End Sub
> >
> >
> > --
> > Richard A. Welch
> > IT Manager
> > House of Raeford Farms, Inc. - Raeford Division
> >
>
>
>

Lenn
Mon Aug 22 15:51:14 CDT 2005

A word of advice; Do NOT mix Excel Automation with .NET Framework 1.1
runtime. Especially for what you are trying to do. Once an instances of excel
is created, it becomes very 'difficult' to release it effectively. Anything
you 'touch' or instantiate under that instance; Workbook, Worksheet, cell,
etc. will hold a reference to COM. and you have to kill every single
Workbook, worksheet, cell to get rid of Excel process. You will find lots of
threads in this and other newsgroups regarding this issue. Also doing COM
Interop, and that's what you're doing with Excel, will not yeild the best
performance results, it seems you really care about that. No number of
threads will help you with that, if anything they will make it worse by
deadlocking.
I had to develop a very similar application, I ran into so many problems
with Excel and .NET, that I finally gave up on it, and developed a solution
in good old Vb6 and a few VBA Excel Macros. You could probably do the same
thing.


"Richard Welch" wrote:

> Willy,
> I agree, it seems definately wrong to have to do multiple instances of
> Excel to accomplish what I need to do. If there is a way to create between 3
> and 4 thousand different workbooks quicker, I would definately like to know.
> I am trying to multithread to take advantage of the hyperthreading availalbe.
> The process I am trying to write takes over 30 hours to accomplish in a
> single Excel instance. I would like to be able to hold it down to a smaller
> number of threads, but am unsure how to accomplish this.
> --
> Richard A. Welch
> IT Manager
> House of Raeford Farms, Inc. - Raeford Division
>
>
>
> "Willy Denoyette [MVP]" wrote:
>
> > Why do you have 125 Excel processes running in the first place? Do you mean
> > you create an instance of Excel in each worker thread you start? Well this
> > is wrong, you should only create a single Excel instance in your main thread
> > and you should not use any other thread at all.
> >
> > Willy.
> >
> >
> > "Richard Welch" <RichardWelch@discussions.microsoft.com> wrote in message
> > news:DA8BAC21-6B2A-4858-92EB-51AC07D63573@microsoft.com...
> > >I am having a problem with QueueUserWorkItem. I thought there was a maximum
> > > number of worker threads a threadpool had available, 25 per processor, or
> > > something like that. However, when executing the following code, after
> > > about
> > > 125 or so Excel processes are running I get:
> > >
> > > "An unhandled exception of type
> > > 'System.Runtime.InteropServices.COMException' occurred in Fiscal Year
> > > End.exe"
> > >
> > > Additional information: Server execution failed
> > >
> > > When another thread starts to execute. I would expect at most 50
> > > concurrent
> > > Excel threads on a P4 hyperthreading system. Is there something wrong in
> > > my
> > > code that keeps the system from recognizing the maximum thread count?
> > >
> > > Private ExcelPool As System.Threading.ThreadPool
> > >
> > > <Some Code>
> > >
> > > For Each AccountRow In ReportingDS.Tables("Ledger Accounts").Rows
> > > SummaryRows = AccountRow.GetChildRows("Account Summary")
> > > For Each SummaryRow In SummaryRows
> > > TargetRange = WS.Cells(CurrentRow, 1)
> > > TargetRange.Resize(1, _
> > > SummaryRow.ItemArray.GetLength(0)).Value =
> > > SummaryRow.ItemArray
> > > TargetRange = WS.Cells(CurrentRow, 1)
> > > WS.Hyperlinks.Add(TargetRange, SaveSubDirectoryString & _
> > > WS.Cells(CurrentRow, 1).Value & ".xls", , _
> > > "Click on account number to open account activity.", _
> > > WS.Cells(CurrentRow, 1).Value)
> > > CurrentRow += 1
> > > Next
> > > Dim StObj As New StateObj
> > > StObj.DataRowArg = AccountRow.GetChildRows("Account Detail")
> > > StObj.DataTableArg = ReportingDS.Tables("Ledger Detail").Clone
> > > StObj.SavePath = SaveSubDirectoryString & AccountRow(0) + ".xls"
> > > StObj.DSName = AccountRow(0)
> > > ExcelPool.QueueUserWorkItem(New System.Threading.WaitCallback _
> > > (AddressOf SaveExcelData), StObj)
> > > Next
> > >
> > > <More Code>
> > >
> > > Private Sub SaveExcelData(ByVal StateObj As Object)
> > > Dim StObj As StateObj
> > > StObj = CType(StateObj, StateObj)
> > > Dim ThreadExcel As New Excel.Application
> > > Dim ThreadWorkbook As Excel.Workbook = ThreadExcel.Workbooks.Add
> > > While ThreadWorkbook.Worksheets.Count > 1
> > > ThreadWorkbook.Worksheets(ThreadWorkbook.Worksheets.Count).Delete()
> > > End While
> > > Dim ThreadWorksheet As Excel.Worksheet = ThreadWorkbook.Worksheets(1)
> > > Dim DR As DataRow
> > > Dim DC As DataColumn
> > > For Each DC In StObj.DataTableArg.Columns
> > > ThreadWorksheet.Cells(1, DC.Ordinal + 1) = DC.ColumnName
> > > Next
> > > ThreadWorksheet.Rows(1).Font.Bold = True
> > > ThreadWorksheet.Rows(1).Font.Underline =
> > > Excel.XlUnderlineStyle.xlUnderlineStyleSingle
> > > Dim CurrentRow As Integer = 2
> > > Try
> > > For Each DR In StObj.DataRowArg
> > > For Each DC In StObj.DataTableArg.Columns
> > > ThreadWorksheet.Cells(CurrentRow, DC.Ordinal + 1).Value =
> > > DR(DC.Ordinal)
> > > Next
> > > CurrentRow += 1
> > > Next
> > > Catch ex As Exception
> > > MsgBox(ex.Message, MsgBoxStyle.Critical, "Thread Excel")
> > > ThreadWorksheet = Nothing
> > > ThreadWorkbook = Nothing
> > > ThreadExcel.Quit()
> > > ThreadExcel = Nothing
> > > End Try
> > > ThreadWorkbook.SaveAs(StObj.SavePath)
> > > ThreadWorkbook.Close()
> > > ThreadExcel.Quit()
> > > ThreadExcel = Nothing
> > > StObj = Nothing
> > > End Sub
> > >
> > >
> > > --
> > > Richard A. Welch
> > > IT Manager
> > > House of Raeford Farms, Inc. - Raeford Division
> > >
> >
> >
> >