Reporting Services Performance

Hi,

I'm just wondering if anyone else has had any problems with reporting services when generating reports.

I have a rather large report (approx 5000 rows) and to be fair there are alot of links to other tables. However it takes 5-10 minutes to generate this report.

If I manually pull the data from the database, perform the aggregations and all the table joins, it takes under 10 seconds to complete and I seriously doubt that rendering the table to put the data in takes 10 minutes to do.

Any Ideas?An update on this,

I just tried creating a report without all the information in the other tables, so the report is now taking 5128 rows from one table and displaying all the columns. This took 5 minutes to generate.

Again any Ideas on why this is taking so long?|||

Do all significant data processing on the server, particularly joins/unions/selections/projections.

Limit the report engine to data processing that's easy for it to do without additional roundtrips to the server, such as running totals, grouping, subtotaling, etc.

I'll bet your 5 minute report will become instantaneous by pushing the data processing to the server, where it is best handled. Views and stored procedures are your friends.

This approach also reduces the dependency on the report writer, so that you can use other report engines to render the same report data regardless of the capabilities and quirks of their client-side data engines.

|||

I have the following task to investigate: which server will need more resources at the time of reports execution using Reporting Services 2005

In a standard deployment you will have two servers: A. Report Server

B. SQL Sever Database

When using enbedded code / Expression in a report: example: Sum, Count, etc. Who will execute this action? It is the Report Server or the SQL Server?

The main idea is to determine who will need more resources in order to have the best performance in the reports execution.

Thanks

Reporting Services Performance

Hi,

I'm just wondering if anyone else has had any problems with reporting services when generating reports.

I have a rather large report (approx 5000 rows) and to be fair there are alot of links to other tables. However it takes 5-10 minutes to generate this report.

If I manually pull the data from the database, perform the aggregations and all the table joins, it takes under 10 seconds to complete and I seriously doubt that rendering the table to put the data in takes 10 minutes to do.

Any Ideas?An update on this,

I just tried creating a report without all the information in the other tables, so the report is now taking 5128 rows from one table and displaying all the columns. This took 5 minutes to generate.

Again any Ideas on why this is taking so long?|||

Do all significant data processing on the server, particularly joins/unions/selections/projections.

Limit the report engine to data processing that's easy for it to do without additional roundtrips to the server, such as running totals, grouping, subtotaling, etc.

I'll bet your 5 minute report will become instantaneous by pushing the data processing to the server, where it is best handled. Views and stored procedures are your friends.

This approach also reduces the dependency on the report writer, so that you can use other report engines to render the same report data regardless of the capabilities and quirks of their client-side data engines.

|||

I have the following task to investigate: which server will need more resources at the time of reports execution using Reporting Services 2005

In a standard deployment you will have two servers: A. Report Server

B. SQL Sever Database

When using enbedded code / Expression in a report: example: Sum, Count, etc. Who will execute this action? It is the Report Server or the SQL Server?

The main idea is to determine who will need more resources in order to have the best performance in the reports execution.

Thanks

Reporting services fail to return large report

I have a huge report (approx. 120MB) needs to be returned from reporting
services. However, most of the time the request die before the report is
returned.
While I am debugging this problem, I realize that an exception is thrown
between the BeginRender() and EndRender() methods of Reporting Service.
Exception:
Unable to read data from the transport connection.
I am not sure if there is any limit in report size that can be
generated/transported from reporting service. Reporting service is called
from another ASP.Net web service. This web service also resides in the same
box as reporting service and SQL server 2000. This box is running Win 2003
and IIS 6(IIS 5 isolation mode).
Here is the code snippet:
----
ReportingService oRS = CreateReportService(sRSession);
IAsyncResult pWait = oRS.BeginRender(m_sReport, sFormatName, null,
sFormatInfo, aParams, null, null, null, null);
if (pWait.AsyncWaitHandle.WaitOne(new TimeSpan(ReportGenTimeout(), 0, 0),
false))
{
byte [] abResult = oRS.EndRender(pWait, out sEncoding, out sMime, out
aUsedParams, out aWarnings, out asStreams);
}
else
{
oRS.Abort();
throw new ApplicationException("Report Generation Timeout Expired");
}
Any help will be greatly appreciated. Thanks.
JocelynHave you tried the report from Report Manager to determine if it is an
integration issue or running a report issue?
What format are you rendering it? If it is not html then instead of report
manager test it using URL so you can specify the render type.
My guess is that it is too big. RS is not designed for this sort of thing. A
lot of rendering goes on in memory so something this large could be
problematic which I why I suggested the above tests.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jocelyn Duhaylungsod" <JocelynDuhaylungsod@.discussions.microsoft.com> wrote
in message news:E26E3D99-D157-4A9B-A164-99F75119969D@.microsoft.com...
> I have a huge report (approx. 120MB) needs to be returned from reporting
> services. However, most of the time the request die before the report is
> returned.
> While I am debugging this problem, I realize that an exception is thrown
> between the BeginRender() and EndRender() methods of Reporting Service.
> Exception:
> Unable to read data from the transport connection.
> I am not sure if there is any limit in report size that can be
> generated/transported from reporting service. Reporting service is called
> from another ASP.Net web service. This web service also resides in the
same
> box as reporting service and SQL server 2000. This box is running Win
2003
> and IIS 6(IIS 5 isolation mode).
>
> Here is the code snippet:
> ----
> ReportingService oRS = CreateReportService(sRSession);
> IAsyncResult pWait = oRS.BeginRender(m_sReport, sFormatName, null,
> sFormatInfo, aParams, null, null, null, null);
> if (pWait.AsyncWaitHandle.WaitOne(new TimeSpan(ReportGenTimeout(), 0, 0),
> false))
> {
> byte [] abResult = oRS.EndRender(pWait, out sEncoding, out sMime, out
> aUsedParams, out aWarnings, out asStreams);
> }
> else
> {
> oRS.Abort();
> throw new ApplicationException("Report Generation Timeout Expired");
> }
>
> Any help will be greatly appreciated. Thanks.
> Jocelyn|||A little over 100MB report was able to be generated and viewed from report
manager. The report is in html format.
All the smaller reports are able to be generated with the same codes. I am
not sure what kind of intergration issue may cause the huge report failed.|||That does tell you something (that it works from Report Manager). It could
be a timing out issue with IIS. Could be something different when using web
services that isn't there when using it from Report Manager.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jocelyn Duhaylungsod" <JocelynDuhaylungsod@.discussions.microsoft.com> wrote
in message news:163991C3-AD80-4E2A-85FF-7C6443278558@.microsoft.com...
> A little over 100MB report was able to be generated and viewed from report
> manager. The report is in html format.
> All the smaller reports are able to be generated with the same codes. I
am
> not sure what kind of intergration issue may cause the huge report failed.
>|||It takes about 3-4 minutes before the request dies.