Solved

Reporting Service - Increase report speed.

Posted on 2009-06-28
8
547 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

776 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