Deleting large volume of data at once in spring boot in fraction of a second

Question

How can I delete large amount of data more than 10 million records in fraction of a second in spring boot project, the code I'm having right now takes more than 1 minute and 30s to delete these records. Below is the code I'm using to delete these records.

Repository

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
        
@Modifying
@Transactional
@Query("delete from Order where orderCode = :orderCode")
void deleteByOrderCode(@Param("orderCode") String orderCode);
}

Service

@Service
public class OrderService {
    
    @Autowired
    private OrderRepository orderRepository;

    public DeleteResponse deleteResponse(String orderCode) {    
    
    orderRepository.deleteByOrderCode(orderCode);
  
        return new DeleteResponse("Orders Deleted Successfully");
   }
}

Controller

@RestController
@RequestMapping("/delete")
public class DeleteOrdersApi {
    
    @Autowired
    private OrderService orderService;

    @GetMapping("/orders/{orderCoder}")
    protected DeleteResponse deleteResponse(@PathVariable String orderCoder){
    return orderService.deleteResponse(orderCoder);
    }
    
}

Any suggestions will be much appreciated...

Answer 1

You haven't mentioned which database you're using here, but the database engine will play a huge part.

Consider adjusting your delete method to use a native query. You can drop the @Transactional and @Modifying annotations.

@Query(nativeQuery = true, value = "delete from order o where o.orderCode = ?")
void deleteByOrderCode(@Param("orderCode") String orderCode);

You're probably going to end up needing to perform some database optimizations to get this to be more efficient. Perhaps you can partition your data around the orderCode? The approach will be dependent on the DB engine that you're using.