List iteration taking so much time?

Question

I have List (of class). having 1800 of count and each object has 90 properties. When I terate earch with 90 properties taking more and more time. How to resolve this

 Dim cellIntStyle As HSSFCellStyle = hssfworkbook.CreateCellStyle
 cellIntStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#")

 Dim cellDateStyle As HSSFCellStyle = hssfworkbook.CreateCellStyle
 cellDateStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(Format
                                                     ("dd-MMM-yyyy"))
 For Each mReport As Report In dtExcel
        row = sheet1.CreateRow(iRow)
        j = 0
        For Each prop As PropertyInfo In props
            Dim value As Object = prop.GetValue(mReport, Nothing)
            If IsInt(value) Then
                CreateRow(row, j, CType(value, Integer), cellIntStyle)
            ElseIf IsDate(value) Then
                CreateRow(row, j, String.Format("{0:dd-MMM-yyyy}", 
                                          value), cellDateStyle)
            Else
                CreateRow(row, j, value)
            End If
            j = j + 1
        Next

        iRow = iRow + 1 // Coming here taking so long... how to make it fast.
    Next



    Private Sub CreateRow(ByRef row As HSSFRow, ByVal colId As Integer, 
                                          ByVal value As String)
        row.CreateCell(colId).SetCellValue(value)
    End Sub
    Private Sub CreateRow(ByRef row As HSSFRow, ByVal colId As Integer, 
                                ByVal value As Integer, 
                                ByVal cellStyle As HSSFCellStyle)
        Dim cell As HSSFCell = row.CreateCell(colId)
        cell.SetCellValue(value)
        cell.CellStyle = cellStyle
    End Sub
    Private Sub CreateRow(ByRef row As HSSFRow, ByVal colId As Integer, 
                                                ByVal value As String, 
                                      ByVal cellStyle As HSSFCellStyle)
        Dim cell As HSSFCell = row.CreateCell(colId)
        cell.SetCellValue(value)
        cell.CellStyle = cellStyle
    End Sub

Answer 1

Other than "iterate over less data," I don't see a straightforward solution here. How much work you have to do scales like (records*fields_per_record); you're stuck passing through your inner loop ~162,000 times given the numbers you provided.

Answer 2

It's hard to tell from the code snip what you're doing, but using PropertyInfo is a pretty big clue that there's some reflection going on (that and the use of GetValue).

Further, everything is funneling through VALUE which is of type object, which is kind of the .net equivalent of the old vb6 VARIANT.

All that typecasting is going to cost you.

Instead, if there was way to get the PROPS list into some sort of already typed objects, so you could avoid all the ISINT, ISDATE, etc, calls, and the GetValue calls, you should see a pretty decent increase in speed.

That's where I'd look first.

Answer 3

Buy a faster processor and add some more memory.

Answer 4

Firstt things first, your code is a mess. The indentation is unclear and iRow is incremented is if in a loop when inside a For Each on reports. I suspect you are iterating over the same data over and over again.

Second, at each row, you test over and over again what types the columns have. Now, since props is not assigned to inside the list, a considerable optimization would be to remove IsInt and IsDate tests by somehow caching what types they have.

Also, If you can preallocate all the cells for a single row before looping over the properties, you might get a nice gain in performance!

However, as other have pointed out, nothing in this will change your code's complexity. It will always remain O(number of rows * number of cells), meaning as you double the amount of data, you should expect to double the computation time.