Poor Performance evaluating the Aspose.Cells for Reporting Services product

cam4371 · February 18, 2010, 10:13am

We are evaluating your product for the ability to export large reports that exceed the 65,536 row limit imposed by reporting services default excel export. Unfortunately, after installing the extension on the report servers we are finding the rendering extension unusable.

The default excel extension on a single column 65,532 row report containing date/time stamp exports in 12 seconds. The Aspose extension exports the same report to tab delimited or xlsx in 10 minutes 18 seconds. The problem with this is we have larger reports containing higher row numbers and multiple columns. So far, I have been unable to even export the larger reports.

The report was configured to have no timeout but it still cancels execution and fails after a full hour of working on rendering. The server itself spends the entire time running between 50-65% CPU utilization. Is this expected behavior?

This message was posted using Aspose.Live 2 Forum

amjad.sahi · February 18, 2010, 12:05pm

Hi,

Could you post your sample RDL file here, we will look into it soon.

Thank you.

cam4371 · February 18, 2010, 1:35pm

I’ve posted it but frankly the test RDL mentioned is extremely simple. It selects just shy of 65,536 cells from a table that contain timestamp information. There is really no formatting beyond the default that SSRS does. Exporting this to any of your rendering extensions takes nearly 11 minutes.

nausherwan.aslam · February 19, 2010, 12:42am

Hi,<?xml:namespace prefix = u1 /> <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for sharing the RDL file.

Please modify the configuration parameter for Aspose.Cells for Reporting Services 2005 at Aspose.Cells.ReportingServices.xml file as mentioned below.

</AutoFit>

Please try it and let us know if it improves the performance at your end.

Thank You & Best Regards,

cam4371 · February 19, 2010, 8:53am

That seems to have cured the performance issue I was seeing for my test report and for the real report that wasn’t completing. Thank you for the prompt response. Is there any documentation on the settings in that file and how they affect output?

amjad.sahi · February 19, 2010, 11:29am

Hi,

Well, auto-fitting rows is a time consuming operation for larger files which would affect performance to certain extent, so disabling it would optimize the performance to great deal.

Thank you.

cam4371 · February 22, 2010, 8:51am

Sorry we didn’t test this before but the same performance issue is happening when using our 2008 report server with this product. Is there something else that needs to be done for an SSRS 2008 installation?

nausherwan.aslam · February 23, 2010, 12:25pm

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

We will get back to you regarding your query.

Thank You & Best Regards,

nausherwan.aslam · February 26, 2010, 3:08am

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Please modify the configuration parameter for Aspose.Cells for Reporting Services 2008 at Aspose.Cells.ReportingServices.xml file as mentioned below.

</Performance>

Please try it and let us know if it improves the performance at your end.

Thank You & Best Regards,

cam4371 · February 26, 2010, 9:53am

That didn’t resolve my performance issues on the report I’m testing at the server. I would also like to point out we have around 3,500+ reports in our production environment.

nausherwan.aslam · March 1, 2010, 12:31am

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for your feedback.

What is the report define format when you published it with SQL Server 2008 Reporting Services server? 2005 define format or 2008 define format?<?xml:namespace prefix = u1 />

Please post your Aspose.Cells.ReportingServices.xml here. We will check this issue soon. Your report name must not include ‘.RDL’ in the performance configuration parameter of Aspose.Cells.ReportingServices.xml file.

Also, we will publish new version for Aspose.Cells.ReportingServices in a few days, in order to make performance configuration the default configuration. We hope that new version can reduce performance configuration issue in order to solve your requirement.

Thank You & Best Regards,

cam4371 · March 1, 2010, 9:04am

The reports are primarily in the 2005 definition format as this is for our upgrade to 2008. Going forward we will start using the 2008 format. The report I'm using for testing has 11 columns and roughly 85,000 rows.

How do we address the performance for large numbers of reports? Or, will I need to add all large reports to the XML file?

amjad.sahi · March 4, 2010, 6:07am

Hi,

Today, we have releases the new version of Aspose.Cells.Reporting Services (v1.5.1: http://www.aspose.com/community/files/52/ssrs-rendering-extensions/aspose.cells-for-reporting-services/entry225304.aspx). Please try it.

We set performance configurations the default configuration. In the new version, please see the default performance parameters.

===========================

============================

The value of performance defaults is ON and Aspose.Cells.ReportingServices renders all the report at performance-optima mode.

If you only need performance for report, you need to re-install new version and not update any default parameters.

11 columns and roughly 85,000 rows is a large data. It needs to use more and more memory space of the server. So it may happen out memory exception.

If you find this issues, please share us some details, e.g. How large memory space does ReprotingServicesSerivec.exe use?

We will check it soon.

Thank you.

cam4371 · March 10, 2010, 1:23pm

I removed Aspose.Cells from the report server and deployed the latest version. I verified the new Performance configuration was set as noted but the report still won't export from 2008. I do not see memory usage climbing in a strange manner for ReportingServicesService.exe. The service started at 115 Mb used. When I click View Report, the usage climbs to roughly 600 Mb Once the Export button is clicked, the CPU usage jumps to 50-70% and stays there until a timeout happens. Memory usage fluctuated up and down within 200 Mb of the 600 Mb amount but overall memory usage of the server stays under 60%. I tried turning off the timeout and it ran the server at that rate for almost 2 hours before I needed to cancell the job.

I noticed the xml file no longer has the tag from before. Would this be something that I can/should add back in? Is there anything else we can do for testing this?

nausherwan.aslam · March 11, 2010, 3:02am

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for the feedback.

Please check the deployed Aspose.Cells.ReportingServices.DLL version for SQL Server 2008 Reporting Services. The DLL file will be in ${SQL Server 2008 Reporting Services Installation Folder}\Reporting Services\ReportServer\bin folder. Please make sure it is version v1.5.1.

If not, please copy ${Aspose.Cells.ReportingServices Installation folder}\Bin\SSRS2008\ Aspose.Cells.ReportingServices.dll into ${SQL Server 2008 Reporting Services Installation Folder}\Reporting Services\ReportServer\Bin folder.

New version Integrates all performance configure parameters. The default value of Auto fit parameter is false. You do not need to modify it. We have done some performance tests and following are the details.

Our test environment:

Windows server 2003 64bit CPU E2200 2.20GHz, 2.00GRAM Virtual Memory 2-4G

SQL Server 2008 Reporting Server

Aspose.Cells.ReportingServices v1.5.1.

Aspose.Cells.ReportingServices.xml is not modified.

========================================================

<?xml version="1.0" encoding="utf-8"?>

==============================================================

Attached are the tested RDL files.

Our test result :

	columns	rows	Time	Memory size	result
Test 1	12	86000	160s	1.2G	export excel2007 file
test 2	20	86000		1.7G	throw exception.

If the installed version is 1.5.1 and the configuration file is not modified, please try to increase memory size. If you still face the issue, please provide us your system environment details i.e. OS, Memory etc. We will check it soon.

Thank You & Best Regards,

cam4371 · March 11, 2010, 3:41pm

The report I'm using for testing shows up in the browser in about 18 seconds. The file exports to the standard CSV extension in about the same amount of time making a 20 Mb text file. It hasn't successfully exported using any of the Aspose.Cells extensions on my 2008 server.

Here is my setup for test run 1:
Windows 2003 SP2 x64
Dual-Core AMD Opteron 8220 SE @ 2.8 GHz
3.75 GB RAM and 2-4 GB virtual memory
DLL is v1.5.1.0
Xml is unmodified
Fresh server - 1.9 GB
Max usage during export 2.44 GB

We upgraded our system to this for the second run:
Dual-Core AMD Opteron 8220 SE @ 3.51 GHz
7.75 GB RAM and 2-4 GB virtual memory
Fresh server - 1.4 GB
Report visible in browser - 2.1 GB
Max usage during export - 2.6 GB

Both of these tests ran for the full 30 minutes using 50-80% of the CPU before timing out.

================================================
<?xml version="1.0" encoding="utf-8" ?>

- <Aspose>

- <logger>

</logger>

- <License>

</License>

- <CSVRender>

</CSVRender>

- <Password value="">

</Password>

- <Client>

</Client>

- <Page>

</Page>

- <Performance value="ON">

</Performance>

</Aspose>

================================================

cam4371 · March 11, 2010, 4:39pm

Increasing the timeout to 60 minutes had no change. The report server runs 50-75% CPU usage until the timeout period completes.

nausherwan.aslam · March 12, 2010, 12:00am

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for your feedback.

We have checked your reply but we cannot search a solution for your issues. We hope that you can provide us more information to sort out the issue.

Please copy Aspose.Cells.ReportingServices.xml into ${SQL Server 2008 Reporting Services Installation Folder}\Reporting Services\ReportServer\bin folder.
Please post install.log for Aspose.Cells for Reporting Services. The install.log file is in ${Aspose.Cells.ReportingServices Installation folder}\ folder.
Please post the “HKEY_LOCAL_MACHINE\SOFTWARE\Aspose\Aspose.Cells.ReportingServices” key of registry details information.
Please set logger parameter to debug mode.

========================================

Debug"/>

========================================

Please set ${Aspose.Cells.ReportingServices Installation folder}\ folder Write permission. Use Aspose.Cells Extension to render. Then Aspose.Cells.ReportingServices.log will get created.

Please post Aspose.Cells.ReportingServices.log file. The Aspose.Cells.ReportingServices.log file is in ${Aspose.Cells.ReportingServices Installation folder}\ folder too. If you do not find the file, please check installation folder writing permission and logger parameter in Aspose.Cells.ReportingServices.xml file.
Please post your RDL file. We will check your report soon.
Please tell us SQL Server 2008 Reporting Services deployment information.
Please post rsreportserver.config here. We will check it soon.
If this issue still exists, you should provide us your msn id. We will resolve this issue with you online.

Thank You & Best Regards,

cam4371 · March 12, 2010, 1:10pm

The failure to render is still happening both at 30 minute and 60 minute time outs.

1. I copied the xml file from the Aspose install directory as it doesn't exist in the bin directory.
2. Attached
3. Attached
4. Set after the copy in number 1.
5. Attached
6. Attached
7. SSRS 2008 SP1 (10.0.2531.0)
8. Attached
9. Working on getting approval to use an external IM client. I have a blackberry so you can send an email with a time you would like to work with me.

nausherwan.aslam · March 15, 2010, 1:41am

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for your feedback.<?xml:namespace prefix = u1 />

We have found the reason for the issues. Your RDL file define format is report define format for SQL Server 2005 Reporting Services. When the report define file for SQL Server 2005 Reporting Services is published using SQL Server 2008 Reporting Services server, its performance is poor as Aspose.Cells.ReportingServices extension rendering calls Microsoft Reporting Services on demand Report Rendering API. So, please upgrade your RDL file define to report define format for SQL Server 2008 Reporting Services.

Thank You & Best Regards,