Solved

Reporting Service - Increase report speed.

Posted on 2009-06-28
8
548 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
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.

820 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