Threading doesnot impact the performance while reading dataset records
I am having dataset with 5000 records. I am reading them in the below 2 functions and writing to excel.
FillDataRows1(worksheet)
FillDataRows2(workshe开发者_高级运维et)
private sub FillDataRow1(byval ws as worksheet)
Dim rowpointer As Integer = 0
While rowpointer <= dsCostUsage.Tables(0).Rows.Count - 1
While colpointer <= dsCostUsage.Tables(0).Columns.Count - 1
str = dsCostUsage.Tables(0).Rows(rowpointer)(colpointer).ToString()
DirectCast(ws.Cells(row, column), Range).Value2 = item
colpointer += 1
End While
End While
End sub
private sub FillDataRow2(byval ws as worksheet)
Dim rowpointer As Integer = 1001
While rowpointer <= dsCostUsage.Tables(0).Rows.Count - 1
While colpointer <= dsCostUsage.Tables(0).Columns.Count - 1
str = dsCostUsage.Tables(0).Rows(rowpointer)(colpointer).ToString()
DirectCast(ws.Cells(row, column), Range).Value2 = item
colpointer += 1
End While
End While
End sub
I am reading 1000 records in the first function and remaining in the second function.
The problem is it is taking minimum 4 min to complete this process.
So, I decided ro use THREADING as
Dim t As New Thread(AddressOf FillDataRows1)
Dim t1 As New Thread(AddressOf FillDataRows2)
t.Start(worksheet)
t1.Start(worksheet)
t.Join()
t1.Join()
When I create thread also it is taking same time .
Am i doing anything wrong in creation of thread? Is there any other way to improve the performance.
Every time you call an Excel function, it has to leave your application's thread, enter Excel's thread, execute the function, save the result, return to your application's thread, and retrieve the reesult. This is slow. Every individual ccall to Excel is slow. And a line such as:
Worksheets(1).Range("A1").Value += 1
is four separate calls to Excel, one for Worksheets
, one for Range
, and two for Value
.
Do as much work as possible in your application. Read and write arrays of data from Excel using the Range.Value
property over a large range.
Edit: I completely forgot to address the actual question.
Threading doesn't help because of the massive bottleneck in accessing Excel. I'm going to guess that the Excel API is only available on a single thread. So with multiple threads accessing Excel, each thread has to wait for the other to finish before it can talk to Excel.
If you pull a lump of data out of Excel, then have multiple threads process this data and create a new data table inside your application, you will see improvements.
精彩评论