Is it possible to use "TRUNCATE" in Spring Data JPA using JpaRepository? Or a more effective method than the standard deleteAll()?

Question

Im currently using the standard jparepository method repository.deleteAll() to clean my table before adding new info. The table consists of 8300 rows with 5 columns each. It's currently taking about 80 sec to get them all removed, while it takes 1-3 sec to put them in using the standard repository.saveAll(list). Is there a more effecient way to do this? Deleting the data manually in sql with DELETE FROM table takes 0,1 sec. Using MySQL database. log from putting in data log from deletion.

Rammish

Remark: there is a difference between DELETE FROM t and TRUNCATE t: the autoincrement numbering. – Angora

notice the answer posted. You can utilize it as a native query, with void return, but you can also issue the return as a Page<MyTable> with the anticipated zero count returned (but you'll also need the countQuery added in your @Query) – Timmerman

Answer 1

Example, in your service interface:

public interface MyTableInterface {
    //...

    void truncateMyTable();
}

In your service implementation (with @Autowired myTableRepository):

public class MyTableImpl implements MyTableService {

    // other methods, @Autowiring, etc

    @Override
    @Transactional
    public void truncateMyTable() {
        myTableRepository.truncateMyTable();
    }
}

In your repository;

public interface MyTableRepository extends JpaRepository<MyTable, Long> {
    //....

    @Modifying
    @Query(
            value = "truncate table myTable",
            nativeQuery = true
    )
    void truncateMyTable();
}

EDIT: Also notice the @Transactional on service implemntation layer, instead of placing it on DAO/Repository layer

Timmerman

This worked, got it down to 1 sec. Thanks for the quick response! – Rammish

Best answer so far – Incoordination

Bare in mind, that TRUNCATE is DDL and not DML. Generally, applications should not have permissions to issue DDL commands. – Kronstadt

Just to note for anyone, this query only works with modifying annotation. I thought it was not needed and tried using it, I kept getting an error. Not sure why its needed, maybe some other so answer has that information. Will update if I can find any info, but nonetheless you need it or else hibernate will complain. – Iphigeniah

Answer 2

The reason that deleteAll is not fast, is because it fetches all entities and then removes them one by one (normally sending one SQL to the database per row):

// Code from SimpleJpaRepository

@Transactional
public void deleteAll() {

    for (T element : findAll()) {
        delete(element);
    }
}

I would suggest that you create your own delete method in the repo like:

@Modifying
@Transactional
@Query("delete from MyEntity m")
void deleteAllWithQuery();

This will create only one SQL DELETE statement.

Jabber

DELETE is DML command and TRUNCATE is DDL command. Therefore, TRUNCATE TABLE is faster and uses fewer system resources than DELETE, because DELETE scans the table to generate a count of rows that were affected then delete the rows one by one and records an entry in the database log for each deleted row, while TRUNCATE TABLE just delete all the rows without providing any additional information. – Incoordination

Is this faster than using truncate as mentioned in above answer? – Iphigeniah

Truncate is faster but then the deletes will not be written to the log and maybe someone would need that – Jabber

Answer 3

Below Annotations over the class file of Dao Layer

@Repository
@Transactional

Below line in the method delete all records from table

int headFoot = entityManager.createQuery("DELETE FROM HeaderFooterUpload HeadFoot").executeUpdate(); 

Cyrano

Answer 4

Of course, about performance, create query and execute through entity manager (truncate table query or delete from table query) seems a good alternative of repository.deleteAll() method. Particulary truncate table seems the best way.

But if you don't want to use query for this problem you can use repository.deleteAllInBatch(). This method is provided by JpaRepository and in background execute delete from EntityName x query basically. this post can help you to have more details about difference between deleteAll() and deleteAllInBatch() methods.

On another hand, truncate query have a limit. You can use it just by creating native query. and your current DBMS might not have this command, or you might later move to a DBMS that does not have this command available. Good link for more information about truncate command with jpa

Irritating