Controlling Threads

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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?
 
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.
 
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.
 
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
 
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.
 
Back
Top