CODEWITHSUNDEEP
sql-serverssrs-2008-r2ssrs-tablix
Dhaval Shukla
Dhaval Shukla

Reputation: 1127

Performance Issue in SSRS - Multiple Dataset

I have one complex report which fetches records from multiple tables.

I saw at many places that SSRS does not allow multiple data tables returned from single stored procedure, that is the reason I created one stored procedure and I created six dataset for report that was filtered from shared dataset, but when I ran below query it shows that my procedure was executed for six times and that might causing the performance issue.

SELECT TOP 100 *,Itempath,parameters,
     TimeDataRetrieval + TimeProcessing + TimeRendering as [total time],
     TimeDataRetrieval, TimeProcessing, TimeRendering,
     ByteCount, [RowCount],Source, AdditionalInfo
FROM ExecutionLog3 where ItemPath like '%GetServiceCalls%'
ORDER BY Timestart DESC

To get rid of this, I removed all dataset filters and applied filter on tablix. After that I can see that procedure was called only one time. But that does not affect the performance much.

Now, question that is coming to me is how exactly I can improve performance of SSRS report.

Note: My Query executes in 13 seconds of time and report takes almost 20 mins to execute.

Please help me to resolve this issue.

Regards,

Dhaval

Upvotes: 0

Views: 743

Answers (1)

Bill
Bill

Reputation: 176

I always found that SSRS filters on large tables to take forever and that any text wildcards performed even more poorly.

My advise would be to do all the "grunt work" except sorts in SQL and then do any sorts in SSRS.

Part of you problem may be that you have a large dataset and you are performing wildcard searches which don't play well with Indexes when you have the wildcard at the start of the like statement (e.g. like '%... ).

Upvotes: 3

Related Questions