Reporting Service - Increase report speed.

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.

mkdev2009Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris LuttrellSenior Database ArchitectCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Auric1983Commented:
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
mkdev2009Author Commented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Chris LuttrellSenior Database ArchitectCommented:
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
Auric1983Commented:
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
mkdev2009Author Commented:
I got use View in my sql query, did view going to slow down the speed?
0
Chris LuttrellSenior Database ArchitectCommented:
Too many views and joining views can be slow.
0
dotnetchickCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.