Manufacturing cost overview report export times out when pulling 18-month history

PLM Teamcenter
, , , , , , , ,
1

We’re experiencing critical timeout issues with manufacturing cost overview exports in TC 12.3. When finance runs month-end reports covering 18 months of cost history across 3,500+ assemblies, the export process consistently fails after 4-5 minutes with database timeout errors.

The query appears to be pulling all cost records without pagination, and we’re seeing memory spikes to 12GB on the method server during execution. I’ve tried adjusting the report timeout settings in site.xconf, but the underlying database query optimization seems to be the real bottleneck.

SELECT * FROM CostHistory ch
JOIN PartMaster pm ON ch.partId = pm.id
WHERE ch.effectiveDate BETWEEN '2023-09-01' AND '2025-03-01'
ORDER BY ch.effectiveDate DESC

This blocks our entire month-end financial close process. Has anyone successfully implemented pagination strategies or query optimization for large manufacturing cost exports?

2

We had similar timeout problems last year. Beyond the database optimization, you really need pagination. Our solution was to break exports into 3-month chunks and aggregate them client-side. It’s not elegant, but it works reliably. We also increased the method server heap to 16GB specifically for cost reporting workloads during month-end. The memory management piece is crucial - without proper pagination, you’re loading everything into memory at once.

3

The composite index will help significantly. Also check your timeout configuration in both wt.properties and your database connection pool settings. We set wt.query.timeout to 600 seconds and increased dbcp.maxWait to match. But honestly, for 18 months across 3,500 assemblies, you’re looking at millions of cost records. Pagination isn’t optional at that scale.

4

I’ve seen this exact pattern before. Your query is doing a full table scan on CostHistory without any indexes on effectiveDate. The ORDER BY on 18 months of data is killing performance. Add a composite index on (effectiveDate, partId) and consider filtering by cost type if you don’t need all categories in every export.

5

Your issue requires a multi-layered approach addressing all four critical areas: database optimization, pagination, memory management, and timeout tuning.

Database Query Optimization: First, create the composite index immediately:

CREATE INDEX idx_cost_eff_part ON CostHistory(effectiveDate, partId, costType);

This enables index-only scans for your date-range queries. Also add a filtered index for active records if you’re including obsolete data unnecessarily.

Report Pagination Strategy: Implement server-side pagination in your cost export service. Modify the query to use OFFSET/FETCH:

int pageSize = 5000;
for (int offset = 0; offset < totalRecords; offset += pageSize) {
    query.setFirstResult(offset).setMaxResults(pageSize);
    processBatch(query.getResultList());
}

This keeps memory footprint constant regardless of total dataset size.

Memory Management: Increase method server heap, but more importantly, implement streaming export. Instead of accumulating all results in memory, write directly to the output stream in batches. Configure your export handler to flush every 1000 records. This prevents the 12GB memory spikes you’re seeing.

Timeout Configuration Tuning: Adjust these properties in wt.properties:


wt.query.timeout=900
wt.pom.dbcp.maxWait=180000
wt.method.timeout=1200000

Also implement a progress indicator so users know the export is running and not stalled. For 18-month exports, consider a two-tier approach: immediate export for recent 6 months, scheduled batch job for historical data that emails results when complete.

The combination of indexed queries, pagination, streaming output, and appropriate timeouts should resolve your month-end blocking issue. Test with 6-month exports first, then scale to full 18-month range once performance is validated.