When creating or modifying incidents with related SLAs there is slow performance.

When creating or modifying incidents with related SLAs there is slow performance.

Product
BMC Remedy Service Desk Application (version 7.0.03)
Environment
Incident Management 7.0.3 SLM 7.1
Solution
This issue was reported in defect SW00303636: PERFORMANCE:problematic table
qualification on HPD:HELP DESK caused long running SQL when executing
INT:HPDSLM:INC:SLMSetTarget_780_CallGuide. There are no plans to resolve this is any
patch for ITSM 7.0.3.
However, defect SW00304467 was created to have this resolved in Incident Management
7.5 .
The details are below.
The SQLs are related to CurrentSLAs_tbl table field on HPD:Help Desk, which has the table
qualification below:
(( $SRInstanceID$ = 'ApplicationInstanceID') OR ( $InstanceId$ = 'ApplicationInstanceID'))
AND ( 'deleted' = "false") AND ( 'GoalTypes' = "Request-Based")
when a NULL value is passed to $SRInstanceID$, the above table qualification is converted to
a SQL where clause as below:
((T1401.C490009000 IS NULL) OR ('ID0014221474ADapapSACkZNBgMYoA' =
T1401.C490009000)) AND (T1401.C490000200 = 1) AND (T1401.C300905300 = 0))
Although there are indexes built on ApplicationInstanceID, the predicate (T1401.C490009000
IS NULL) OR ('ID0014221474ADapapSACkZNBgMYoA' = T1401.C490009000) caused a full
table scan on the Oracle database becaue Oracle's index does not store NULL values.
A possible workaround:
Create a filter that puts "NA" in the SRInstanceID field when it is NULL.