开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜