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.

performance reporting-services ssrs-2008
Edit
Report