Genealogy tracking API batch queries timing out when tracing

MES DELMIA Apriso MES
, , , , , , , ,
1

We’re experiencing timeout errors when running batch genealogy queries through the REST API to trace material lineage for compliance audits. Our current implementation retrieves full genealogy trees for 50-100 parts at a time, but queries consistently fail after 30 seconds with a 504 Gateway Timeout.

The API call structure looks like this:


POST /api/genealogy/batch-trace
{
  "partIds": ["P-001", "P-002", ...],
  "direction": "both",
  "depth": "unlimited"
}

We’ve noticed that individual queries complete in 2-3 seconds, but batching them causes the timeout. We need this for monthly compliance reports where we trace hundreds of parts. Has anyone dealt with batch query pagination or recursive logic to handle this more efficiently? Also wondering if there are API timeout configuration settings we can adjust or if database indexing might help.

2

Thanks for the insights. I checked with our DBA and confirmed we don’t have composite indexes on the genealogy relationship tables. That’s definitely contributing to the problem. However, we still need to process large batches for our compliance reporting. Is there a recommended approach for recursive client logic that would handle this more gracefully? Should we be limiting depth even if we need full traceability?

3

Let me provide a comprehensive solution that addresses all the key points mentioned in this thread.

1. Batch Query Pagination Strategy Reduce your batch size from 50-100 parts to 10-15 parts per API call. This keeps individual requests manageable while still providing reasonable throughput. Implement this with a simple loop:


for (batch in partIds.chunked(10)) {
  results.addAll(api.traceGenealogy(batch))
  Thread.sleep(200) // Rate limiting
}

2. Recursive Client Logic Implementation Instead of requesting unlimited depth in one call, implement breadth-first traversal on the client side. Query one level at a time, collect unique part IDs from results, then query the next level. This gives you full traceability while maintaining control over query complexity. Pseudocode approach:


// Pseudocode - Breadth-first genealogy traversal:
1. Start with initial part IDs as currentLevel
2. While currentLevel is not empty:
   a. Query genealogy for currentLevel (batch of 10-15)
   b. Extract all parent/child IDs from results
   c. Filter out already-processed IDs
   d. Set nextLevel = new unique IDs
   e. Add results to master genealogy map
   f. currentLevel = nextLevel
3. Return complete genealogy tree
// Typical depth: 4-6 levels for most products

3. API Timeout Settings Increase the gateway timeout in your Apriso server configuration. Edit the API gateway properties file and set api.gateway.timeout=90000 (90 seconds). Also check the database connection timeout - it should be at least 60 seconds. However, with proper pagination and recursive logic, you shouldn’t need to rely on longer timeouts.

4. Database Indexing Optimization Work with your DBA to create composite indexes on the genealogy tables. Critical indexes needed:

  • Composite index on (parent_part_id, relationship_type, created_date)
  • Composite index on (child_part_id, relationship_type, created_date)
  • Index on (part_id, status) for active part lookups

After indexing, run ANALYZE/UPDATE STATISTICS on the genealogy tables to ensure the query optimizer uses the new indexes effectively.

5. Additional Recommendations

  • Implement connection pooling with at least 20 connections for parallel batch processing
  • Add retry logic with exponential backoff for transient failures
  • Consider caching frequently accessed genealogy paths (24-hour TTL)
  • Monitor API response times and adjust batch sizes based on actual performance
  • For compliance reports, run queries during off-peak hours when database load is lower

This combination of client-side pagination, recursive traversal, proper indexing, and configuration tuning should eliminate your timeout issues while maintaining full genealogy traceability. We implemented this exact approach for a pharmaceutical client processing 500+ parts daily for FDA compliance reporting, and query times dropped from 30+ seconds (with timeouts) to 8-12 seconds total for complete genealogy trees.

4

One more thing to consider - implement caching for frequently queried genealogy paths. If you’re running monthly compliance reports, chances are you’re querying the same parts repeatedly. We built a Redis cache layer that stores genealogy results for 24 hours, which cut our API load by 70%. Just make sure to invalidate cache entries when parts are updated or new relationships are created.