SSRS报告在10分钟后超时

Question

我有一份报告有一个非常慢的数据集。不幸的是，目前我们无法进一步优化查询。然而，无论什么超时，我更改报告仍然在10分钟后超时。我已经尝试过报表数据集超时、报表执行超时以及sql服务器上的远程执行。似乎都没起作用。

经过一番挖掘，我得到了以下资源，但我也没有用：

https://blogs.msdn.microsoft.com/selvar/2010/07/12/report-builder-2-0-3-0-errors-out-with-the-operation-has-timed-out-when-previewing-a-report-saved-in-report-server-takes-more-than-10-minutes/

https://msdn.microsoft.com/en-us/library/ms155782.aspx

为什么我的报告会在10分钟后超时？

剧本：

DECLARE @SessionDateFrom DATETIME = '2016/11/01'
DECLARE @SessionDateTo DATETIME = '2016/12/01'
DECLARE @SiteNo INT = 1

SELECT DISTINCT [CS].[No] AS SiteNo
    ,[CS].[Name] AS SiteName
    ,TSE.[MediaNo] AS Media
    ,TSE.[MediaIndex]
    ,Sum(TSE.[Qty]) AS SalesQty
    ,Sum(TSE.[Value]) AS SalesValue
    ,TSE.[MediaGroup]
    ,TSE.[MediaName]
    ,TSE.SortOrder
    ,TSE.[ReasonNo]
    ,[R].[Name] AS ReasonName
    ,Convert(BIT, CASE 
            WHEN TSE.[MediaNo] NOT IN (
                    1001
                    ,1002
                    ,1003
                    ,1004
                    ,3002
                    ,3004
                    ,7002
                    ,7004
                    ,7006
                    )
                THEN 1
            ELSE 0
            END) AS Clickable
FROM TSE
LEFT JOIN CS ON CS.No = TSE.SiteNo
LEFT JOIN R ON (R.No = TSE.ReasonNo)
WHERE EXISTS (
            SELECT Descendant
            FROM DescendantSites
            WHERE Parent = @SiteNo
                AND Descendant = TSE.SiteNo
            )
        AND (
            Qty <> 0
            OR Value <> 0
            )
        AND MediaNo <> 0
        AND ExcludeFromReport <> 1
        --AND (
        --  MediaNo IN (@MediaNo)
        --  OR MediaNo IS NULL
        --  )
        AND TermNo = 0
        AND SessionDate BETWEEN @SessionDateFrom
            AND @SessionDateTo
GROUP BY [CS].[No]
    ,[CS].[Name]
    ,TSE.[MediaNo]
    ,TSE.[MediaIndex]
    ,TSE.[MediaGroup]
    ,TSE.[MediaName]
    ,TSE.SortOrder
    ,TSE.[ReasonNo]
    ,[Reason].[Name]
ORDER BY TSE.[MediaGroup]
    ,TSE.[SortOrder]
    ,TSE.[MediaIndex]
OPTION (OPTIMIZE FOR UNKNOWN)

将需要过滤的媒体，但如果它可以返回所有在合理的时间，那么应该是好的(请纠正我，如果那个逻辑是错误的。我对sql很陌生)。

Answer 1

我能够将查询重写为以下内容。它的速度有很大的提高(从大约40秒增加到大约2秒)。

SELECT [CfgSites].[No] AS SiteNo
,[CfgSites].[Name] AS SiteName
,[Media] AS Media
,[MediaIndex]
,Sum([Qty]) AS SalesQty
,Sum([Value]) AS SalesValue
,[MediaGroup]
,[MediaName]
,TSE.SortOrder
,TSE.[ReasonNo]
,[Reason].[Name] AS ReasonName
,Convert(BIT, CASE 
        WHEN TSE.[Media] NOT IN (
                1001
                ,1002
                ,1003
                ,1004
                ,3002
                ,3004
                ,7002
                ,7004
                ,7006
                )
            THEN 1
        ELSE 0
        END) AS Clickable
FROM (
SELECT SessionDate
    ,TransactionSales.SiteNo
    ,Media
    ,MediaIndex
    ,Qty
    ,Value
    ,MediaGroup
    ,FullName as MediaName
    ,SortOrder
    ,ReasonNo
FROM TransactionSales
LEFT JOIN MediaExtended ON TransactionSales.Media = MediaExtended.MediaNo 
AND TransactionSales.MediaIndex = MediaExtended.[Index]
INNER JOIN [Sessions] ON TransactionSales.[SiteNo] = [Sessions].[SiteNo] AND TransactionSales.[TermNo] = [Sessions].[TermNo] AND TransactionSales.SessionNo = [Sessions].[SessionNo] AND 
                     [Sessions].[Type] = 0
WHERE TransactionSales.SiteNo IN (@SiteNo)
    AND (
        Qty <> 0
        OR Value <> 0
        )
    AND Media <> 0
    AND ExcludeFromReport <> 1
    AND (
        MediaNo IN (@MediaNo)
        OR MediaNo IS NULL
        )
    AND TransactionSales.TermNo = 0
    AND SessionDate BETWEEN @SessionDateFrom
        AND @SessionDateTo
) TSE
LEFT JOIN CfgSites ON CfgSites.No = TSE.SiteNo
LEFT JOIN [Reason] ON ([Reason].[ReasonNo] = TSE.[ReasonNo])
GROUP BY [CfgSites].[No]
,[CfgSites].[Name]
,[Media]
,[MediaIndex]
,[MediaGroup]
,[MediaName]
,TSE.SortOrder
,TSE.[ReasonNo]
,[Reason].[Name]
UNION ALL

SELECT CfgSites.No
,CfgSites.Name
,MediaExtended.MediaNo
,0
,sum(transactionsales.qty) AS Qty
,SUM(transactionsales.value) AS Value
,MediaGroup
,ISNULL([MediaExtended].[FullName], 'Unknown Media') AS MediaName
,MediaExtended.SortOrder
,null
,null
,null
FROM MediaExtended
INNER JOIN transactionsales ON transactionsales.media = MediaExtended.TotalForMedia
INNER JOIN [Sessions] ON [TransactionSales].[SiteNo] = [Sessions].[SiteNo]
AND [TransactionSales].[TermNo] = [Sessions].[TermNo]
AND [TransactionSales].[SessionNo] = [Sessions].[SessionNo]
AND [Sessions].[Type] = 0
LEFT JOIN CfgSites ON TransactionSales.SiteNo = CfgSites.No
WHERE [MediaExtended].[TotalForMedia] > 0    AND TransactionSales.SiteNo IN (@SiteNo)
    AND (
        Qty <> 0
        OR Value <> 0
        )
    AND Media <> 0
    AND ExcludeFromReport <> 1
    AND (
        MediaNo IN (@MediaNo)
        OR MediaNo IS NULL
        )
    AND TransactionSales.TermNo = 0
    AND SessionDate BETWEEN @SessionDateFrom
        AND @SessionDateTo

        Group By CfgSites.No
,CfgSites.Name
,MediaNo
,MediaGroup
,FullName
,MediaExtended.SortOrder

ORDER BY [MediaGroup]
,TSE.[SortOrder]
,[MediaIndex]
OPTION (OPTIMIZE FOR UNKNOWN)

Answer 2

这不是一个直接的答案，而是一个解决办法。您可以在处理选项中打开缓存。然后安排订阅以刷新缓存。计划报告运行的超时要宽松得多。然后，当您去查看报告时，您甚至不需要等待10分钟，它就会从缓存中加载。

Answer 3

WorkAround，尽量减少报表中不必要的列。