Hi
We’re experiencing some performance issues calling the importdataview function within a loop. Here’s an outline of the problem:
1. Have a datatable containing > 75000 rows
2. Create a dataview.
3. Loop through say 25 times, each time doing a rowfilter on the dataview (avg 4000 rows returned).
4. Call ImportDataView for each dataview, inserting side-by-side with InsertRows set to False.
What we’re finding is that for the first few calls to ImportDataView (eg 5-10), its very quick … usually within milliseconds. However, the performance gets worse and worse so that by the time we get to the 20th or so loop, it takes minutes to do the import. The result is that an excel file takes 20+ minutes to generate.
Note that we’re using Aspose Excel 2.7.2.3. I’m not sure whether this issue is only affected with this version and whether the performance is better in the more recent releases. We currently have a large number of reports using this version so we would like to avoid having to upgrade. (this is a large financial application which means upgrading would require extensive regression testing)
Any light you can shed on this issue would greatly be appreciated.
Steve
Environment:
ASP.NET 1.1
IIS 6 / WIN2003
ASPOSE 2.7.2.3
Sample code:
Dim dt as Datatable
’ load datatable from db
Dim currentColumn as Integer = 0
For i As Integer = 0 to 30
Dim dv as New DataView(dt)
dv.RowFilter = "id = " & i ’ sample
Worksheet(0).Cells.ImportDataView(dv, 0, currentColumn, False)
currentColumn += 5 ’ 5 column datatable
End For
The performance issus is caused by:
1. When more data are added into a worksheet, program will be slow.
2. In your case, please create a single dataview. That will be much faster.
When putting a large amount of data to a worksheet, you should add values to the cells, first by rows and then by columns. This approach will greatly improve the efficiency of your applications.
In your case, importing a dataview to A1:J4000 will be much faster than importing a dataview to A1:E4000 and importing a dataview to F1:J4000.