Slow performance on reports

Products

• ServiceDesk

Issue/Introduction

Slow performance and timeouts in reports due to the use of the IncidentExtension, ChangeExtension, and ProblemExtension tables

Here is an example report:

select distinct
    Process.ReportLogProcessID as [Process.ReportLogProcessID],
    Process.ProcessStarted as [Process.ProcessStarted],
    Process.ProcessStarted as [Process.AgeDescription],
    Process.Result as [Process.Status],
    ImIncidentTicket0.ProcessId as [Incident.ProcessId],
    ImIncidentTicket0.IncidentName as [Incident.IncidentName],
    ImIncidentTicket0.IncidentType as [Incident.IncidentType],
    ImIncidentTicket0.AffectedUser as [Incident.AffectedUser],
    ImIncidentTicket0.Priority as [Incident.Priority],
    ImIncidentTicket0.Owner as [Incident.Owner],
    Task1.TaskID as [Task.TaskID],
    Task1.WFTaskNumber as [Task.WFTaskNumber],
    Task1.RespondDisplayFormat as [Task.RespondDisplayFormat],
    Task1.FormWidth as [Task.FormWidth],
    Task1.FormHeight as [Task.FormHeight],
    IncidentExtension2.Country as [IncidentExtension.Country],
    IncidentExtension2.Customer as [IncidentExtension.Customer],
    Group3.GroupName as [Task.GroupName]
from
    ReportProcess as Process with (NOLOCK) inner join
    ImIncidentTicket as ImIncidentTicket0 with (NOLOCK) on
        ((ImIncidentTicket0.ProcessId = Process.ReportProcessID)) inner join
    Task as Task1 with (NOLOCK) on
        ((Task1.SessionID = Process.SessionID)) left join
    IncidentExtension as IncidentExtension2 with (NOLOCK) on
        ((IncidentExtension2.IncidentExtension_id = Process.SessionID)) inner join
    TaskAssignment as TaskAssignment3 with (NOLOCK) on
        ((TaskAssignment3.TaskID = Task1.TaskID)) inner join
    UserReferenceIDLookup as UserReferenceIDLookup3 with (NOLOCK) on
        ((UserReferenceIDLookup3.ReferenceID = TaskAssignment3.ReferenceID)) inner join
    [User] as User3 with (NOLOCK) on
        ((UserReferenceIDLookup3.UserID = User3.UserID)) inner join
    [Group] as Group3 with (NOLOCK) on
        ((Group3.GroupID = TaskAssignment3.ReferenceID))
where
    (Task1.DoNotShowInTaskList = 'False' and
    Task1.IsCompleted = 'False' and
    ((UserReferenceIDLookup3.UserID = 'abef7b59-dca5-11ea-a2c3-0050569e7f9b' and
    (TaskAssignment3.ReferenceType = 2))) and
    (Process.Result != 'Closed'))
order by
    [Process.AgeDescription] desc

Checking the execution plan it was found that there is an implicit conversion between the IncidentExtension2.IncidentExtension_id and Process.SessionID fields, which have different data types Varchar and Nvarchar.

Changing to: ((IncidentExtension2.IncidentExtension_id  = CAST(Process.SessionID AS VARCHAR(50)))) inner join

0 seconds

Environment

Release: 8.5 RU4

Cause

Defect

Resolution

Download Workflow_8.5_POST_RU4_v8_3Feb_2022.zip from CUMULATIVE POST ITMS 8.5 RU4 POINT FIXES