Click here to Skip to main content
15,848,724 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
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.

What I have tried:

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...
Posted
Comments
Richard MacCutchan 26-Apr-23 10:32am    
That's about 9 microseconds per record. Not bad really.
Office Systems 26-Apr-23 10:36am    
yah sure @Richard MacCutchan but is there any means I can optimize the query to reduce this time at least to 30 seconds up to completion.
Gerry Schmitz 26-Apr-23 10:38am    
What's the backend database? How many records are left, if any?
Office Systems 26-Apr-23 10:44am    
I'm using PostgreSQL as the backend database and 1000k records are left after deleting these records with that particular orderCode Id @Gerry Schmitz
Gerry Schmitz 26-Apr-23 11:54am    
Use SQL directly. Native SQL is always faster than the ORM; just don't be using the ORM "at the same time" while you purge (then it shouldn't matter). Conversely, "logical" deletes don't physically delete (at the moment); but only return "non-deleted" records in queries (saving purging for later).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900