SSRS优化-sqlserver2008R2中单独执行存储过程很快，但在SSRS报表中执行缓慢

这个问题困扰了我两天，最后各种尝试解决了，现在写出来，如果大家有这样的情况，试试我的方法，少走弯路

查看对应报表中执行时间，发现大部分执行时间是在 字段 TimeDataRetrieval 上

SELECT  C.Name                         AS ReportName

       ,E.ReportID                     AS ReportID

       ,E.UserName                     AS UserName

       ,E.Format                       AS Format

       ,E.Parameters                   AS Parameters

       ,E.TimeStart                    AS TimeStart

       ,E.TimeEnd                      AS TimeEnd

       ,E.TimeDataRetrieval*1.0/1000   AS TimeDataRetrieval

       ,E.TimeProcessing*1.0/1000      AS TimeProcessing

       ,E.TimeRendering*1.0/1000       AS TimeRendering

       ,DATEDIFF(SECOND, TimeStart, TimeEnd)

                                       AS  CostTime

FROM ReportServer.dbo.ExecutionLog E WITH(NOLOCK)

INNER JOIN ReportServer.dbo.Catalog C WITH(NOLOCK)ON E.ReportID = C.ItemID

WHERE C.Name ='TradeGeneralCommodity'

   AND E.TimeStart > CAST('2016-04-11 00:00:00' AS DATETIME)

  AND E.TimeStart <= CAST('2016-04-11 23:59:59' AS DATETIME)

ORDER BY TimeStart DESC

TimeStart	TimeEnd	TimeDataRetrieval	TimeProcessing	TimeRendering	CostTime

2016-04-11 13:54:54.857	2016-04-11 14:25:48.357	1853.333000	0.023000	0.000000	1854

2016-04-11 18:54:21.843	2016-04-11 19:25:11.823	1849.914000	0.018000	0.000000	1850

2016-04-11 13:51:05.190	2016-04-11 14:21:48.413	1843.020000	0.036000	0.000000	1843

2016-04-11 13:57:44.603	2016-04-11 14:27:48.367	1803.641000	0.021000	0.000000	1804

2016-04-11 09:51:01.037	2016-04-11 09:51:28.957	27.128000	0.345000	0.413000	27

2016-04-11 13:17:09.913	2016-04-11 13:17:31.927	21.166000	0.473000	0.326000	22

2016-04-11 10:26:28.457	2016-04-11 10:26:45.540	16.204000	0.492000	0.353000	17

接下来，通过SQL Server Profiler 抓执行报表的sql

比如这个过程有5步的执行sql，发现到第3步的时候卡住了

当我把这个卡主的sql单独执行的时候2s中就出结果，然后我在想，既然这个存储过程整体执行不慢，加载到SSRS中执行很慢，原来这个存储过程结构有修改调整，

那么可能就是这个存储过程没有安装最新的执行计划来执行

，重新编译存储过程 ：exec sp_recompile @objname='procedureName'  ，编译后 果然在报表中查询的速度和单独执行存储过程一样，问题解决了

解决方法2：在存储过程中添加 重新编译的关键代码： with recompile

CREATE PROCEDURE procedureName

    @starttime DATETIME ,

    @endtime DATETIME

    WITH RECOMPILE

AS 

    BEGIN

        SET NOCOUNT ON; 

        

        SELECT  GETDATE()

    END