Here’s the complete optimization approach that should resolve your timeout issues:
1. Database Indexing (Critical)
Have your DBA create these indexes on the document version history tables:
CREATE INDEX idx_doc_versions_composite ON document_versions(document_id, version_number, effective_date);
CREATE INDEX idx_doc_versions_audit ON document_versions(audit_trail_id, created_date);
These indexes dramatically speed up version history queries, especially when joining with audit trail data.
2. Batch Processing Configuration
Update your mastercontrol-export.properties file:
export.batch.size=2000
export.batch.commit.interval=500
export.batch.timeout=7200
The key is matching commit interval to half your batch size and giving individual batches enough timeout headroom.
3. Pagination Settings
In the same properties file, align pagination with batch processing:
export.pagination.maxResults=2500
export.pagination.fetchSize=1000
This ensures queries fetch enough data per round-trip without overwhelming memory.
4. Timeout Configuration Strategy
Instead of just increasing the global timeout, use tiered timeouts:
- Query timeout: 1800 seconds (30 min)
- Batch timeout: 7200 seconds (2 hours)
- Overall export timeout: 10800 seconds (3 hours)
This prevents individual slow queries from blocking the entire export while giving the full process enough time.
5. Enable Streaming Mode
In Admin Console > Document Control > Export Settings, enable “Stream Export Results”. This allows the compliance-auditing module to start building reports while the export is still running, rather than waiting for completion.
6. Monitor and Validate
After applying these changes, test with a subset first (e.g., 3000 documents). Monitor the database during export to confirm indexes are being used. Check the export logs for batch processing metrics-you should see consistent batch completion times around 2-3 minutes per batch.
The combination of proper indexing, optimized batch processing, and aligned pagination settings should get your 12K document export completing in under 90 minutes. The streaming mode ensures audit reports start generating immediately, further reducing perceived wait time.