RDLC LocalReport导出到Excel真的很慢 [英] RDLC LocalReport Export to Excel really slow
问题描述
我们有一个Asp.Net页，在后端对Oracle数据库运行RDLC本地报告，这在导出到Excel电子表格时非常慢.我进行了一些调查，确定查询本身不应该受到指责-我可以使用SQL Developer直接针对Oracle运行查询，并在5秒钟左右将结果导出到Excel，但是当我通过asp运行它时.网页和ReportViewer控件大约需要3分钟才能返回.
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.
有人对为什么这么慢有任何建议吗?该查询返回大约8000行，每行大约30列，因此它不是一个很小的结果集，但也不是很大.我们将对如何优化报告的任何建议表示赞赏.
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.
我正在使用Microsoft.ReportViewer.WebForms 10.0.0.0版，有人知道v11是否具有性能改进吗?
I'm using Microsoft.ReportViewer.WebForms version 10.0.0.0, does anyone know if v11 has performance improvements?
编辑:尝试使用ReportViewer v11，但速度没有提高.
Tried ReportViewer v11, no improvement in speed.
推荐答案
如果您的报表中包含分组.从.NET 4开始，当删除旧版CAS时，本地处理的RDLC报告需要花费大量时间来执行动态分组或动态过滤器.没有与此
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.从现有项目中调用代码
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);
}
要进行真正的快速测试，您可以尝试按照本文所述在web.config中添加CAS.
For really quick testing , you can try adding the CAS in web.config as mentioned in the article.
在ASP Net应用程序中，可以在web.config文件的system.web部分中使用<trust legacyCasModel="true" level="Full"/>获得相同的结果.
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.
如果速度显示出显着改善，则上面的代码将具有相同的行为.上面代码的好处是创建了一个单独的AppDomain，而不是影响整个解决方案.
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.
这篇关于RDLC LocalReport导出到Excel真的很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
