时间戳

首页 > 解决方案 > SSRS 报告运行缓慢

reporting-services - SSRS 报告运行缓慢

问题描述

使用 Report Builder 3.0，我有一个使用以下数据集的报告：

Declare @Hierarchy nVarChar (1000) = EAC.GetHierarchy (@UserName, DEFAULT)
SELECT TOP(100)
CASE
         WHEN @ShowSourceTime = 1 THEN SourceTime
         ELSE Dateadd(Minute, @TimeZoneOffset, Time)
       END   as Time
  ,Warehouse.EventInfo.TimeZoneShortName
  ,Warehouse.EventInfo.Category
  ,Warehouse.EventInfo.[Action]
  ,Warehouse.EventInfo.[Result]
  ,Warehouse.EventInfo.Reason
  ,Warehouse.EventInfo.PersonId
  ,Warehouse.EventInfo.Title
  ,Warehouse.EventInfo.FirstName
  ,Warehouse.EventInfo.MiddleName
  ,Warehouse.EventInfo.LastName
  ,Warehouse.EventInfo.Suffix
  ,Warehouse.EventInfo.Nickname
  ,Warehouse.EventInfo.DoorName
  ,Warehouse.EventInfo.DoorBehaviorName
  ,Warehouse.EventInfo.EnterZone
  ,Warehouse.EventInfo.LeaveZone
  ,Warehouse.EventInfo.SiteCode
  ,Warehouse.EventInfo.CardCode
  ,Warehouse.EventInfo.SiteCode + '-' + Warehouse.EventInfo.CardCode as Badge

FROM
  Warehouse.EventInfo
WHERE
  Warehouse.EventInfo.[Time] Between @StartTime and @EndTime
  AND (1 = @PersonIdExpr OR EventInfo.PersonId IN (@PersonId))
  AND WareHouse.EventInfo.ZoneHierarchy LIKE @Hierarchy 
order by Time

The dataset is calling the view below:
SELECT     EAC.Event.CreatedUTC AS Time
,DATEADD(MINUTE, EAC.Event.CreatedUTCOffset
, EAC.Event.CreatedUTC) AS SourceTime
, EAC.TimeZoneMap.TimeZoneShortName
, EAC.EventClass.Name AS Class
, EAC.EventCategory.Name AS Category
, EAC.EventAction.Name AS Action
, EAC.EventResult.Name AS Result
, EAC.EventReason.Name AS Reason
, EAC.Person.Id AS PersonId
, EAC.Person.Title
, EAC.Person.FirstName
, EAC.Person.MiddleName
, EAC.Person.LastName
, EAC.Person.Suffix
, EAC.Person.DisplayName AS Nickname
, EAC.Door.Name AS DoorName
, Zone_1.Name AS EnterZone
, (SELECT TOP (1) Name 
   FROM EAC.Zone
   WHERE (Id IN (EAC.Door.Zone1Id, EAC.Door.Zone2Id)) 
   AND (Id <> EAC.Event.ZoneId)) AS LeaveZone
, EAC.DoorBehavior.Name AS DoorBehaviorName
, EAC.Event.CardCode
, EAC.Event.SiteCode
, ISNULL(EAC.Event.Alarmed, 0) AS AlarmType
, Zone_1.ZoneHierarchy
FROM EAC.Event 
INNER JOIN EAC.EventType ON EAC.EventType.Id = EAC.Event.EventTypeId 
LEFT OUTER JOIN EAC.EventClass ON EAC.EventClass.Id = EAC.Event.EventClassId 
LEFT OUTER JOIN EAC.EventCategory ON EAC.EventCategory.Id = EAC.EventType.CategoryId
LEFT OUTER JOIN EAC.EventAction ON EAC.EventAction.Id = EAC.EventType.ActionId 
LEFT OUTER JOIN EAC.EventResult ON EAC.EventResult.Id = EAC.EventType.ResultId 
LEFT OUTER JOIN EAC.EventReason ON EAC.EventReason.Id = EAC.EventType.ReasonId 
LEFT OUTER JOIN EAC.Person ON EAC.Person.Id = EAC.Event.PersonId 
LEFT OUTER JOIN EAC.Door ON EAC.Door.Id = EAC.Event.DoorId 
LEFT OUTER JOIN EAC.Zone AS Zone_1 ON Zone_1.Id = EAC.Event.ZoneId 
LEFT OUTER JOIN EAC.DoorBehavior ON EAC.DoorBehavior.Id = EAC.Door.DoorBehaviorId 
LEFT OUTER JOIN EAC.Credential ON EAC.Credential.Id = EAC.Event.CredentialId
LEFT OUTER JOIN EAC.WiegandCredential ON EAC.WiegandCredential.CredentialId = EAC.Credential.Id
LEFT OUTER JOIN EAC.TimeZoneMap on EAC.TimeZoneMap.Id = EAC.Door.TimeZoneMapId

它正在查询的表有数百万条记录，但为了得到任何返回，我将其限制为 100 条记录，但仍需要 15 分钟。

当我运行报告时，我在数据库中看到了这个：

这是执行计划： 执行计划

我可以做些什么来使这个报告更有效地运行？

标签： reporting-services

解决方案

有了这么多LEFT OUTER JOIN，您的查询运行缓慢也就不足为奇了。每个连接都会给您的代码带来复杂性。此外，LEFT|RIGHT [OUTER] JOIN它比常规连接慢得多，因为它必须完成 INNER JOIN（常规连接）的所有工作以及对结果进行空值扩展的额外工作。

此外，如果没有查看查询的解释计划，就不可能发表评论或查明问题。可能您缺少表上的索引

推荐阅读