Excel Export OOM exception in Pivot Grid

[Shahin]

Hello,

While exporting data from Pivot Grid into Excel, always we are getting OOM (Out Of Memory) exception. Even the Pivot Grid contains of 20000 records.

And if we export 4000-5000 records, the excel export is very very slow.

If we increase the number of records then we are getting OOM exception.

The data binding and other things are working fine as we expected with this Pivot Grid. Only thing we are facing the issue is Pivot Grid Excel export.


Actually we have implemented the code based on the below link provided by Telerik. In the Telerik SDK browser also the Pivot Grid is not exporting well. There also we are getting OOM exception if the Pivot Grid contains more than 20000 records.

http://www.telerik.com/help/wpf/radpivotgrid-features-export.html


Please help me resolve the Pivot Grid - Excel export OOM and performance issues.


Thanks,
Shahrooz 

[Polya]

Hello Shahrooz,

This issue is related to the available RAM of the machine used for the export.
Referring to the SDK example there are 2 places where the OOM exception might occur. Could you please check whether the exception is caused when calling the pivot.GenerateExport() or whether it is when exporting the XslxFormatProivider to a workbook . Both of these operations have high memory consumption.
I might suggest a way to optimize the performance by excluding the export of the top and left borders of the cells. Setting them leads to huge calculations which are slowing down the whole export operation.

So to boost the export you should change the following line:

cellSelection.SetBorders(new CellBorders(leftBorder, topBorder, rightBorder, bottomBorder, insideBorder, insideBorder, null, null));

To:

cellSelection.SetBorders(new CellBorders(null, null, rightBorder, bottomBorder, insideBorder, insideBorder, null, null));

Hopefully this helps and is suitable for your scenario.

Regards,
Polya
Telerik

 

 

[Shahin]

Hello Polya,

Thanks for your reply.

The solution which you have given is working. The OOM issue is occurring in pivot.GenerateExport() function.
The cellSelection.SetBorders function is calling after the GenerateExport function. But the error is raising before cellSelection.SetBorders function.

This issue occurring with more than 25000 records. Not only OOM exception, it is exporting slowly also.

Please help me to resolve this OOM issue and performance issue while exporting to Excel from Pivot Grid.

We are using WIN8 machine with 8Gb RAM. I think it is not related to RAM and all.

Thanks,
Shahrooz

[Polya]

Hello Shahrooz,

In the implementation of pivot.GenerateExport() we are using a List to store the generated export objects.
There are a few limitations of the Framework that may cause problems in such huge exports:
1) Currently, the Microsoft implementation of a List<T> can hold up to Int32.MaxValue count of objects ( just above 2 billion ).
2) Also in the Microsoft implementation of the CLR there's a maximum of 2GB object size.
That said if an object that is above 2GB is created the system throws OOM.

With the .Net Framework 4.5 Microsoft introduce the gcAllowVeryLargeObjects element, that when set enables arrays that are greater than 2 GB in size. You can find more information about it here.
Could you please give setting it a try and let us know whether this helps with the exception:

<runtime>
    <gcAllowVeryLargeObjects enabled="true"/>
</runtime>

Keep in mind that in order for it to work:
1) The platform has to be 64 bit.
2) The application framework version have to be 4.5.

Regards,
Polya
Telerik

 

 

[Shahin]

Hello Polya,

Our application framework is 4.0. And we are building the application in x86 mode because of some 32 bit machines.
So help me to resolve PivotGrid Excel export performance and OOM issues in the above environment.

Thanks
Shahrooz

[Kalin]

Hello Shahrooz,

I'm afraid that the issue here is caused by framework limitation and the only solution would be the one we already had suggested you in our previous post.

If you have any other questions or concerns, please do not hesitate to contact us.

Regards,
Kalin
Telerik