Optimize of hibernate delete operation

Question

The code below works but have performance issues when the SQL deletes more than a million rows. It takes a very long time for 2M tables to be deleted approx 1 hr. We don't have control on the sql condition passed to us. Is there a better way to make this run faster?

Apart from this we don't have DB admin access so stored procedures can't be a solution. Any help is greatly appreciated. Thanks

SAMPLE SQL QUERY

DELETE TBL_A WHERE ID IN (SELECT ID FROM TBL_B WHERE 1=1 AND TBL_B.BD < TO_DATE(ADD_MONTHS(SYSDATE, -240)));

DELETE METHOD

 public void deleteRows(String table, String condition){

    StringBuilder sql = new StringBuilder();
    sql.append("DELETE TABLE=:TABLE WHERE CONDITION=:CONDITION ");

    try{
    transaction = session.beginTransaction();

    int rows = session.createSQLQuery(sql.toString()).setParamater("TABLE",TABLE).setParameter("CONDITION",CONDITION).executeUpdate();

    transaction.commit();
    }
    catch{
    //logic here
    }
    }

peter.hrasko.sk · Accepted Answer · 2014-06-24 20:20:00Z

1

There's no faster way for this specific "issue". Unfortunately, it is not a performance issue, it is an application design issue.

... BUT ...

You can:

Create a list-partitioned replica of the table with a single partition and a set of indexes identical to the original table.
Use insert /*+ append */ into the replica select * from the original table with NOT of the CONDITION.
Swap the single replica partition with the original table.
Truncate the single replica partition with drop storage option.

More specific answer would require a much more specific question.

answered Jun 24, 2014 at 20:20

peter.hrasko.sk

4,2012 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dimas Over a year ago

thanks nop, so the only way we optimize the performance of delete operation of our app is by changing the SQL condition itself right?

peter.hrasko.sk Over a year ago

Not quite. What I suggested is that instead of deleting a large amount of rows via DELETE you can use the "DWH"-like approach and insert the complementary rows (via direct-load!) into a different table, and then swap the two tables (via partition exchange). But, generally, the final solution is, really, very dependent on what amounts of data we are talking about, what options of doing table organization changes you have, etc. etc. :-(

peter.hrasko.sk Over a year ago

Also, for that particular sample query you posted in your question ... Try creating indexes on TBL_B (BD, ID) and on TBL_A (ID), if you you don't yet have those, and have the statistics on TBL_A and TBL_B recomputed. That might help a potentially suboptimal execution plan for the query. Alas, without knowing your application, all of the execution plans being used, your table structures and data volumes I can't suggest solutions to your performance problem(s).

Collectives™ on Stack Overflow

Optimize of hibernate delete operation

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related