RDLC LocalReport Export to Excel really slow

Question

We have an Asp.Net page running a RDLC Local report against an Oracle database in the backend, which is ridiculously slow when exporting to an Excel spreadsheet. I've done some investigation and established that the query itself shouldn't be to blame - I can run the query directly against Oracle using SQL Developer and export the results to Excel in around 5 seconds, yet when I run it via the asp.net page and the ReportViewer control it takes about 3 minutes to return.

Does anyone have any suggestions as to why this is so slow? The query returns around 8000 lines each with about 30 columns, so it's not a tiny result set but it's not massive either. Any suggestions on how we can optimize the report would be much appreciated.

I'm using Microsoft.ReportViewer.WebForms version 10.0.0.0, does anyone know if v11 has performance improvements?

EDIT: Tried ReportViewer v11, no improvement in speed.

Heathen

have you tried to render it directly to Excel without preview? is it still slow? msdn.microsoft.com/en-US/library/ms251839%28v=vs.90%29.aspx – Addictive

@Addictive yes I've tried that approach, it's just as slow as using the preview export controls. – Heathen

this is not a joke: to have performance improvements you can recreate your report for ReportViewer 8.0.0 (2005) or 9.0.0 (2008). you can also create directly a CSV file (or an XLS file if you have to format your data) from report dataset. there are also some guidelines to speed up ReportViewer 10.0.0 (if I remember well not using merged cells, etc.): try searching them on the web. – Addictive

Answer 1

If you have groupings inside your Report . From .NET 4 onwards when legacy CAS was removed ,RDLC Reports processed locally take enormous time for executing dynamic groupings or dynamic filters. There is an existing discussion related to this https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6d89e2ce-3528-465f-9740-7e22aa7b7aae/slow-performance-with-dynamic-grouping-and-reportviewer-in-local-mode?forum=sqlreportingservices
The best solution I have found amongst them is,
1. Create a new .NET 3.5 library project and create a file which does the actual processing of Report .

using Microsoft.Reporting.WebForms;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

//As you would expect, the new assembly WebReportviewer.FullTrustReportviewer
//all it does is just run the report. that's it. here is the code, it should be in a separated project:

namespace WebReportviewer
{
    [Serializable]
    public class FullTrustReportviewer : MarshalByRefObject
    {
        private ReportViewer FullTrust;
        public FullTrustReportviewer()
        {
            FullTrust = new ReportViewer();
            FullTrust.ShowExportControls = false;
            FullTrust.ShowPrintButton = true;
            FullTrust.ShowZoomControl = true;
            FullTrust.SizeToReportContent = false;
            FullTrust.ShowReportBody = true;
            FullTrust.ShowDocumentMapButton = false;
            FullTrust.ShowFindControls = true;
            //FullTrust.LocalReport.SubreportProcessing += LocalReport_SubreportProcessing;
            //FullTrust.LocalReport.SetBasePermissionsForSandboxAppDomain(new PermissionSet(PermissionState.Unrestricted));
        }

        public void Initialize(string DisplayName, string ReportPath, bool Visible, ReportParameter[] reportParam, string reportRenderFormat, string deviceInfo, string repMainContent, List<string[]> repSubContent)
        {
            FullTrust.LocalReport.DisplayName = DisplayName;
            FullTrust.LocalReport.ReportPath = ReportPath;
            //FullTrust.Visible = Visible;
            //FullTrust.LocalReport.LoadReportDefinition(new StringReader(repMainContent));
            FullTrust.LocalReport.SetParameters(reportParam);

            repSubContent.ForEach(x =>
            {
                FullTrust.LocalReport.LoadSubreportDefinition(x[0], new StringReader(x[1]));
            });
            FullTrust.LocalReport.DataSources.Clear();
        }

        public byte[] Render(string reportRenderFormat, string deviceInfo)
        {
            return FullTrust.LocalReport.Render(reportRenderFormat, deviceInfo);
        }
        public void AddDataSources(string p, DataTable datatable)
        {
            FullTrust.LocalReport.DataSources.Add(new ReportDataSource(p, datatable));
        }

        public SubreportProcessingEventHandler SubreportProcessing { get; set; }

        public static void LocalReport_SubreportProcessing(object sender, SubreportProcessingEventArgs e)
        {
            LocalReport lr = (LocalReport)sender;

            e.DataSources.Clear();
            ReportDataSource rds;

            if (e.ReportPath.Contains("DataTable2"))
            {
                DataTable dt = (DataTable)lr.DataSources["DataTable2"].Value;
                DataView dv = new DataView(dt);
                dv.RowFilter = string.Format("Id={0}", e.Parameters["Id"].Values[0]);
                rds = new ReportDataSource("DataTable2", dv.ToTable());
                e.DataSources.Add(rds);
            }
        }
    }
}

2. Call the code from the existing project

 public static byte[] GeneratePBAReport()
        {


            string l_spName = string.Empty;
            string l_reportPath = string.Empty;
            var repCol = new List<ReportDataSource>();

            var repParCol = new ReportParameter[1];
            if (id == "")
            {

                l_reportPath = HttpContext.Current.Server.MapPath("~\\.rdlc");
                l_spName = "";
            }
            else
            {
                l_reportPath = HttpContext.Current.Server.MapPath("~\\.rdlc");
                l_spName = "";
            }

            repParCol[0] = new ReportParameter("pID", "");

            var ds = new DataSet();
            using (var sqlCmd = new SqlCommand(l_spName, new SqlConnection(ConfigurationManager.ConnectionStrings[""].ConnectionString)))
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                var sqlParam = new SqlParameter() { Value = "", ParameterName = "" };
                sqlCmd.Parameters.Add(sqlParam);
                sqlCmd.CommandTimeout = 300;
                using (var sqlAdapter = new SqlDataAdapter(sqlCmd))
                {
                    sqlAdapter.Fill(ds);
                }
            }

            var rds = new ReportDataSource();
            rds.Name = "";
            rds.Value = ds.Tables[0];
            //l_report.DataSources.Add(rds);
            repCol.Add(rds);

            rds = new ReportDataSource();
            rds.Name = "";
            rds.Value = ds.Tables[1];
            //l_report.DataSources.Add(rds);
            repCol.Add(rds);

            rds = new ReportDataSource();
            rds.Name = "";
            rds.Value = ds.Tables[2];
            //l_report.DataSources.Add(rds);
            repCol.Add(rds);

            rds = new ReportDataSource();
            rds.Name = "";
            rds.Value = ds.Tables[3];
            //l_report.DataSources.Add(rds);
            repCol.Add(rds);

            Warning[] warnings;
            string[] streamids;
            string mimeType;
            string encoding;
            string extension;
            string deviceInfo;


            deviceInfo = "<DeviceInfo><SimplePageHeaders>True</SimplePageHeaders></DeviceInfo>";

            return NewDomainReport.Render("PDF", deviceInfo, "-" , l_reportPath, true, repCol, string.Empty, new List<string[]>(), repParCol);
        }

For really quick testing , you can try adding the CAS in web.config as mentioned in the article.

In an ASP Net application you can use <trust legacyCasModel="true" level="Full"/> in the system.web section of the web.config file to achieve the same result.

If the speeds show significant improvement the above code will behave the same . The benefit of above code is creating a separate AppDomain instead of affecting the whole solution.

Digital

<trust legacyCasModel="true" level="Full"/> worked for me, but I am unsure how to use your code example. What do I do with with the byte array being returned? How do I get a Web Forms page to render it? – Therese

Answer 2

Adding <trust legacyCasModel="true" level="Full"/> is not an option for me as i;m using dynamic types in my code.

This code is working:

public class CustomReportRenderer
{
    public static byte[] RenderReport(string reportPath, string rdlcDSName, DataTable rdlcDt, ReportParameter[] rptParams, string downloadFormat, out string mimeType, out string filenameExtension)
    {
        var assemblyDir = Path.GetDirectoryName(new Uri(Assembly.GetExecutingAssembly().CodeBase).LocalPath);
        AppDomainSetup setup = new AppDomainSetup()
        {
            ApplicationBase = AppDomain.CurrentDomain.SetupInformation.ApplicationBase,
            ConfigurationFile = AppDomain.CurrentDomain.SetupInformation.ConfigurationFile,
            LoaderOptimization = LoaderOptimization.MultiDomainHost,
            PrivateBinPath = assemblyDir
        };

        setup.SetCompatibilitySwitches(new[] { "NetFx40_LegacySecurityPolicy" });
        AppDomain _casPolicyEnabledDomain = AppDomain.CreateDomain("Full Trust", null, setup);
        try
        {
            FullTrustReportviewer rpt = (FullTrustReportviewer)_casPolicyEnabledDomain.CreateInstanceFromAndUnwrap(typeof(FullTrustReportviewer).Assembly.CodeBase, typeof(FullTrustReportviewer).FullName);
            rpt.Initialize(reportPath, rptParams);
            var bytes = rpt.Render(rdlcDSName, rdlcDt, downloadFormat, out mimeType, out filenameExtension);
            return bytes;
        }
        finally
        {
            AppDomain.Unload(_casPolicyEnabledDomain);
        }
    }

}

[Serializable]
public class FullTrustReportviewer : MarshalByRefObject
{
    private ReportViewer FullTrust;
    public FullTrustReportviewer()
    {
        FullTrust = new ReportViewer();
        FullTrust.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local;
    }

    public void Initialize(string reportPath, ReportParameter[] rptParams)
    {
        FullTrust.LocalReport.ReportPath = reportPath;
        FullTrust.LocalReport.SetParameters(rptParams);

    }
    public byte[] Render(string rdlcDSName, DataTable rdlcDt, string downloadFormat, out string mimeType, out string filenameExtension)
    {
        Warning[] warnings;
        string[] streamids;
        string encoding;
        FullTrust.LocalReport.DataSources.Add(new ReportDataSource(rdlcDSName, rdlcDt));
        var bytes = FullTrust.LocalReport.Render(downloadFormat, null, out mimeType, out encoding, out filenameExtension, out streamids, out warnings);
        return bytes;

    }
}

Call RenderReport method from aspx or mvc controller method

var bytes = CustomReportRenderer.RenderReport(rdlcFileFullPath, "ReportsDataSet", ds.Tables[0], rptParams, downloadFormat, out mimeType, out extension);
// Now that you have all the bytes representing the PDF report, buffer it and send it to the client.
Response.Buffer = true;
Response.Clear();
Response.ContentType = mimeType;
Response.AddHeader("content-disposition", "attachment; filename=" + fileName + "." + extension);
Response.BinaryWrite(bytes); // create the file
Response.Flush(); // send it to the client to download

Torritorricelli

Did this solution work for you long term? I am encountering this issue. Thanks. – Sian

Brilliant solution! Underrated also. A 23 pages of PDF was generating for 220 seconds before. Now is under a second. Thanks, Jyo! – Somnifacient

Answer 3

Simple expressions and conditional formatting may be to blame.

One of our reports had a very similar problem with large data (20 thousand + rows). The query was returning data quickly but it was slow to generate to the screen and even slower to export to excel. The problem was the same using report viewer 10 and 12.

Much to my surprise, removing a tablix sort, a date format expression and an alternating background row colour expression caused this report to generates much faster and exporting to excel went from taking over a half hour to about a minute.

Nipissing

My tablix doesn't have a sort and still generates too slow. – Issue

In our case the background row color expression was the biggest culprit, by far. So if you know for a fact that your query returns data quickly - try removing all expressions/formatting to see if it improves, if so, add them back one by one to see how much time each one costs. – Nipissing

I think the easiest way is to simply migrate the report to run on SSRS... So bad that MS is letting RDLC die :( – Issue

Answer 4

For Microsoft.ReportViewer.WebForms, Version=15.0.0.0 in asp.net c# .net framework 4.8, I put this in mi code:

System.Security.PermissionSet secSetting =new System.Security.PermissionSet(System.Security.Permissions.PermissionState.Unrestricted); ReportViewer1.LocalReport.SetBasePermissionsForSandboxAppDomain(secSetting);

I taked it from here https://learn.microsoft.com/en-us/answers/questions/1045072/sql-server-reporting-services-rdlc-performance-iss

Titanium