Threading doesnot impact the performance while reading dataset records

Question

I am having dataset with 5000 records. I am reading them in the below 2 functions and writing to excel.

FillDataRows1(worksheet)
FillDataRows2(worksheet) 

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.

please watch your formating - and if your "worksheet" is slow and not able to do this in parallel (and I suppose just that) than you will be a bit slower with this — Random Dev, Aug 29 '11 at 18:54
Threading doesn't always make things go faster - often it's just so you can also do something else while your task is getting done.... — Tim Williams, Aug 29 '11 at 19:11
@Tim :Is there anyway to make it fast, I want to fill excel within a minute. — Ramesh, Aug 29 '11 at 19:16
What is "excelLib" ? Does it have any options for transferring items in a block rather than individually? Maybe also consider creating some local variables to cache lookups like dsCostUsage.Tables(0).Columns.Count instead of getting the value over and over. — Tim Williams, Aug 29 '11 at 19:40
@Ramesh: so you're automating Excel directly? That wasn't clear from your post, which was why I asked. In VBA you can assign a whole range in one shot (to assign all values from a 2-d array to a worksheet for example) - you should be able to do that from .NET, and it should be much faster. See: http://support.microsoft.com/kb/302094 — Tim Williams, Aug 29 '11 at 20:01
@Ramesh: see also: http://stackoverflow.com/questions/3096090/fastest-way-to-write-cells-to-excel-with-office-interop — Tim Williams, Aug 29 '11 at 20:08

Hand-E-Food · Answer 1 · 2011-08-29T23:16:28.717

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.

Threading doesnot impact the performance while reading dataset records

1 Answers1