Query runs fast but report renders slow: how to debug this?

Question

I'm running SSRS 08. My query has four parameters (all varchars). I can run the query through management studio and it runs in under 10sec. I get the same performance if I run the query in Visual Studio's query designer.

However...if I try to preview the report or actually run the report once it's deployed the actual report sometimes takes more than 5 minutes. I ran a trace and the query seems to be in/out of the SQL server side quickly. How can I find where/why I'm loosing this performance? I've tried/checked so far the following:

No images are referenced in the report
No grouping/sorting outside of the stored procedure
Since it's a straight forward report I rebuilt it with same result (to check if it's not an issue of a corrupted report).
ran SQL trace when I executed the report to make sure the query did not have issues

Based on some suggestions before I rewrote the query to use variables instead of parameters like so

    -- ...
    -- Note: @Parameter is a varchar(40) 
    -- ...
    declare @Var as varchar(40) 
    set @var=@parameter

    select * from table where fieldvalue=@var

Ran the trace and it's not an issue on the query side.

You can run the query below to find out where is the overhead: SELECT Name, TimeDataRetrieval,TimeProcessing,TimeRendering , TimeDataRetrieval+TimeProcessing+TimeRendering AS TotalTime,Format,Parameters,username,TimeStart FROM dbo.ExecutionLog L WITH (NOLOCK) INNER JOIN dbo.Catalog R WITH (NOLOCK) ON L.ReportID = R.ItemID WHERE Name='' -- update parameters for report name AND Format>'' ORDER BY Name The times are for query data retrieval, report processing and rendering. If all measures are normal then the problem is somewhere else, not reporting services — niktrs

Alexey Sukhanov Alexey Sukhanov · Accepted Answer · 2016-06-01T14:07:30

I had the report html output trouble on report retrieving 32000 lines. In my case I had to activate “Interactive Paging” to allow user see first page and able to generate Excel file. The pro is that first page appears fast and user can generate export to Excel or PDF, the cons is that user can scroll only current page. If user wants to see more content he\she must use navigation buttons above the grid. In my case user accepted this behavior because the export to Excel was more important.

To activate “Interactive Paging” you must click on the free area in the report pane and change property “InteractiveSize”\ “Height” on the report level in Properties pane. Set this property to different from 0. I set to 8.5 inches in my case. Also ensure that you unchecked “Keep together on one page if possible” property on the Tablix level (right click on the Tablix, then “Tablix Properties”, then “General”\ “Page Break Options”).

Query runs fast but report renders slow: how to debug this?

3 Answers