Service Desk Application performs slow with SLA Violation time calculation enabled on the Incident or Request or Problem Search List page.

Document ID : KB000031048
Last Modified Date : 14/02/2018
Show Technical Document Details

Problem:

The SLA Violation time calculation on the Incident or Request or Problem Search List page leads to slow performance of ServiceDesk application.

 

Cause:

The SLA Violation time calculation on the Incident or Request or Problem Search List page builds the below SQL Query (can be seen in the ServiceDesk stdlog) that leads to slow performance:

03/07 00:32:28.04 servername sqlagt:select1064 46852 SIGNIFICANT sqlclass.c 1046 The following statement took 20219 milliseconds: Clause (SELECT call_req.open_date, call_req.id, min(attached_sla.time_to_violation) AS "mintime", min(usp_target_time.target_time) AS "mintgt", call_req.ref_num, call_req.last_mod_dt FROM (call_req LEFT JOIN attached_sla ON call_req.persid = attached_sla.mapped_cr LEFT JOIN usp_target_time ON call_req.persid = usp_target_time.mapped_cr) WHERE ( call_req.active_flag = 1 AND ( call_req.status = ? ) ) AND ( call_req.customer = ? OR call_req.assignee = ? OR call_req.group_id IN (SELECT grpmem.group_id FROM grpmem WHERE grpmem.member = ?) OR call_req.log_agent = ? OR call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.zservice_owner = ?) OR call_req.zapprover = ? OR call_req.group_id IN (SELECT ca_contact.contact_uuid FROM ca_contact WHERE ca_contact.alternate_identifier = ?) ) GROUP BY call_req.open_date, call_req.id, call_req.ref_num, call_req.last_mod_dt ORDER BY call_req.open_date DESC) Input (<string>ACK|<uuid>FE76C8E26ED87D40A736F7E31C9D829E|<uuid>FE76C8E26ED87D40A736F7E31C9D829E|<uuid>FE76C8E26ED87D40A736F7E31C9D829E|<uuid>FE76C8E26ED87D40A736F7E31C9D829E|<uuid>FE76C8E26ED87D40A736F7E31C9D829E|<uuid>FE76C8E26ED87D40A736F7E31C9D829E|<string>CCDDCMOPSSYS) 

 

Resolution:

The SLA calculation has to be disabled in order to avoid performance problems. Perform the below steps:

Create a .mod file with the below code and place it under /site/mods/majic. Recycle SDM Services. 

Note: Please test this on Test Server before placing this on Production Server.

MODIFY FACTORY cr 

DOMSET cr_list_web "open_date DESC, id DESC, status=status.sym, priority=priority.sym DESC, ref_num" "" STATIC; 

}; 

MODIFY FACTORY cr 

DOMSET cr_list_web_active_only "open_date DESC, id DESC, status=status.sym, priority=priority.sym DESC, ref_num" "" STATIC 

WHERE "active=1" ; 

}; 

}; 

 

MODIFY FACTORY in 

DOMSET in_list_web "open_date DESC, id DESC, status=status.sym, priority=priority.sym DESC, ref_num" "" STATIC; 

}; 

MODIFY FACTORY in 

DOMSET in_list_web_active_only "open_date DESC, id DESC, status=status.sym, priority=priority.sym DESC, ref_num" "" STATIC 

WHERE "active=1" ; 

}; 

}; 

 

MODIFY FACTORY pr 

DOMSET pr_list_web "open_date DESC, id DESC, status=status.sym, priority=priority.sym DESC, ref_num" "" STATIC; 

}; 

MODIFY FACTORY pr 

DOMSET pr_list_web_active_only "open_date DESC, id DESC, status=status.sym, priority=priority.sym DESC, ref_num" "" STATIC 

WHERE "active=1" ; 

}; 

};