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...

Question: can you directly delete these records from the database in that time? If not, there's no code you can write that will be able to either. You could always do this in an async method so it returns immediately while still doing the delete in the background. — lane.maxwell, Apr 26 '23 at 14:42
Thanks so much @lane.maxwell for quick response however I have to delete these records not from the database but using code and if so is there any means I can optimize the query so that I can reduce time to at least 30 seconds up to its completion? — Janeth Jackson, Apr 26 '23 at 14:51
I think he meant can you try deleting directly from database and how long does it take? If it takes as long or similar, then not much can be done via code. To optimize query: what percent of records are you deleting? If you're deleting more than 50%, it could be faster to save the records you don't wish to delete in a temp taple, truncate the table and then insert back the data or something like that. https://stackoverflow.com/questions/24785439/deleting-1-millions-rows-in-sql-server — Raizekas, Apr 26 '23 at 16:09
Thanks @Raizekas for clarifying it more further however using the native query has worked perfect fine it has reduced much time thanks so much for your concern. — Janeth Jackson, Apr 26 '23 at 17:17

score 2 · Accepted Answer · answered Apr 26 '23 at 16:08

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.

Thanks alot @lane.maxwell for your suggestion using the Native SQL has reduced time up to 33 seconds which is somehow perfect as the way I wanted, thanks so much I really appreciate it. — Janeth Jackson, Apr 26 '23 at 17:13

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

1 Answers1