SSRS运行报告30秒,但在sqlstudio中只需要3秒

你要还多少张唱片?您是否查看了报表服务器执行表以查看数据检索与呈现所用的时间?

报表是每次调用时都要花费那么多时间,还是仅仅是第一次调用,基本上一旦缓存了计划,它就会快速运行?可能是礼包问题。

编辑: 对报表服务器数据库运行此脚本。它将为您提供比您所寻找的更多的信息,但是,如果您按报表名称排序,您可以查看数据检索时间与处理时间或处理和呈现时间的对比。这会告诉你时间到底花在哪里。此外,您的报表服务器将保存默认的最后60天,所以如果您只想说昨天,然后取消注释之间的日期在何处子句。

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;

在SSRS中设置报表属性“InteractiveSize”迫使报表对返回的数据分页,而不是试图在一个页面中呈现所有内容。

这将呈现报表所需的时间减少到3秒。

~z~不多

EXEC custom_sp_name_here

在我看到的情况下,调用SP的testsql查询的性能要比将数据集设置为存储过程要好得多。