Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Reporting Service - Increase report speed.

Posted on 2009-06-28
8
Medium Priority
?
552 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 27

Accepted Solution

by:
Chris Luttrell earned 1200 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 400 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 1200 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 27

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 400 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

609 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