Performance issue with the OOTB View Audit Log Button on the HPD:Help Desk Form

Version 2
    Share:|

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy Service Desk Application


    APPLIES TO:

    BMC Remedy Service Desk Application



    PROBLEM:

     

    We have a performance issue with the OOTB View Audit Log Button on the HPD:Help Desk Form. When clicked, it either takes around 2-3 minutes to return or locks the database causing other users to be kicked out of the system

    When Clicked, the first part of the SQL used is "WHERE (((T1622.C459 LIKE '%INC000000963482%' ) OR " This is the bit that's causing an issue as it is doing a LIKE search (front and back) on a text field in a table (HPD_HelpDesk_AuditLogSystem) which currently has 6.1 million records in.

    The only indexing is on the Original Request ID (field450) which is the 2nd part of the WHERE clause. I took all 3 parts of the WHERE clause and run them separately in Pre-Prod, the 2nd and the 3rd came back instantly however the first part took 7 seconds (and there's less than 1/2 million records on this table in Pre-Prod).

     

     


    SOLUTION:

     

    Legacy ID:KA375071

      

    lease open th form "HPD:HelpDesk_AuditLogSystem"

    The qualification in the table field " z2TH SystemAudit " on HPD:Help Desk Audit Log is doing a full scan of the form - "HPD:HelpDesk_AuditLogSystem" because of the "OR" clause.

    - We do not use 'Log Key 1', Qualification on the table can be changed :

    From: (('Original Request ID' = $Request ID$) OR ('Log Key 1' = $Infrastructure Change ID$)) AND EXTERNAL($Qualification$)

    To: ('Original Request ID' = $Request ID$) AND EXTERNAL($Qualification$)

    When this is done, the form opens quickly and does not hang.
     

      
    Related Products:  
       
    1. BMC Remedy Service Desk Application

     


    Article Number:

    000064801


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles