Carrier rate lookup latency spikes during peak load in transportation management, causing shipment delays and SLA breaches

SCM Manhattan Active Supply Chain
, , , , , , , ,
1

We’re experiencing significant latency spikes in carrier rate lookup API calls during peak order processing hours. Our transportation management system handles around 15,000 shipments daily, and during morning batch processing (7-10 AM), rate lookup calls that normally complete in 200-300ms are spiking to 4-8 seconds.

The rate_lookup_cache table has grown to 8M+ records, and we’re seeing timeout errors on about 12% of API calls during peak load. This is causing shipment delays and SLA violations.

Here’s a typical API call pattern we’re seeing:

RateLookupRequest request = new RateLookupRequest();
request.setCarrierCode("FEDEX");
request.setServiceLevel("GROUND");
RateLookupResponse response = rateService.getRates(request);
// Timeout after 5000ms during peak hours

We’ve noticed that bulk rating jobs are scheduled to run at the same time as interactive lookups, and thread pool configuration hasn’t been reviewed since initial implementation. Any guidance on optimizing this would be greatly appreciated.

2

Thanks for the quick response. You’re right - our bulk rating jobs are scheduled at 8 AM which overlaps with peak interactive load. The thread pool config shows core=10, max=25 threads for the rate service. Given our volume, that does seem low. What would you recommend for 15K daily shipments? Also, should we implement response caching for frequently requested carrier/service combinations?

3

The 8M record cache table is your main bottleneck. Run an EXPLAIN PLAN on your rate lookup queries - I bet you’re doing full table scans. You need composite indexes on (carrier_code, service_level, origin_zip, dest_zip, effective_date). Also, implement a purge strategy for expired rates older than 90 days. That cache table should be under 2M records max for optimal performance.

4

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

API Response Caching Implementation: Enable Manhattan’s built-in rate cache framework first - it’s optimized for the transportation module. Configure in your rate_service.properties:

rate.cache.enabled=true
rate.cache.ttl=1800
rate.cache.max.entries=50000

This gives you 30-minute TTL with 50K entry capacity. Application-layer caching adds complexity without much benefit here.

Database Index Tuning: Create these composite indexes immediately:

CREATE INDEX idx_rate_lookup_composite ON rate_lookup_cache
(carrier_code, service_level, origin_zip, dest_zip, effective_date);
CREATE INDEX idx_rate_lookup_expiry ON rate_lookup_cache (expiry_date);

Then implement a nightly purge job to remove rates older than 90 days. This will bring your table from 8M to under 2M records.

Bulk Job Scheduling: Reschedule bulk rating jobs to 2:00 AM - 5:00 AM window. Update your job scheduler configuration to set interactive rate lookups at priority level 1 and bulk jobs at priority 5. This ensures interactive requests get resources first during any overlap.

Thread Pool Configuration: For 15K daily shipments with peak bursts, increase your rate service thread pool:

rate.service.thread.pool.core=40
rate.service.thread.pool.max=80
rate.service.thread.pool.queue.capacity=500

This handles concurrent lookups without exhausting resources.

Additional Monitoring: Implement response time tracking in your rate lookup calls. Set alerts for average response times exceeding 500ms or error rates above 2%. This gives you early warning of degradation.

After implementing these changes, you should see:

  • 70-80% reduction in database load through caching
  • Sub-second response times even during peak hours
  • Near-zero timeout errors
  • Improved SLA compliance

Monitor for 2-3 days and adjust cache TTL and thread pool sizes based on actual metrics. The combination of proper indexing, caching, and resource allocation should resolve your latency spikes completely.