Spring boot jpa deletion getting too long time

Question

I have a postgresql database and there is a table having millions of record. This table has columns auto_id (sequence added), id, schedule, date, status.

auto_id is the primary key and sequence added to it.

In my jpa repository I have a method with following query,

@Query("delete from Rate r where r.id IN :rateIds")
void deleteByIds(@Param("rateIds") List<Integer> rateIds);

In this query I'm going to delete by id column value and this list size is around 100. But in the table for this id there are thousands of records. When I execute this code it getting too long time to respond.

There is a index added to this table combining id, schedule, date and status columns.

Can anyone give me a solution to speed this deletion? Is it ok to add a index to id column and will it faster?

Thank you.

Harsh · Accepted Answer · 2022-06-23T06:21:33.750

1

Several solutions can be applied! Proposing below to my knowledge

Index would certainly help but they are good when data needs to be retrieved instead of deleted.
Try creating a Stored procedure since they are pre-compiled and faster in executions instead of native queries!
Give this delete operation Asynchronous touch if possible. i.e. Accept the operation of delete and let it run in background, send the acknowledge request as soon as you receive the call (As your list may grow or shrink and you cannot always rely on DB performance)
Make use of JdbcTemplate instead of JPA as it is faster (This article has some related discussion Spring Data JPA Is Too Slow)

edited Jun 23 '22 at 06:21

answered Jun 23 '22 at 06:14

Harsh

812
1
10
23

Thank you very much for your reply. regarding point 3, currently delete happens in another thread but its status need to return and if return success I'm inserting data. Can you please suggest a article for point no 2 please? – Dui Samarasinghe Jun 23 '22 at 06:34
https://www.geeksforgeeks.org/postgresql-introduction-to-stored-procedures/ https://www.postgresqltutorial.com/postgresql-plpgsql/postgresql-create-procedure/ , Yes but your another thread would only return success when DB execution completes which may take time since you have numerous records ! Hence, invoking a store procedure will save few seconds, or best approach is Think about point 3 – Harsh Jun 23 '22 at 06:43
Do you have any sample on point 3. I'm actually not much clear on that idea. If it is a best way I can try it. I need to run the insert operation if only this delete is success. Thank you. – Dui Samarasinghe Jun 23 '22 at 06:46
https://www.baeldung.com/spring-async https://www.javadevjournal.com/spring-boot/spring-async-annotation/ Check these article , you may use `@Async` and `CompletableFuture` in combination to perform Async REST API operations – Harsh Jun 23 '22 at 06:51
With stored procedure I able to reduce the time. – Dui Samarasinghe Jun 27 '22 at 03:33

score 0 · Answer 2 · answered Jun 27 '22 at 03:39

I have created a procedure like this,

CREATE OR REPLACE FUNCTION schema."fn_delete"(ids text) RETURNS boolean AS $BODY$   DECLARE

query               VARCHAR(8000);

BEGIN
    query := 'delete from table where id in ('||ids||')';
    
    EXECUTE query;
    RETURN TRUE;
END;   $BODY$ LANGUAGE plpgsql VOLATILE COST 100;

In the repository added this,

@Query(value = "SELECT schema.fn_delete(:ids);", nativeQuery = true)
boolean deleteFunc(@Param("ids") String ids);

called it.

Also I have added this property,

spring.datasource.tomcat.remove-abandoned-timeout=120

Thank you @Harsh for your answer.

Spring boot jpa deletion getting too long time

2 Answers2