SSRS performance issue

Question

I have a report which will fetch 8 lakh records. I dont have any groups or complex expressions. The report has a simple table which will get the data from a dataset.

The stored proc being used for retrieving the dataset starts giving the records in 2 secs and query completes in 6 mins.

I am expecting the records to be displayed in the report using dynamic paging . But the report is not displaying immediately (in 2secs).

I do not have any reference to Globals!TotalPages also.

Please suggest.

Answer 1

Hi,

Try to set cache option http://msdn.microsoft.com/en-us/library/ms155927(v=sql.105).aspx

Zdenek

---

Please mark as helpful and propose as answer if you find this as correct. nosekz.eu

Answer 2

Please explain what the query does? Why it takes so long? Do you have useful indexes on the table?

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 3

The query has to fetch the information related to all the members in an organization. The member personal details and their contribution amounts.

The problem is the report is pointed to OLTP db and not OLAP. so lot of normalization is there in d existing OLTP db.

but the report has fields which have to retrieved from so many tables using some left joins. and for getting the contribution details related to members, i need to go for Rank/Row_Number as I have to get the latest contribution dates.

Answer 4

Hi,

how often is data updated or inserted?

If it is not very often, you can set cache option. This means that report store query result to ReportServerTempDB and every report render will take this cached session instead of querying data again and again.

Zdenek

---

Please mark as helpful and propose as answer if you find this as correct. nosekz.eu

Answer 5

This is a stored proc performance issue rather than an SSRS issue.  Can you tune your SP at all?  Run the execution plan and create indexes to improve performance?

Answer 6

Even though the query execution takes time for completion... the rows starts getting feteched from 2 secs

then i expect the rdl to display the first set of records using dynamic paging..

but that is not happening y?

Answer 7

This links to a similar question asked before and what you are expecting to happen is not how SSRS works by default for a single table of results.

http://forums.asp.net/t/1667056.aspx/1?On+Demand+Report+Processing