Compliance report export takes over 30 minutes for large datasets

QMS ETQ Reliance
, , , , , , , ,
1

We’re experiencing severe performance issues with compliance report exports in ETQ Reliance 2021. Our regulatory team needs to generate monthly compliance reports covering about 18,000 audit records, and the export process is taking 30-45 minutes to complete.

The current batch job configuration seems standard, but I suspect we need to optimize SQL indexes or review the query execution plan. The report pulls data from multiple compliance tables and joins with audit history.

Has anyone dealt with similar slow export issues? We need to get this down to under 5 minutes for regulatory deadlines.

SELECT c.*, a.audit_date, a.status
FROM compliance_records c
JOIN audit_history a ON c.record_id = a.record_id
WHERE c.created_date >= '2025-01-01'

Any suggestions for batch job tuning or database optimization would be appreciated.

2

Thanks for the suggestions. I checked and we do have indexes on record_id in both tables, but the execution plan shows a hash join with high cost. The batch size is set to 200 currently. Should I increase it significantly?

3

Hash joins aren’t necessarily bad, but for compliance reports with date range filters, you need a composite index. Create an index on compliance_records covering (created_date, record_id) and another on audit_history (record_id, audit_date, status). This will enable index intersection and dramatically reduce I/O. The query optimizer should then switch to merge joins which are much faster for sorted data.

4

Let me give you a comprehensive solution addressing all three optimization areas:

1. Batch Job Parameter Tuning: Increase your batch size from 200 to 1000-1500 records. Access ETQ Admin > System Configuration > Batch Job Settings and modify the ComplianceReportExport job parameters:


batchSize=1000
maxConcurrentThreads=4
timeoutMinutes=15

2. SQL Index Optimization: Create these composite indexes to support your query pattern:

CREATE INDEX IX_Compliance_DateRecord ON compliance_records(created_date, record_id) INCLUDE (status, audit_type);
CREATE INDEX IX_Audit_RecordDate ON audit_history(record_id, audit_date) INCLUDE (status);

These covering indexes eliminate key lookups and enable efficient range scans on the date filter.

3. Query Execution Plan Analysis: After creating indexes, update statistics and review the execution plan:

UPDATE STATISTICS compliance_records WITH FULLSCAN;
UPDATE STATISTICS audit_history WITH FULLSCAN;

You should now see index seeks instead of scans, and merge joins instead of hash joins. The estimated rows should closely match actual rows.

Additional Optimizations:

  • Enable query result caching in ETQ for frequently-run compliance reports
  • Configure SQL Server max degree of parallelism (MAXDOP) to 4 for this query type
  • Schedule index maintenance to run weekly during off-hours
  • Consider implementing filtered indexes if you frequently query specific compliance types

Expected Results: With these changes, your 18K record export should complete in 3-5 minutes. Monitor the first few runs and adjust batch size if needed. If you’re still seeing issues after implementing all three areas, check for blocking queries or resource contention during peak hours.

The key is addressing all three focus areas together - batch tuning alone won’t solve it if your indexes are suboptimal, and great indexes won’t help if your execution plan isn’t utilizing them properly.

5

First thing to check is whether you have proper indexes on the join columns. Run an execution plan analysis on that query - I bet you’re seeing table scans instead of index seeks. Also, check if statistics are up to date on those tables.

6

Don’t forget to check your tempdb configuration too. Compliance reports often use tempdb heavily for sorting and intermediate results. Make sure you have multiple tempdb files configured (one per CPU core up to 8 files) with equal sizing.