CA Service Management

  • Private Community
 View Only

  • 1.  BOXI Performance issues

    Posted Feb 26, 2015 04:42 PM


  • 2.  Re: BOXI Performance issues

    Broadcom Employee
    Posted Mar 02, 2015 12:20 AM

    Hi,

    Could your question be more specific? Are you talking about OOTB reports, or customized reports?

    BOXI performance could related to many factors, such as architecture/hardware, database performance, or how the report/SQL designed, etc.

     

    It would be better to have a dedicated server for boxi, and a replicated database server.

    You can run the SQL (used by the report) directly on the database to test its performance, and ask your DBA to tune  both on the SQL and database to meet your performance target.

     

    Regards,

    Mark



  • 3.  Re: BOXI Performance issues

    Posted Mar 02, 2015 10:37 AM

    Hi Mark,

     

    This is regarding the customized reports, we have dedicated BOXI server with replicated database, our reports uses Audit_Log table and views for generating reports, we report on tickets to calculate SLA for each group that worked on the ticket and the one who closed it, so we report from Audit_Log status view.

     

    We did took the db backup and restored on test db, then executed command to count total rows in View_Audit_Group (select count(*) from [View_Audit_Group]) and it kept running for more than 15 minutes but could not return result even after 15 mins..but when I run command select count(*) from audit_log it returns results in just a few seconds..

     

    I am using SSMS to run these queries... we have few more reports that fetch data from act_log table to fetch data like who resolved the ticket to generate one of the report.. but reports takes 30-45 mins to execute.. and this is what is worrying us..


    -AB



  • 4.  Re: BOXI Performance issues

    Broadcom Employee
    Posted Mar 04, 2015 08:16 PM

    I guess there could be a large number of data in the audit_log table. How many rows?

     

    And apparently, select count(*) from [View_Audit_Group]), is different with select count(*) from audit_log.


    Here is the OOTB definition of View_Audit_Group

    Create view  [dbo].[View_Audit_Group] AS SELECT a.audobj_uniqueid, b.attr_after_val from_val, a.attr_after_val to_val, b.change_date from_time, a.change_date to_time FROM  audit_log a,  audit_log b WHERE a.audobj_uniqueid = b.audobj_uniqueid AND a.attr_name = 'group' AND a.attr_name = b.attr_name AND

    b.change_date = ( SELECT MAX(change_date) FROM  audit_log c WHERE c.change_date < a.change_date and c.audobj_uniqueid = a.audobj_uniqueid and c.attr_name = 'group') UNION SELECT a.audobj_uniqueid, a.attr_after_val, '', a.change_date, null FROM  audit_log a WHERE a.attr_name = 'group' AND a.attr_after_val != '' AND a.change_date = ( SELECT MAX(change_date) FROM  audit_log b

    WHERE b.audobj_uniqueid = a.audobj_uniqueid AND b.attr_name = 'group')

    UNION SELECT a.audobj_uniqueid, b.attr_before_val, b.attr_after_val, a.change_date, b.change_date FROM  audit_log a,  audit_log b WHERE a.attr_name = '' AND b.attr_name = 'group' AND b.change_date = ( SELECT MIN(change_date) FROM  audit_log c WHERE c.audobj_uniqueid = a.audobj_uniqueid AND c.attr_name = 'group') UNION SELECT distinct a.audobj_uniqueid, VG.last_name, '', a.change_date, null

    FROM (( call_req cr inner join  audit_log a ON cr.persid = a.audobj_persid) inner join  View_Group VG

    ON cr.group_id = VG.contact_uuid) WHERE NOT EXISTS ( SELECT b.audobj_persid FROM  audit_log b

    WHERE b.attr_name = 'group' AND cr.persid = b.audobj_persid) AND a.change_date = ( SELECT MIN(change_date) FROM  audit_log c WHERE a.audobj_persid = c.audobj_persid) UNION SELECT distinct a.audobj_uniqueid,VG.last_name, '', a.change_date, null FROM (( chg inner join  audit_log a ON chg.persid = a.audobj_persid) inner join  View_Group VG ON chg.group_id = VG.contact_uuid)

    WHERE NOT EXISTS ( SELECT b.audobj_persid FROM  audit_log b WHERE b.attr_name = 'group' AND chg.persid = b.audobj_persid) AND a.change_date = ( SELECT MIN(change_date) FROM  audit_log c WHERE a.audobj_persid = c.audobj_persid)

    GO

     

    I think it's normal that query on View_Audit_Group will take much longer time.

     

    You may consider:

    1. split the audit_log table, for example, one month data in a table

    As you have dedicated database and SDM for reporting, it should not be a problem to add new tables into the schema.

    And I think this should be the best way to improve the performance.

     

    2. Ask your DBA to tune the related SQL/view

     

    3. upgrade the hardware

     

    Regards,

    Mark