[FIXED] crudrepository save takes too long to update an entity

Issue

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

Solution

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

Answered By - Tan Sang
Answer Checked By - Timothy Miller (JavaFixing Admin)