after profiling my application, it turns out that a single method is taking 3 minutes to run, which is about a third of the total runtime.
The method deletes approx. 400.000 rows from each table (PROCESSED_CVA and PROCESSED_DVA).
The code executing the queries :
public final static String DELETE_CVA = "delete from PROCESSED_CVA where RUN_ID = ?";
public final static String DELETE_DVA = "delete from PROCESSED_DVA where RUN_ID = ?";
public void purge(Run run) throws HibernateException {
Session session = null;
if (session == null) {
session = sessionFactory.openSession();
}
Transaction t = session.beginTransaction();
try {
SQLQuery query = session.createSQLQuery(DELETE_CVA);
query.setLong(0, run.getRunId());
query.executeUpdate();
query = session.createSQLQuery(DELETE_DVA);
query.setLong(0, run.getRunId());
query.executeUpdate();
t.commit();
} catch (HibernateException he) {
logger.error("Failed to purge processed cva and dva for run: " + run.getRunId(), he);
t.rollback();
throw he;
}
}
Both tables have the same structure.
CREATE TABLE "PROCESSED_CVA" (
"DEAL_ID" VARCHAR2(23 BYTE),
"NTT_ID" VARCHAR2(10 BYTE),
"CVA" FLOAT(126),
"RUN_ID" NUMBER(10,0)) ;
ALTER TABLE "PROCESSED_CVA" ADD CONSTRAINT "PK_CVA" PRIMARY KEY ("DEAL_ID", "RUN_ID")
There is an index on the primary key.
The execution plan :
OPERATION OBJECT_NAME OPTIONS COST
DELETE STATEMENT 100582
|_ DELETE PROCESSED_CVA
|_ INDEX PK_CVA SKIP SCAN 100582
|_ Access Predicates
|_ RUN_ID=100
|_ Filter Predicates
|_ RUN_ID=100
Can I speed this up ?
UPDATE DBMS : Oracle
