Solved

SQL Server Reporting Services 2005 (Performance)

Posted on 2011-02-25
5
273 Views
Last Modified: 2012-05-11
Greetings:

Please let me know your thoughts about a slowness I am experiencing:

I have a c#.net application using .net sql connection to a sql server 2005 database.   Every 7 seconds the application updates (via query) to display data to the screen from the database. (The database is relatively small (<400mb - recordsets max ~ 60k)

Recently we added a report to sql server 2005 reporting services that calculates information from the same database.  The report is a dashboard that shows about 7 calculated figures.  There are 5 datasets to this report (3 of the datasets grab data from multiple joined views).

When we ran the RS report we would notice that the c# application would halt and not return data every 7 seconds.  We would have to wait for the report to finish which took ~ 30 minutes.
Figuring it was the joining of views and multiple datasets behaving so slow we ran the same report directly against the database through enterprise manager and the results were drastically different.  We got information back almost instantly without affects other applications attached to the database.  

Next, we made a testdatabase to sit along side the original database; we found out its not a hardware resource issue on the server since we could run the RS report against the testdatabase and not interrupt the original database or c# applicatoin attaching to it.  RS still took way too long to compile and display the report.

We figured it could be the joining of views and indexed the views; this did help the RS report to run ~ 20% faster - but still takes a lot more time to compile in RS compared to running the queries directly in enterprise.  

There are many reports that have been running on the same report server for a few years along side the c# applicatoin that shows new data every 7 seconds without interrupts.  These reports are also crunching some pretty complex reports - but very quickly (a few seconds).  The big difference with the new report is it having 5 datasets and inside 3 of those datasets it is running queries that join multiple views. (Please note: I read online that joining views can degrade performance in sql server - but in this case it appears that it is a little bit more than should be expected since the joining of views in a direct query to the database through enterprise pulls back data relatively quickly).

I am starting to think it is RS and its ability to handle the complex structure of the dashboard?

*We also ran the dashboard through access against the original database (same database c# app connects to) and it compiled the report in seconds. (Did NOT affect the c# application at all during those few seconds)

Please let me know your thoughts.
0
Comment
Question by:ChaS3me
5 Comments
 
LVL 17

Assisted Solution

by:MIKE
MIKE earned 250 total points
ID: 34987922
Try turning the VIEWS into tables and/or change the datasets into Stored Procs..and see how performance is increased? You should see an increase.

do yo "really" need the figures updated every 7 seconds? Is this a "Call Center" report of something where you are hitting a "live" transaction system?
0
 
LVL 2

Accepted Solution

by:
KentMarsh earned 250 total points
ID: 34997994
A good first step for you would be to use the SQL Server analysis tool that shows you the query plan.
The query plan that for your "5 datasets and inside 3 of those datasets it is running queries that join multiple views" will likely have one or more areas that could be optimized.
Optimizations may include:
Add an index or two
Use a query rather than joining views.
Denormalize some data to avoid joins during reports.
Consider how the Database is caching data and play to that strength. (e.g. Avoid Table scans.)
Create a Model-Driven SSRS Report rather than a detailed report.  The model driven report will only select data as needed, so you can present to the users some high level data that could retrieve fairly quickly, then the users can choose if they want to drill down to the detail. Thus, your constant retrieval involves far less data.
Consider using a DataWarehouse as the source of your timely reports. The database can update the DataWarehouse tables with triggers or through some other means. The data in the DataWarehouse tables is denormalized and designed for fast, read-only processing.
0
 
LVL 3

Author Comment

by:ChaS3me
ID: 35020210
Thanks for the comments - when I get back to this report I am certain some of the suggestions will help to imrpove performance - just going to leave open for a few more days to see if any other thoughts come in.

Really interested why the report compiles and runs faster in access than in SSRS...
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35275403
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CROSS APPLY 4 43
Strange msg in the SSMS pane 13 48
Problem to error 4 43
My Query is not giving correct result. Please help 5 27
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

919 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now