crudrepository save takes too long to update an entity-CodePudding

I've got a controller that first selects all data with status = 'CREATED' transferType = 'SOME_TYPE' and DATE_TIME between x and y, and then put all the data in the List<TransferEntity>

then i am going through each element in the list and updating status to 'CHECKED'

if (listOfTransfers.isNotEmpty()){
    for(element in listOfTransfers){
        element.status = "CHECKED"
        repos.transfers.save(element)
    }
}

entity itself is pretty straight forward with no relations to other tables


@Entity
@Table( name = "TRANSFERS")
class TransferEntity(

    @Id
    @Column(name = "Identifier", nullable = false)
    var Identifier: String? = null,

    @Column(name = "TRANS_DATE_TIME")
    var transDateTime: LocalDateTime? = null,

    @Column(name = "TRANS_TYPE", nullable = true, length = 255)
    var transType: String? = null,

    @Column(name = "STATUS")
    var status: String = ""
)

i tried to experiment with indexes (oracle)

`CREATE INDEX TRANS_INDEX_1 ON TRANSFERS(STATUS)`
`CREATE INDEX TRANS_INDEX_2 ON TRANSFERS(TRANS_DATE_TIME)`
`CREATE INDEX TRANS_INDEX_3 ON TRANSFERS(TRANS_TYPE)`

or created them as one index CREATE INDEX TRANS_INDEX_4 ON TRANSFERS(STATUS,TRANS_DATE_TIME,TRANS_TYPE)

but it wasnt a big difference UPDATE

witn TRANS_INDEX_1 2 and 3 - 3192 elements were updateind in 5 minutes 30 sec
with TRANS_INDEX_4 - 3192 elements were updated in 5 minutes 30 sec

maybe there are different approaches to mass update elements inside the list or perhaps indexes are completely wrong and i dont understand them as much as i want it to.

UPDATE 2 technically saveAll() method works much faster but still I think there should be a room for improvement

saveAll() - 3192 elements were saved under 3minutes 21seconds
save() 3192 elements were save under 5minutes 30 seconds

CodePudding user response：

You call save() each time you update an element. 1000 elements will create 1000 query calls to the database, you repeat too many calls to your DB and that's why your function is slow.

Instead, you could use saveAll() after you updated all the elements

as suggested below, we also have to config the batch_size properly to really do the trick

Indexes won't help in this situation since they benefit the select operation more than update or insert

CodePudding user response：

Since you set the same value to all the elements of your list, you can make a batch update query :

Query q = entityManager.createQuery("update TransferEntity t set t.status = :value where t in (:list)");
q.setParameter("value", "CHECKED");
q.setParamter("list", listOfTransfers);
q.execute();

If you use ORACLE as backend be aware that in clause is limited to 1000 elements. Therefore you might have to split your list in buckets of 1000 elements and loop on this query for each bucket.