CODEWITHSUNDEEP
performancereporting-servicesssrs-2008
Ahd
Ahd

Reputation: 281

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

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:

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.

Upvotes: 16

Views: 49103

Answers (3)

Alexey Sukhanov
Alexey Sukhanov

Reputation: 399

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”). Report Properties pane

Upvotes: 3

Ahd
Ahd

Reputation: 281

I think I may have found my answer on a social.msdn.microsoft.com thread http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/1e34dc76-4d78-4376-89b0-7c381ed82726/

I unchecked the "Keep together" setting and it now actually previews coming from the server and I can export the report. Much improvement

Upvotes: 0

Frank Pearson
Frank Pearson

Reputation: 872

I was troubleshooting a similar issue a while ago and it turned out to be related to the browser I was using to view the report. If the report returns a lot of data to the screen consider splitting it into multiple pages if it is not already configured that way.

Why Does Business Intelligence Studio Load Reports Faster than the Report Server

Upvotes: 0

Related Questions