Legacy code optimization: reducing processing time by 99%

# Optimizing Legacy Code: How to Cut Processing Time by 99% in a Real-World Case

Implementing REST integration with an external system often runs into performance issues. In this article, we'll break down how we sped up processing 75,000 records in just 4 steps, slashing execution time by 99%. The case is based on a real task integrating a banking system with an external partner under strict constraints.

Problem: REST Integration with Partner Constraints

Initial conditions:

• Need to send user data to the partner's system via REST API
• Partner accepts only single requests (no batch processing allowed)
• Execution time limit: a 5 a.m. window to minimize load
• Data volume: 75,000 records with NEW status
• No asynchronous processing or horizontal scaling allowed

The original code used paginated loading via Spring Data Pageable with a page size of 2000 records. The critical error was accumulating all modified entities in memory until a single saveAll() operation, which led to:

• Automatic context flush before each SELECT
• Generation of 2000 UPDATE queries per page
• Transaction lock during HTTP calls

Initial benchmarks showed processing time ≈105 seconds on an Apple M4 with 16 GB RAM. Emulating a 1 ms delay from the external system made this a critical metric for production.

Step 1: Eliminating Redundant DB Operations

SQL log analysis revealed the fatal flaw: Hibernate was generating 2000 separate UPDATEs instead of batch operations. The cause was holding entities in the persistence context across loop iterations. The solution involved two key changes:

• Replacing end-of-process saves with intermediate commits:

content.forEach(this::registerUser);
userRepository.saveAll(content);

• Implementing a native query for bulk status updates:

@Modifying
@Query(value = """
UPDATE users SET status = CAST(:status AS text)
WHERE id IN (:ids)
""", nativeQuery = true)
int batchUpdateStatus(@Param("ids") Collection<Long> ids, @Param("status") String status);

These changes yielded initial results—a 6% time reduction (to 98.4 seconds). Key takeaway: Standard Spring Data JPA methods can incur hidden overhead with large data volumes.

Step 2: Fine-Tuning Processing Parameters

The next optimization focused on batch size. Testing showed:

• At size 500: time increased by 8.41% (106.68 sec)
• At size 2000: baseline 98.4 sec
• At size 5000: 1.86% improvement (96.58 sec)

It's important to understand there's no silver bullet. Optimal size depends on:

• Hardware specs (disk IOPS, network bandwidth)
• Complexity of business logic in the processing loop
• DB connection pool settings

Recommended approach: Run A/B testing in a production-like environment with resource profiling (CPU, memory, disk I/O). In our case, 5000 records proved optimal, reducing the number of transaction commits.

Step 3: Safe Horizontal Scaling

Attempting two-thread processing led to disaster—duplicating all operations (150,000 instead of 75,000 queries). The solution required changing data access strategy:

• Implementing SELECT FOR UPDATE SKIP LOCKED:

SELECT * FROM users WHERE status = 'NEW' ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 5000

• Implementing a transactional loop:

@Transactional
public void processBatch() {
  List<User> batch = userRepository.lockAndSelectBatch(5000);
  batch.forEach(this::registerUser);
  userRepository.batchUpdateStatus(
    batch.stream().map(User::getId).collect(Collectors.toList()),
    UserStatus.PROCESSED.name()
  );
}

This approach ensured:

• Guaranteed one-time processing
• Minimal table locks
• Ability to run N parallel instances

After implementation, processing time dropped to 1.05 seconds—a 99% reduction from the original.

Key Optimization Stages

• Analyzing Hidden Overhead

- Enabling show-sql to track generated queries

- Checking persistence context behavior in loops

• Switching to Batch Operations

- Replacing saveAll() with intermediate commits

- Using native queries for bulk updates

• Experimenting with Parameters

- Testing various batch sizes

- Monitoring system metrics during tests

• Preparing for Scaling

- Implementing SKIP LOCKED for concurrent access

- Decomposing transactions into atomic blocks

Key Takeaways

• Hibernate Flush Before SELECT—a critical issue with large data volumes. Every DB query triggers an automatic flush, causing a cascade of UPDATEs.

• Batch Size Isn't Constant. Requires empirical tuning based on workload specifics. Tests showed 5000 records as optimal.

• Transactional Isolation for Scaling. SELECT FOR UPDATE SKIP LOCKED enables concurrent processing without duplication but increases lock hold times.

• Profiling Is Essential. Without production-like benchmarks, optimizations can backfire (like shrinking batch size to 500).

• Legacy Constraint Compatibility. Solutions must account for external limits (e.g., partner's ban on batch requests), ruling out simple async processing.

The final implementation delivered stable 1.05±0.05 second processing times under load testing with 100,000 records. The key to success was combining technical optimizations with deep understanding of the external system contract. This approach works for any REST API integration scenarios with request size limits and reliability requirements.

— Editorial Team