Troubleshooting SSRS 2005 report that runs slow

Question

I have a report in SSRS 2005, with an embedded SQL query (no stored procedure). This report is running slower in SSRS than the query will run in SSMS. I'm wondering how to troubleshoot this, and if there are any differences in the way query optimization works for a report with embedded SQL code vs. a stored procedure.

Thanks!

Answer 1

There can be differences between running a query from SSMS and a report, but SQL Server query optimization is not it. In other words, SQL Server doesn't care where the query comes from.

First, you should use SQL Profiler to capture the query when run from both sources. You can see if the real performance difference is due to time on the SQL Server and not elsewhere. Also, with the results you can see if the queries are identical. SSRS probably uses a parameterized query and it's actually different than what you're running in SSMS.

Is there a large amount of data that is returned to the report? You may see timing differences because the results return faster to SSMS.

Answer 2

Both the stored proc and the query thru ssrs will get parameterized, so they should be cached the same way although they will be 2 different caches. If I were you I would start with examining the execution log on the report server database to find where your issue is coming from.

Here is a script that will show you the times its requiring to get the data, process, and render.

select
       reverse ( substring ( reverse ( el . ReportPath ), 1 , charindex ( '/' , reverse ( el . ReportPath ))- 1 )) as ReportName
       , u . UserName as LastModBy
       , coalesce ( cast ( el . parameters as varchar ( max )), '' ) as [Parameters]
       ,( select count (*) from executionlog2 tmp where tmp . reportpath = el . reportpath and tmp . username = el . username and tmp . reportaction = 'Render' and tmp . status = 'rsSuccess' group by tmp . ReportPath ) as UserCount60Day
       , el . Format
       , el . UserName
       , el . ReportAction
       , el . Status
       , el .Source
       , el . [RowCount]
       , el . ExecutionId
       , el . TimeDataRetrieval / 1000 as DataRetrieval
       , el . TimeProcessing / 1000 as Processing
       , el . TimeRendering / 1000 as Rendering
       ,( el . TimeProcessing + el . TimeRendering ) / 1000 as ProcessAndRender
       , el . AdditionalInfo
       , case
             when datediff ( ss , el . TimeStart , el . TimeEnd ) >= 30
                   then 1
             else 2
       end as DisplayInRed

from
      ExecutionLog2 el
       join ReportServer . dbo . Catalog c  
             on c . Path = el . ReportPath
       join ReportServer . dbo . Users u  
             on u . UserId = c . ModifiedByID

where
      el . ReportAction = 'Render'

Also keep profiler running while you execute the report so you can see whats going on behind the scenes.

Hope it helps.