有哪些通用方法可以使Reporting Services更快 [英] What are the generic ways to make Reporting Services faster

问题描述

虽然我了解到这个问题相当模糊，因为我没有为您提供尽可能多的详细信息，但我希望可以对我的世代代码或报表本身进行一些常规的改进加快他们的速度.我要求更多的硬件，但遭到拒绝.

public Stream GenerateReport(string reportName, string format)
{
    if (reportName == null)
        throw new ArgumentNullException("reportName");

    reportExecutionService.LoadReport(reportName, null);

    string extension;
    string encoding;
    string mimeType;
    ReportExecution2005.Warning[] warnings;
    string[] streamIDs;

    byte[] results = reportExecutionService.Render(format, null, out extension, 
        out encoding, out mimeType, out warnings, out streamIDs);

    return new MemoryStream(results);
}

每个报告本身需要6到10秒钟.我已经缩小了Reporting Services本身的瓶颈.我应该从哪里开始着手消除潜在的速度瓶颈.注意:已删除一些代码以保护无辜者.

解决方案

尽管与您发布的代码没有直接关系，但是在Reporting Services中编写报表时，您应该始终考虑以下几个通用增强功能:

1. 预加载报告表，以便它们已经聚合了本应在报告中聚合的所有数据.例如，如果报表数据源汇总了数千行数据，并且需要将多个表连接在一起，那么您应该创建一个预先汇总的表，该表将所有数据连接在一起，并且已经按报表所需的粒度汇总了数据. /p>

2. 如果要将参数传递到数据源中，则聚合的基础表应具有与将如何搜索表相对应的聚簇索引.例如，如果报表仅显示单个客户和给定交易日期范围内的数据，则应在客户和交易日期对聚簇索引进行排序.

3. 筛选数据应在数据源查询中而不是报表本身中进行.意思是，如果您对报告进行参数化以使其过滤数据，则应将参数传递给数据库，以便它返回较小的数据集.不要返回大量数据，然后过滤数据.使用多值参数时很容易犯此错误，因为使用多值参数的现成说明是在将数据返回给Reporting Services之后过滤数据.

我希望您已经在执行上述操作，并且这不是相关的帖子. :)

While I understand this question is fairly vague since I'm not giving you all as much detail as I'd like to, I'm hoping for some general improvements that can be made to my generation code or the reports themselves to speed them up. I've asked for more hardware, but have been denied.

public Stream GenerateReport(string reportName, string format)
{
    if (reportName == null)
        throw new ArgumentNullException("reportName");

    reportExecutionService.LoadReport(reportName, null);

    string extension;
    string encoding;
    string mimeType;
    ReportExecution2005.Warning[] warnings;
    string[] streamIDs;

    byte[] results = reportExecutionService.Render(format, null, out extension, 
        out encoding, out mimeType, out warnings, out streamIDs);

    return new MemoryStream(results);
}

The reports themselves are taking 6-10 seconds each. I've narrowed down the bottleneck to Reporting Services itself. Where should I start looking to removed potential speed bottlenecks. Note: some code has been removed to protect the innocent.

解决方案

Although not directly related to the code you posted, here are a couple of generic enhancements you should always consider when writing reports in Reporting Services:

1. Pre-load report tables so that they already aggregate any data that would have been aggregated in the report. For instance, if the report data source summarizes thousands of rows of data and requires joining multiple tables together, then you should create a pre-aggregated table that joins all the data together and already summarizes the data at the required grain for the report.

2. If you are passing parameters into the data source, then the aggregated underlying table should have a clustered index that corresponds with how the table will be searched. For instance, if the report only displays data for an individual customer and for a given transaction date range, then the clustered index should be ordered on the customer and transaction date.

3. Filtering data should occur in the data source query and not in the report itself. Meaning, if you parameterize your report so that it filters data, then the parameters should be passed to the database so that it returns a smaller set of data. Do not return a large set of data and then filter the data. It is easy to make this mistake when using a multi-valued parameter since the out-of-box instructions for using multi-value parameters is to filter the data AFTER the data has been returned to Reporting Services.

I hope you are already doing the above and that this is not a relevant post. :)

这篇关于有哪些通用方法可以使Reporting Services更快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！