Hi,
We have built an application to use WinGrid to show data and then provided a feature to export the grid data to excel using UltraGridExcelExporter (Version used: 10.1)
The general performance of the export functionality for records greater than 25000 is slow and when the number is higher in the range of 40000 to 90000 we end up getting System.OutOfMemoryException.
Trying to isolate this problem, is this error and the performance anything to do with the client side memory or is there any limitation with ExcelExporter?
Also is there any stats related to performance of Export functionality for different size of records available for reference? This will be helpful to set a broad level expectations of the feature performance to users.
Looking at some of the older post of performance and out of memory exception i noticed few places the recomendation is to download latest hotfixes. There is one Service release for 10.1 on 15th Mar 2011, i will install this.
Any details related to the issue on how to go about addressing it will be helpful.
Exception Stack trace is as follow:
Process Name: mscorlibWin32 Thread Id: at System.Collections.Generic.Dictionary`2.Resize() at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add) at System.Collections.Generic.Dictionary`2.set_Item(TKey key, TValue value) at Infragistics.Excel.WorksheetCell.set_CellFormatProxy(WorksheetCellFormatProxy value) at Infragistics.Excel.WorksheetCell.get_CellFormatInternal() at Infragistics.Excel.WorksheetCell.get_CellFormat() at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.SetRegionRelativeToOrigin(UltraGridExporterHelper exportHelper, Rectangle rect, Object val, IWorksheetCellFormat cellFormat) at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.WriteValueCell(UltraGridExporterHelper exportHelper, UltraGridRow row, UltraGridColumn ugColumn, Rectangle valueCellRect) at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.ExportRowValuesCells(UltraGridExporterHelper exportHelper, UltraGridRow row) at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.WriteSingleRow(UltraGridExporterHelper exportHelper, UltraGridRow row) at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.ProcessSingleRow(UltraGridExporterHelper exportHelper, UltraGridRow row) at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.ProcessGridRowInternal(UltraGridExporterHelper exportHelper, UltraGridRow row, ProcessRowParams processRowParams) at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper.ProcessRow(UltraGridRow row, ProcessRowParams processRowParams) at Infragistics.Win.UltraWinGrid.RowsCollection.InternalTraverseRowsHelper(IUltraGridExporter exporter) at Infragistics.Win.UltraWinGrid.UltraGrid.Export(IUltraGridExporter exporter) at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.Export(UltraGrid grid, Worksheet worksheet, Int32 startRow, Int32 startColumn) at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.Export(UltraGrid grid, Workbook workbook)
Thanks,
Narasimha
A large number of performance and memory optimization have been made to the Excel code in the 11.1 development cycle. The Excel library is now very close to the performance and memory footprint of Microsoft Excel when opening and saving similar files. In addition, from the stack trace you have posted, I am very confident this will no longer be an issue for you with the 11.1 assemblies. Unfortunately, due to the amount of code changes required for these improvements, they were only made to the latest version of the code, so upgrading to the latest 10.1 SR will not help. You will have to wait for the 11.1 release.
thanks for the update Mike.
I'm curious if the ExcelExporter memory issues have been fixed in 11.2?
Thanks.
I believe there was a memory improvement implemented for a bug fix recently, but I'm not sure if it fixed this scenario. If you are still experiencing a memory issue, I can put you in contact with a Developer Support engineer so that a bug report can be created for you. Are you experiencing a problem?
We performed a comparitive study of the time and meory useage for exporting the data between the versions 10.1 (the version currently we are using) and 11.1. The stats break up is as follows:
Stats related to data: The data exported had 60 odd columns, with varied data types : int, string, datetime , decimal etc
Though we noticed an improvement in the performance the memory usage was still high and as a result we deffered the upgrade from 10.1 to 11.1.
Based on the above stats we have gathered (this may not necessaryly be 100 %accurate ) could you or someone from the developer support engineers confirm if the memory usage is on par or higher?
In my previous post the table data is not visible comletely , pasting the stats again
10.1
11.1
Records
Time Taken
Memory Usage
7,000
51sec579msec
34,664K
48sec235msec
38,144k
17,000
2min6sec299msec
53,575K
1min57sec252msec
54,692K
25,000
3min 59sec582msec
68,684K
2min34sec 284msec
69,540K
35,000
16min 46sec66msec
52,184K
4min28sec473msec
88,368K
I went ahead and upgraded from 10.3 to 11.2. I don't have any formal test results but I would say the Excel export is greatly improved as long as I don't use the ExcelExporter object and just manually populate the worksheet. I can export 660,000 records (2 cols) in about 30 seconds now manually, whereas if I used the ExcelExporter I waited minutes and minutes.
In any case here is the problem I have now, although it may not be an issue with the Excel library. I just don't know how the worksheet objects themselves function and how much memory they use. I have 2 recordsets calculated that I want to export to Excel. Each set contains 2 columns with around 660,000 rows. One contains 2 double values and the other is a double and a datetime. I wanted to have 1 workbook that contained a separate worksheet for each set. However, attempting to do this always causes an out of memory exception on the wb.Save(). I can export both sets into the same worksheet (set 1 in cols A, B and set 2 in cols D, E) successfully.
My assumption is that there is some base memory used to create the worksheet object, but that further memory is a function of the data exported (rows/columns). In other words, I was thinking I should be able to export more rows if I have fewer columns. Combining both sets onto a single worksheet doesn't seem to take much memory, so I was somewhat surprised a second worksheet causes an out of memory exception.
I have not been able to reproduce this on my end. Would it be possible for you to attach a sample to this forum thread which reproduces the issues you are referring to?
We struggle with poor performance, it can take up to a minute to export at times (on a strong 64 bit machine).
There are also inconsistencies between exporting to Office 2003 and 2007.
Breaking changes were also introduced on some of the colour attributes.
Can you describe for me the issues you are having with exporting to Excel in 2012 volume 1.
We are also strugging with the exporter in 12.1, will this be addressed any time soon?
For the exporter memory usage, it looks like you still have a problem, so I have forwarded this post to the Developer Support Manager and a DS engineer will be contacting you about this issue.
As for the extra memory used when exporting two worksheets, it's possible there are some more improvements that can be made in this scenario to reduce memory usage further. I'll have the DS engineer handle this as a separate issue.