Spring Batch Item Writer Calling Stored Procedure

Question

While migrating a Spring application to Spring Batch, I encountered a scenario where I need to process chunks of records from the database and invoke a stored procedure for each chunk. However, each stored procedure call is taking approximately 500 milliseconds, and the performance improvement isn't significant.

Note : I should exclusively use stored procedures because the process involves handling records from over 30 tables

@Bean("simpletaskExecutor")
    public TaskExecutor simpletaskExecutor() {
        
         SimpleAsyncTaskExecutor asyncTaskExecutor = new  SimpleAsyncTaskExecutor("Async");
         asyncTaskExecutor.setConcurrencyLimit(concurrencyCount);
         asyncTaskExecutor.setThreadNamePrefix("can-batch-thread-"); 
         return asyncTaskExecutor;
        
    }


public class EmployeeItemWritter implements ItemWriter<Employee>{

  @Autowired
  private EmployeeService employeeService;
    
  @Override
    public void write(Chunk<? extends Employee> chunk) throws Exception {
    
    List<Employee> employeeList=(List<Employee>) chunk.getItems();
    
     for(Employee employee:employeeList) 
          {
              //long start = System.currentTimeMillis();
              callStoredProcedure(employee);
             // callStoredProcedureWithoutJPA(indvInvoiceConsCtl);
              //long end = System.currentTimeMillis();
              //System.out.println("Processing time in milleseconds "+(end-start));
          
          }
      
    
    }
    
    
    public void callStoredProcedure(Employee employee) {
        
        Map<String, Object> resultMap = employeeService.getEmployeeDetails(employee.getEmpId(),employee.getEmpTxnId());
        
        if(CollectionUtils.isEmpty(resultMap)) {
            log.error(ERROR_WHILE_EXECUTING_EMPLOYEE_DETAILS_PROCEDURE);
        }
    }

}

1.Is there any approach to enhance the performance for processing 17,000 records, which currently takes approximately 21 minutes?

2. Is it possible to utilize the parallelStream method within a Spring Batch item writer?

import org.springframework.batch.item.ItemWriter; public class EmployeeItemWritter implements ItemWriter{

    {
        @Override
        public void write(Chunk<? extends Employee> chunk) throws Exception {
                
                 long startTime = System.currentTimeMillis();
                 employeeList.parallelStream().forEach(employee -> { 
                 Map<String, Object> resultMap =employeeService.getEmpDetails(employee.getEmpId(),employee.getEmpTxnId);             
                 }); 
                 long endTime = System.currentTimeMillis();
                 System.out.println("Processing time in milleseconds "+(endTime-startTime));
        }        
                 
    }

Answer 1

Is there any approach to enhance the performance for processing 17,000 records, which currently takes approximately 21 minutes?

So app level throughput is less than 14 row/second. Yikes! Achieving one or two orders of magnitude improvement shouldn't be too hard.

Consider making a new CREATE TABLE REPORTING_EMP ..., something like that, which is dirt simple, no FK checks or anything like that. Just INSERT your 17k employee identifiers into it. Should take about one second.

Now that all those rows are visible, all at once, to the backend query optimizer, we're ready to BEGIN transaction and do whatever processing you intend. Should take less than 21 minutes. Why? Rather than producing lots of little plans, we make one big one. This can be more efficient, e.g. if the little plans would retrieve most rows from some table, it's far more efficient to simply tablescan it once.

each stored procedure call is taking approximately 500 milliseconds

It's not clear that a SQL command from the client should have been replaced by a stored procedure. In any event, if you reveal the EXPLAIN SELECT ... query plan, it will be possible to see what was happening during those 500 msec, and to see whether sensible things were happening. Understanding what was "slow" is the first step to writing a fast query.

     for(Employee employee:employeeList)
        ...

              callStoredProcedure(employee);
        ...
        Map<String, Object> resultMap = employeeService.getEmployeeDetails(
            employee.getEmpId(), employee.getEmpTxnId());

This seems to suggest that your batch size is 1?

If you're making hundreds or thousands of calls to this in a loop, you may want to rethink your approach. Usually it's better to push the loop down into the backend, letting it know that "many result rows" will come back, so it can produce a better plan. You might prefer to issue a single query which (quickly!) produces 17k result rows.

If you're JOINing against a lot of tables, consider requesting fewer details by ignoring some of those tables. It's much easier to work with a known performant query, and slowly build it up, adding one JOIN then another, until you "break" it, until it slows to a crawl. Then you know for certain where the slowdown came from, and you can focus your efforts on just that. Pay attention to the fast "before" and the slow "after" query plans, and how they changed.