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.

Answer 1

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”).