Perfomance problems on ExportAsync()

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

Hi,

I couldn't say why the process is not using the entire CPU. Maybe it simply doesn't need it because a lot of the processing is done by writing to the hard drive.

Anyway, exporting asynchronously will always be slower than exporting synchronously, because it's done by time-slicing. The advantage is that this doesn't lock up the UI while the export is in progress.

Hello Mike,

I hope infragistics will improve the infragistics excel export perfomance soon.

My computer is a fast one: 8 core cpu with a ultra fast ssd drive that can't have big bottlenecks and that does not justify the extremely slow  infragistics excel export with perfomance speed under 100 rows/sec is much slow. Even a I/O StreamWriter is faster. Why don't you you implement time-slicing AND parallel cpu core power over more then one kernel?

It would be amazing if the infragistics team could optimize the perfomance for excel exports.

My question:

At this thime i try to improve the perfomance by playing around with the interval and duration properties.

What are the maximum values for interval and duration properties?

Thank you very much.

Andy Nikolov

Hi Andy,

Marco Schwingel said:

perfomance speed under 100 rows/sec is much slow

That does sound pretty slow. Is that when exporting synchronously or asynchronously? If you can post a small sample project demonstrating the performance, I would be happy to take a look at it and see if there is anything that can be done to improve the performance.

Marco Schwingel said:

What are the maximum values for interval and duration properties?

There are no maximums as far as I know - other than int.MaxValue, of course, since these properties are integers.

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).

Greetings,

Andy Nikolov

Hi Andy,

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?

Hello Mike,

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:

1.             ultraGridExcelExporter.ExportFormulas = False
2.             ultraGridExcelExporter.ExportValueLists = False

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.

Greetings,

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?

Hello Mike,

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)
         

                ultraExcelExporter.ExportAsync(Me, _ultraWorkBook)

Async Export is up to 3 times slower than regular Export()

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