SSRS Report timing out after 10 minutes

Question

I have a report with an incredibly slow dataset. Unfortunately we can't optimise the query further at this moment in time. However, no matter what timeouts i change the report still times out after 10 minutes. I have tried the report dataset timeout, the report execution time out as well as the remote execution on the sql server. None seem to have worked.

a bit of digging yielded the following resource which i have also tried to no avail:

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

Why would my report be timing out after 10 minutes?

The script:

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)

Will need to filter for media but the if it can return all in reasonable time then should be okay (please correct me if that logic is wrong. Im quite new to sql).

Answer 1

I was able to rewrite the query to the following. It has a massive boost in speed (from ~40 seconds to about ~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)