Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2014
    Posts
    2

    Unanswered: Bulk deletion of data

    - We use JPA/Hibernate for db stuff.
    - We have 9 related tables to cleanup.

    Basically, we need to remove (bulk delete) old, "bad" data through a background task that runs periodically.
    I guess we can select all that bad data, put it on a list and loop it to remove the data - no worrying if related data were also deleted. But performance is critical and the said solution is not efficient. At first I tried to use jpql to do the bulk delete but we have tables that are embeddable and I cannot delete those manually using jpql. I also tried to add ON DELETE CASCADE on hibernate, but hibernate is giving me an exception (cannot remember the exception, though I think it is about inverse one to many relationship). So what I plan to do is execute native sql. Delete tables manually - DELETE FROM table where table.id NOT IN (<subquery>).

    Am I doing it right? Or should I really consider the ON DELETE CASCADE?
    Or are there better ways to do this?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by hardc0d3r View Post
    - We use JPA/Hibernate for db stuff.
    - We have 9 related tables to cleanup.

    Basically, we need to remove (bulk delete) old, "bad" data through a background task that runs periodically.
    I guess we can select all that bad data, put it on a list and loop it to remove the data - no worrying if related data were also deleted. But performance is critical and the said solution is not efficient. At first I tried to use jpql to do the bulk delete but we have tables that are embeddable and I cannot delete those manually using jpql. I also tried to add ON DELETE CASCADE on hibernate, but hibernate is giving me an exception (cannot remember the exception, though I think it is about inverse one to many relationship). So what I plan to do is execute native sql. Delete tables manually - DELETE FROM table where table.id NOT IN (<subquery>).

    Am I doing it right? Or should I really consider the ON DELETE CASCADE?
    Or are there better ways to do this?
    There are multiple ways to accomplish this from the database perspective, but Hibernate isn't likely to like any of those ways!

    Probably the most efficient way is to use native database commands. Depending on your database engine, the easiest answer is probably proprietary and may be rather drastic (like dropping and re-creating the table itself.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2014
    Posts
    2
    The data to be deleted are not that many compared to the good data, so I guess re-creating the table is not needed.

    Thanks for the answer!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •