Question multithreading

[suja]

Hi All
I am new to multithreading concept in VB.NET. I am writing a code to compare multiple excel sheets. I came across a problem where in time taken to compare a single excel is 7 min, for two excels it is 14 and so on... Even after using threads its the same. What is the problem with my below code. can any one please help
Public Class Form2
Inherits System.Windows.Forms.Form
Public Shared Property xlTmp3 As Excel.Application
Public Shared Property xlWb As Excel.Workbook
Private Threads(256) As Threading.Thread
Private CompareClassObjects(256) As CompareClass2
Private Sub Button4_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim cr As Integer
Dim CheckedItemArray1(2), CheckedItemArray2(2) As String
Dim start As Date = Date.Now
Dim totalTime As TimeSpan
CheckedItemArray1(0) = "C:\excel1.xlsx"
CheckedItemArray2(0) = "C:\here\excel2.xlsx"
CheckedItemArray1(1) = "C:\ excel11.xlsx"
CheckedItemArray2(1) = "C:\here\ excel21.xlsx"
xlTmp3 = CreateObject("Excel.Application")
xlWb = xlTmp3.Workbooks.Add
xlTmp3.Visible = True
For cr = 0 To 1
CompareClassObjects(cr) = New CompareClass2(cr, CheckedItemArray1(cr), CheckedItemArray2(cr))
Threads(cr) = New Threading.Thread(AddressOf CompareClassObjects(cr).CompareStart)
Threads(cr).IsBackground = True
Threads(cr).Start()
Next
For cr = 0 To 1
Threads(cr).Join()
Next
Dim end1 As Date = Date.Now
totalTime = end1.Subtract(start)
MsgBox("COMPARISON PERFORMED!!! " & vbCrLf & "Total Time Taken : " & totalTime.Duration.ToString, vbOKOnly, "Status")
End Sub
End Class
Public Class CompareClass2
Public xlTmp1 As Excel.Application
Public xlTmp2 As Excel.Application
Public xlSht1 As Excel.Worksheet
Public xlSht2 As Excel.Worksheet
Public R1, DiffCount, SheetCnt As Long
Private value As Integer
Private firstexcel, secondexcel As String
Public Sub New(ByVal number As Integer, ByVal text1 As String, ByVal text2 As String)
value = number
firstexcel = text1
secondexcel = text2
Form2.xlWb.Worksheets.Add()
Form2.xlWb.Worksheets(1).Name = value + 1
Form2.xlWb.Worksheets(1).Cells.ClearContents()
End Sub
Public Sub CompareStart()
R1 = 3' just a variable used to notate as Rownumebr to put the output result in a new excel
DiffCount = 0 ' it will increment inside TestCompareWorksheets fn as and when mismatches are found
SheetCnt = 0 'to notate the sheet count
Call OpenExcels(value)
Call TestCompareWorksheets()
Call QuitExcels()
End Sub
Public Sub OpenExcels(ByVal i As Integer)
'here code for opening the excel
End Sub
Public Sub TestCompareWorksheets()
Dim w As Excel.Worksheet
Dim FirstSheetCnt, SecondSheetCnt, K AsInteger
FirstSheetCnt = xlTmp1.Worksheets.Count
SecondSheetCnt = xlTmp2.Worksheets.Count
K = 1
If FirstSheetCnt = SecondSheetCnt Then
ForEach w In xlTmp1.Worksheets
SheetCnt = SheetCnt + 1
xlSht1 = xlTmp1.Sheets(K)
xlSht2 = xlTmp2.Sheets(K)
Call CompareWorksheets()
K = K + 1
Next w
EndIf
End Sub
Public Sub CompareWorksheets()
'Actual code to compare the excel sheets
End Sub
Public Sub QuitExcels()
'code to closing the excels
End Sub
End Class

 

[RickJames]

Hi suja,

I've tried to something similar to this with MS word and multi threading a while back, I seemed to get the same results as you're describing.

It doesn't seem to matter that you spawn a new excel process for each thread, there seems to be some sort of queuing system on office OLE commands.. maybe somebody with more experience can clarify this? as I never resolved it.

however, maybe you could use a different approach, is there any reason you're doing the comparison directly through excel? could you not read in the excel data, then do the multithreaded comparison on your extracted data? thereby minimizing the number of COM interactions?

just a thought

Rick

 

[JohnH]

If an operation takes 100% CPU then doing that operation twice will take twice as long. Simultaneous 100% threads can actually slow each other down more than if they were run in succession. This is what I'm seeing when doing heavy multi-threaded tests with Excel automation, the CPU just can't process faster. The threads all execute asynchronously and finish indeterministically around the same time, though with increased time under increased load, as they should and must.

About your code, "Form2.xlWb..." is called from secondary thread. Here you are accessing a shared property by default form instance. When you access default form instance from secondary thread this will create and initialize a new form instance. That means for each thread you are implicitly creating a new Form2 instance, that invokes its constructor and calls InitializeComponent (that creates and initializes all the form controls), and all its fields are initialized. So you should move your shared properties to a Module, or else you have access them by UI thread or pass the initial form instance to thread.

"Threads(cr).Join()" calling this from button handler is bad, this will block your UI thread.

I don't know how you are processing, but probably it at least involves reading many cell values, and perhaps also setting many cell values. If you do this by accessing each cell it will add a really lot to processing time, if so see this article about getting/setting a range of values using arrays: How to automate Excel from Visual Basic .NET to fill or to obtain data in a range by using arrays
As an example, in one of my tests I was setting 40.000 values cell by cell which completed in 23 seconds, when I changed that to 8 array calls the same operation completed in only 1 second. When doing the same only reading cell values I got a 13:1 ratio in this test.