So we are in the final testing stages of our SSRS reporting
interface in a Java web front-end.
The results are really fantastic! However, going thru this
process has taught me a lot about how SSRS works internally, things that I took
for granted when using report manager to render reports. These things will
probably help my report design in the future, so I thought I would write them
down and pay it forward!
First, a bit about how we implemented this:
In our reports, we force exact pagination by having a
caption at the top of the report that reads:
This report contains xxx pages.
Since the report wants this number, SSRS will go ahead and process the
report to calculate the page count. If
we had left this out, SSRS would render the first page and not worry about how
many subsequent pages were coming.
In the front end, we render page 1 of the report, get the
total page count, and then display the first page in a viewer similar to report
manager. Note that we are NOT using the report viewer control since this is a
Java application. We render all pages to HTML4.0 and display as is on the web
page. Since SSRS embeds all of the formatting, the front end is responsible for
none of that.
When the user clicks to go to the next page, we again call
render with the page number in the device info XML.
In parallel, I created a small C# app that calls a report
and renders it much the same way. I did
this so I could make sure I fully understood the process and could assist the
Java developers.
We ran into issues when our reports took much longer to
render in our front end than it did in report manager or my C# app. So I started watching stats using the
ExecutionLog3 view on the report database.
I paid special attention to TimeDataRetrieval, TimeProcessing, and
TimeRendering.
From my app I was seeing the data retrieval occurring on the
first render, and some time processing and then some time rendering. These numbers were all pretty much in-line
with what I would have expected. For a 110 page report, I had 3000-3500ms for
data, 750-1000ms for processing, and 1000-1500 for rendering. On this first
page, the Source field was Live.
On moving to the next page though, the Source field was
Session, I was seeing no data retrieval at all, and minimal processing and
rendering: 0ms for data, 35-40ms for processing, and 100ms for rendering. Very
acceptable numbers for a reporting platform! So this tells me that SSRS
processes the dataset all at once, does a bit of pre-rendering to get the page
count, and fully renders the first page.
From our front end however, I saw something completely different!
Every request had a Source of Live, and each request was pulling data and
rendering, sometimes taking up to 15-20 seconds to run the report.
I put WireShark on the SSRS server and started watching network
packets. I was able to watch every command that came into the SSRS server, and
the response from the server. It quickly
became apparent what was happening. The
front end was not sending the page number for the first Render call, and then calling
SetExecutionParameters between each page. The first Render call was causing the
server to fully render the report for the first page. The
SetExecutionParameters call was invalidating the session and causing the report
to be full re-rendered, including data retrieval, for each page. Obviously this
was the cause behind the huge difference in execution times!
So I worked with the Java developer and was able to remove
any extraneous SSRS calls, and now the performance is amazing!
Our report template it pretty complex, we have a lot of
background features that require processing, so to have execution times in the
low 100ms range is great!
Speaking of our report template, we have a parameter called
Date Range. A user can select a value in
this drop down and select ranges such as last month, last year, etc. When the
user selects one of these, we call the SQL Server to return a start and end
date based on the selection. In report manager, once you select one date range,
and then try to select a second, the start and end dates will not update. This is a “feature” according to Microsoft.
Since we wrote our own front end, we were able to work around this. So you can
select Last 2 Years and the start and end dates will populate, and then you can
change to rolling 13 months and the start and end dates will update
accordingly!
That’s all for now… I welcome any and all questions…
peace
