Hello,
I have a small question regarding a performance difference between using the export function and the async export function on the UltraGridExcelExporter (version 11.2.20112.1010).I noticed that when using the export function its up to 3 times faster than using the async export.
A simple example that exports 54539 rows is included, as you will see:- export takes up to 94852 milliseconds- async export takes up to 325792 milliseconds
I noticed the cpu only takes up to a third of its total "power" when using async export; which explains the difference in timings.But my question is: why does the async export only uses a third of the cpu resources available?
We would like to use async export but if it's that much slower we are "forced" to use the normal export functionalities.
Thanks for your advice.
Hi,
When exporting asynchronously, the export is done using slices of time. That means that the component does a little bit of exporting, then releases processing back to the application for a time, then does a little but more of the export, etc.
That's the nature of asynchronous exporting. So exporting asynchronously will always take longer than synchronous exporting. That's just the laws of physics. The time has to come from somewhere. :)
The trade off is, of course, that during the asynchronous export operation, the UI of the entire application doesn't lock up - so the user can still do other things.
You might be able to speed up the asynchronous export by giving more time to the export process and less free time to the application. You can adjust the time slices using a couple of static properties:
Infragistics.Win.UltraWinGrid.AsynchronousExportManager.AsynchronousExportDurationInfragistics.Win.UltraWinGrid.AsynchronousExportManager.AsynchronousExportInterval
By increasing the duration or deceasing the interval you will give more time to the export and less time to the application. The risk there is that the UI for the rest of the application might not be as responsive while the export is in progress - and this may vary based on the speed of the machine, so just because it's fine on your machine doesn't necessarily mean it will be fine on a user's machine if their machine is slower than yours.
Hello Mike,
i have got the same perfomance problems on export huge data from ultragrid to excel (200.000 rows / 50 columns).
I am having problems to understand, that the cpu usage is not greater than 4% with exportAsync and not over 14 % on regular export() method.
Why isn't it possible to use all cpu kernels (more than one cpu-core) by higher cpu usage than 4% - 10% working power.
Here's my code example:
Dim ultraExcelExporter As New UltraGridExcelExporter() Try 'AddHandler ultraExcelExporterAsync AddHandler ultraExcelExporter.InitializeColumn, AddressOf InitializeColumn_OnExport _ultraWorkBook = New Workbook(pWorkbookFormat) If pFileName = String.Empty Then _ultraWorkBook.Worksheets.Add("Tabelle1") Else Dim worksheetName As String = pFileName.Substring(0, Math.Min(pFileName.Length, 31)) _ultraWorkBook.Worksheets.Add(worksheetName) End If Dim oldVisible As New ArrayList Dim counter As Integer = 0 For Each col As UltraGridColumn In Me.DisplayLayout.Bands(0).Columns If Not col.IsChaptered Then Dim colInfo As New clsColInfo(clsPropertyInfo.IsDesignMode(Me), Me.Username, Me.Name, FindForm().Name, _ col.Header.Caption, col.Key) oldVisible.Add(col.Hidden) col.Hidden = Not colInfo.ColExcelExport End If Next For Each col As UltraGridColumn In Me.DisplayLayout.Bands(0).Columns If Not col.IsChaptered Then col.Hidden = oldVisible.Item(counter) counter += 1 End If Next RemoveHandler ultraExcelExporter.ExportEnded, AddressOf ExportFinished AddHandler ultraExcelExporter.ExportEnded, AddressOf ExportFinished deactivatedControls = New List(Of String)() ' ultraExcelExporter. SetControlsActive(False, ParentForm.Controls)
ultraExcelExporter.ExportAsync(Me, _ultraWorkBook)
Async Export is up to 3 times slower than regular Export()
I hope you can help us to speed up the Excel export.
Greetings,
A. Nikolov
here's the code:
Try _ultraWorkBook = New Workbook(pWorkbookFormat) If pFileName = String.Empty Then _ultraWorkBook.Worksheets.Add("Tabelle1") Else Dim worksheetName As String = pFileName.Substring(0, Math.Min(pFileName.Length, 31)) _ultraWorkBook.Worksheets.Add(worksheetName) End If Dim oldVisible As New ArrayList Dim counter As Integer = 0 For Each col As UltraGridColumn In Me.DisplayLayout.Bands(0).Columns If Not col.IsChaptered Then Dim colInfo As New clsColInfo(clsPropertyInfo.IsDesignMode(Me), Me.Username, Me.Name, FindForm().Name, _ col.Header.Caption, col.Key) oldVisible.Add(col.Hidden) col.Hidden = Not colInfo.ColExcelExport End If Next For Each col As UltraGridColumn In Me.DisplayLayout.Bands(0).Columns If Not col.IsChaptered Then col.Hidden = oldVisible.Item(counter) counter += 1 End If Next RemoveHandler ultraExcelExporter.ExportEnded, AddressOf ExportFinished AddHandler ultraExcelExporter.ExportEnded, AddressOf ExportFinished deactivatedControls = New List(Of String)() ' ultraExcelExporter. SetControlsActive(False, ParentForm.Controls)
In various aggregation level the grid has round-about 100.000 rows and 50 columns.
It needs up to 12 minutes to export non-async and 34 minutes async.
For both modes -only- 1 CPU kernel is working:
async: 4% usage rate
sync: 14% usage rate
I think the export AND exportAsync method could use more of cpu power.
Thanks for you answer,
Andy Nikolov
Hi Andy,
A code snippet is not sufficient for us to investigate the issue. There are too many unknowns, such as the contents of the grid, the data types, the appearances, and a whole host of other grid properties and settings that could affect performance. Can you post a working sample project?
i just wanted to let you know that i have solved the problem on my own.
The reason for the bad excel export perfomance is, that lists and have been exportet.
I have just changed the following properties from ultraGridExcelExporter class:
In my case these two properties have been responsible for the bad excel export perfomance up to 34 minutes (101150 rows á 60 columns).
Now the export takes about 7-8 minutes async!
Thanks for your help & have a nice day.
Out of curiosity, which one caused the bulk of the time?
Do you have a lot of formulas in your grid?
How many different ValueLists are you using?
Hi Mike,
i expected that the export method only uses all the data values that the grid shows at runtime (all columns that are header.hidden = false).
I have used two ValueList and no ultragrid formulas within it's cells, but the databinded object(List) behind the datasource has some big list properties and readonly properties (which are calculating the property return values based on mathematical functions).