Solved

Reporting Service - Increase report speed.

Posted on 2009-06-28
8
549 Views
Last Modified: 2012-08-13
Hi experts,
I'm new in reporting service.
I have built a report to render 100,000 data from database by using reporting server. It took around 3min to execute. Is that normal? Any thing i can do to increase the speed?

Thanks.

0
Comment
Question by:mkdev2009
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 300 total points
ID: 24734108
If you are returning and rendering 100,000 rows in the report, it will take a bit to generate but 3 min is excessive.  The time is a combination of how long your query takes to run and retrun the data and how many data elements you are putting on the report and what you do with them.  You say 100K rows, but how many columns per row and what are the datatypes?  It could be the voulume/type of data you are returning.  

I just did a test with 68K+ rows with 28 columns of numbers, dates and a few varchar fields and it generated in < 30 seconds.  I did not do anything fancy, just displayed the values in a table format.  The query for this returns in Management Studio in 3 seconds.

My first suggestion would be to evaluate your query or stored procedure that you are getting the data from.  Test it and analyse it from Management Studio to get it as fast as possible, that is usually the problem.  Other than that it will depend on what you are doing in the report that could be making it slow.
0
 
LVL 16

Assisted Solution

by:Auric1983
Auric1983 earned 100 total points
ID: 24735053
One thing to note too, if you are joining on fields that are not indexed it will greatly increase the time it takes to execute the query regardless of what tool you are running it in.
0
 

Author Comment

by:mkdev2009
ID: 24742987
HI CGLuttrell, Auric1983,
Thanks for the reply.
beside the sql query, any thing we can do on the reporting server to increase the render speed?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 300 total points
ID: 24743021
Other than tuning the query (which is still usually the best place to start) then it depends on what you are doing in the Report, (Groups, Filters, Ordering, special features like allowing dynamic column ordering) these all take extra time to render.  The only other thing then is more machine (Memory, Memory, Memory and a fast cpu)  to run it on.
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24748142
mkdev, one thing to check on the query side of things.

If you are returning all the columns from a table (select * from) perhaps a giving the query only the list of columns you want will speed things up?
0
 

Author Comment

by:mkdev2009
ID: 24750974
I got use View in my sql query, did view going to slow down the speed?
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24751087
Too many views and joining views can be slow.
0
 
LVL 1

Assisted Solution

by:dotnetchick
dotnetchick earned 100 total points
ID: 24874759
You can cache the report so that it renders from the cached version instead of re-rendering everything each time the report is run.  This isn't ideal if you have data that changes very frequently, but you can schedule the report to re-cache on a monthly, weekly, daily or hourly schedule.  The report will still take 3 mins to run, but only when it gets cached.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…

736 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question