Our genealogy queries through the REST API are taking 15-20 seconds to return results for serial number traceability. We’re running Smart Factory 2022.2 and querying parent-child relationships for components used in final assemblies.
The query retrieves about 500 serial numbers per assembly, going back 3-4 levels in the genealogy tree. Response times are impacting our quality investigations-we need near real-time traceability for customer complaints.
Are there indexing strategies or API filtering techniques that could speed this up? Should we be using pagination differently or implementing some form of caching?
The API supports depth limiting and field filtering that most people don’t use. Add ?depth=4&fields=serialNumber,parentSerialNumber,componentCode to your GET request. This prevents the API from retrieving unnecessary genealogy metadata and limits traversal depth. Also, use the expand parameter selectively-expanding all relationships is expensive.
Pagination in genealogy is tricky. Use cursor-based pagination with ?cursor=next&pageSize=100 rather than offset-based. The API maintains the tree structure across pages, so you won’t get orphaned child nodes. However, each page request still triggers a full tree traversal-pagination only affects the response size, not query performance. For real optimization, you need caching.
Comprehensive optimization strategy addressing all focus areas:
Serial Number Indexing:
Database-level improvements are critical. Beyond basic indexes, implement:
- Covering indexes that include all columns in your SELECT clause to avoid table lookups
- Partitioning on CreatedDate for the genealogy table-monthly partitions work well for most manufacturing operations
- Materialized views for common genealogy patterns (e.g., direct parent-child, full assembly trees)
- Database statistics refresh-run ANALYZE on genealogy tables weekly to maintain optimal query plans
For Smart Factory 2022.2, verify these indexes exist:
- CREATE INDEX idx_genealogy_serial ON genealogy(serialNumber, createdDate)
- CREATE INDEX idx_genealogy_parent ON genealogy(parentSerialNumber, childSerialNumber, componentCode)
- CREATE INDEX idx_genealogy_composite ON genealogy(serialNumber, parentSerialNumber, level)
API Pagination and Filtering:
Optimize your API calls with these techniques:
-
Depth Control: Always specify exact depth needed. Default unlimited depth is a performance killer:
GET /api/genealogy/{serialNumber}?depth=4&direction=upstream
-
Field Projection: Request only required fields to reduce payload size and database joins:
?fields=serialNumber,parentSerialNumber,componentCode,level,createdDate
-
Cursor Pagination: Use cursor-based for large result sets (>100 records):
?cursor=eyJzZXJpYWwiOiJTTi0xMjM0NSIsImxldmVsIjoyfQ&pageSize=50
-
Parallel Queries: For multi-level trees, query each level in parallel rather than sequentially. Make separate API calls for each depth level and assemble client-side.
-
Batch Serial Lookup: Use POST /api/genealogy/batch with array of serial numbers to retrieve multiple trees in one request-much faster than individual GET calls.
Caching Strategies:
Implement multi-layer caching for maximum performance:
Layer 1 - Application Cache (Redis/Memcached):
- Cache complete genealogy trees with TTL based on assembly status (6 hours for active, 24 hours for completed)
- Key pattern: `genealogy:{serialNumber}:depth:{n}:direction:{upstream|downstream}
- Implement cache warming for frequently queried assemblies
- Use cache-aside pattern with lazy loading
Layer 2 - API Response Cache:
- Enable Smart Factory’s built-in response caching with ETag support
- Send If-None-Match headers on subsequent requests to get 304 Not Modified responses
- Configure cache headers: Cache-Control: max-age=3600 for completed assemblies
Layer 3 - Database Query Cache:
- Enable query result caching in your database (Oracle result cache, PostgreSQL shared buffers)
- Pre-execute common genealogy queries during off-peak hours to warm database cache
Invalidation Strategy:
- Subscribe to Smart Factory webhooks for genealogy changes: POST /api/webhooks with event type ‘genealogy.updated’
- Implement selective cache invalidation-only clear affected serial numbers, not entire cache
- Use cache tags to group related genealogy trees for bulk invalidation
Performance Monitoring:
- Enable API query timing headers: X-Query-Time, X-Cache-Status
- Set up alerts for queries exceeding 2-second threshold
- Track cache hit rates-target 90%+ for production genealogy queries
Implementation Priority:
- Add database indexes (immediate 60-70% improvement)
- Implement API filtering and depth limiting (additional 20-30% improvement)
- Deploy Redis caching layer (90%+ reduction in average query time for cached data)
- Set up genealogy snapshots for completed assemblies (handles historical queries)
This combination should bring your 15-20 second queries down to under 500ms for most cases, with cache hits returning in under 100ms. Monitor and adjust TTL values based on your manufacturing cycle times and genealogy change frequency.
Implement Redis caching with a 6-hour TTL for genealogy trees. Key pattern: genealogy:{serialNumber}:depth{n}. On first query, cache the entire tree structure. Subsequent queries hit the cache. Use cache invalidation webhooks-Smart Factory can notify your cache when genealogy records change. This reduced our average query time to 200ms with 95% cache hit rate.
Good suggestions. We’re not using depth limiting currently-the API was returning the entire tree by default. How does pagination work with genealogy queries? If we paginate at level 2, does it still traverse all child levels or does it cut off mid-tree?
Don’t forget about the genealogy snapshot feature in 2022.2. You can pre-generate genealogy snapshots for completed assemblies and query those instead of live data. The snapshots are indexed differently and optimized for read performance. Set up a scheduled job to create snapshots for assemblies older than 24 hours.