Logo 
Search:

MOSS Forum

Ask Question   UnAnswered
Home » Forum » MOSS       RSS Feeds

SSRS performance issue on MOSS 2007

  Asked By: Darren    Date: Oct 11    Category: MOSS    Views: 826

Our company is using SQL 2005 Reporting Services (SSRS) to display a
dashboard with some useful statistics and fancy bar charts on the
company intranet. The dashboard uses report viewer webparts with the
SSRS source files (.rdl I think) hosted in reports center report library
of a moss site. The problem is that the ReportViewer takes too long to
bring up results on the MOSS-based site. The SQL works efficiently from
Query Analyzer (takes around 1 sec) but the report using that query on
MOSS almost 40 seconds (!!!!) to come back with results on the MOSS
site.

We have tried to identify the cause of delay without any luck so far.
The Server has sufficient memory / other hardware specs so that does not
seem to be the bottleneck. I wonder if there are any known performance
issues with using SSRS over MOSS? Is there any better alternative?

Any suggestions, tips, clues, articles, blogs to help optimize the
reports would be highly appreciated.

Share: 

 

8 Answers Found

 
Answer #1    Answered By: Himanshu Gohil     Answered On: Oct 11

How many rows are in the result set? You may get the result set back on
the SQL Server very quickly, but if the bar charts are constructed using
tens of thousands of rows, they won't be generated until the entire
result set has been generated and processed. If SSRS is on the same
server as the database, then transporting the result set over the
network will not be an issue. If the MOSS-based site has to pull the
records over the network and then generate the charts, then you'll be
waiting for all the rows to traverse the network first.

 
Answer #2    Answered By: Ashton Schroeder     Answered On: Oct 11

The table that stored procedure pulls data from is large ... like
300,000 records. Though this is just a test environment, production is
expected to have around 10 million records in this table!

Also, sql server and SSRS are both installed on the same server as MOSS.
The machine itself is a virtual machine with 3 GB RAM. The host machine
is 3.0 Ghz dual xeon with 8 GB RAM. I think this should be ok for the
test environment with just 300,000 records which is definitely not huge.

Now, the result set actually returning from the stored procedure is not
huge either. Right now in test environment, result set has anywhere
between 10 to 100 records. The stored procedure itself is very fast as
its returning result set within 2 seconds from the query analyzer.

 
Answer #3    Answered By: Iris Ballard     Answered On: Oct 11

For large reports, SSRS has the ability to "schedule"
reports at certain times and then view the results of
this generated report.

Is this a feasible solution?

 
Answer #4    Answered By: Jamila Guthrie     Answered On: Oct 11

Ok, it sounds like you've eliminated SQL and network transport as the
issue.

The only other idea I have is the IIS worker process. If you generate
the same report twice in a row, does the second time run without delay?
If the delay is only on the first access, it may just be w3wp.exe waking
up...

 
Answer #5    Answered By: Kalpana Ghatge     Answered On: Oct 11

Nope, the report takes the similar amount of time everytime it is
pulled, around 30 - 35 seconds after clicking on "generate report"
button.

Someone suggested me to look at the logs of SSRS by importing them in a
sql database and analyzing the bottleneck. It turned out that it's the
"Rendering Time" which is becoming the bottleneck. Interestingly it was
found to be uneven and we were not able to find a linear or even
exponential relationship between the rendering time and the row count.

All of this makes me wonder if SSRS is really an "enterprise level"
reporting tool or is it us missing something ? is there an article or
best practice for using SSRS? How can we optimize the rendering time?

 
Answer #6    Answered By: Bobbie Rodgers     Answered On: Oct 11

That's a good question. Our implementation pulls data from a 4-year old
dual-core box over a 1 GB network to a VM reporting services box and
displays fancy charts in SSRS (not through MOSS) from a 70,000 row
database in about 5 seconds. We haven't tried connecting it through MOSS
yet.

 
Answer #7    Answered By: Bhumi Gokhale     Answered On: Oct 11

Maybe we really are missing something. Can you please also let me know
if you made any configuration changes to achieve this level of
performance? Any insight into how you were able to get to this or the
specifics of your reports could really give me some clues.

 
Answer #8    Answered By: Davon Henson     Answered On: Oct 11

Ssrs web does take ages to access normally I've noticed

Haven't tried adding it to warm up scripts but that may help

 
Didn't find what you were looking for? Find more on SSRS performance issue on MOSS 2007 Or get search suggestion and latest updates.




Tagged: