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.
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..
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)
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
Retrieving data ...