SSRS runs report for 30 sec however it takes only 3 sec in SQL Studio...

Question

SSRS report calls sproc from SQL 2008 and it takes 30 sec to render it.

If I call the same sproc with the same params it takes 3 sec to finish.

SQL server and SSRS are on the same box.

What can I do about it?

Answer 1

Are you on 2008 R2? If so, make sure you apply CU3, R2 was very buggy.

Answer 2

I've noticed a difference between using a data set that is set to call a stored procedure versus a data set that is set to use a text SQL query but all the text does is call the same SP.

EXEC custom_sp_name_here

In what I've seen, the test SQL query calling the SP performs much better than setting the data set to be a stored procedure.

Jamie F

Answer 3

Sorry this isnt a comment, but thats not an option yet.

How many records are you returning? Have you looked in the report server execution table to see the times it is taking for data retreival vs rendering?

If the report has a large number of pages returned is the report streaming ie.. 1 of 2? or is it returning all the pages before rendering the first?

Does the report take that amount of time every time you call it or is it only the first time, basically once the plan is cached it is running quick? Could be complilation problems.

EDIT: Run this script against your report server DB. It will give you alot more information than you were looking for but, if you order by the report name you can view the data retrieval times, vs. processing time or process and render time. This will tell you where the time is actually being taken. Also your report server will be saving the last 60 days by default so if your only wanting say yesterday then uncomment the date between in the where clause.

declare 
@ReportPath varchar(200)
,@DayCount int

set @ReportPath = 'ALL'
set @DayCount = -1 * @DayCount

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]
    ,round(datediff(ss,el.TimeStart, el.TimeEnd)/60,0,1) DurationMin
    ,case 
        when datediff(ss,el.TimeStart, el.TimeEnd) > 59 
            then datediff(ss,el.TimeStart, el.TimeEnd) % 60 
        else datediff(ss,el.TimeStart, el.TimeEnd)
    end as DurationSec
    ,case 
        when dt_el2.AvgDuration60Day > 59
            then cast(round(dt_el2.AvgDuration60Day / 60,0,1) as varchar(20)) + ' min ' +  cast((dt_el2.avgduration60day % 60) as varchar(20)) + ' sec' 
        else cast(dt_el2.AvgDuration60Day as varchar(20)) + ' sec'
    end as AvgDuration60Day
    ,case 
        when dt_el2.TotalDuration60Day > 59
            then cast(round(dt_el2.TotalDuration60Day / 60,0,1) as varchar(20)) + ' min ' +  cast((dt_el2.TotalDuration60Day % 60) as varchar(20)) + ' sec' 
        else cast(dt_el2.TotalDuration60Day as varchar(20)) + ' sec'
    end as TotalDuration60Day
    ,case 
        when dt_el2.MinDuration60Day > 59
            then cast(round(dt_el2.MinDuration60Day / 60,0,1) as varchar(20)) + ' min ' +  cast((dt_el2.MinDuration60Day % 60) as varchar(20)) + ' sec' 
        else cast(dt_el2.MinDuration60Day as varchar(20)) + ' sec'
    end as MinDuration60Day
    ,case 
        when dt_el2.MaxDuration60Day > 59
            then cast(round(dt_el2.MaxDuration60Day / 60,0,1) as varchar(20)) + ' min ' +  cast((dt_el2.MaxDuration60Day % 60) as varchar(20)) + ' sec' 
        else cast(dt_el2.MaxDuration60Day as varchar(20)) + ' sec'
    end as MaxDuration60Day
    ,dt_el2.Count60Day
    ,(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
    join(
            select
                reportpath
                ,sum(datediff(ss,timestart,timeend)) as TotalDuration60Day
                ,max(datediff(ss,timestart,timeend)) as MaxDuration60Day
                ,min(datediff(ss,timestart,timeend)) as MinDuration60Day
                ,avg(datediff(ss,timestart,timeend)) as AvgDuration60Day
                ,count(*) as Count60Day
                --,count(*) over(partition by username) as UserCount60Day
            from
                executionlog2
            where
                reportaction = 'Render'
                and status = 'rsSuccess'
            group by reportpath
        ) dt_el2 on el.ReportPath = dt_el2.ReportPath


where
    (@reportpath = 'ALL' or el.ReportPath = @reportpath)
    --and el.TimeStart between 
        --convert(varchar,dateadd(dd,@daycount,getdate()),112) + ' 00:00:00.000' and
        --convert(varchar,getdate(),112) + ' 23:59:59.000'
    and el.ReportPath != 'Unknown' -- exclude reports that have been deleted after executing
    and el.ReportAction = 'Render'

order by durationmin desc, DurationSec desc;