Fastest way to delete cascade multiple objects in Hibernate
I am using Hibernate to delete an object that has two cascade levels, and my problem is that it is VERY slow when I query for the objects and then delete and I am interested in seeing if there is a faster way. My code, which takes about 15-30 seconds to delete 15 Statement objects looks like this:
public void deleteStatement(Long batchId) {
List<Statement> statements = session.createQuery("from Statement where batchId = ?").setParameter(0, batchId).list();
for(Statement statement : statements) {
session.delete(statement);
logger.debug("Deleted statement");
}
}
I know I could just do:
session.createQuery("delete from Statement where batchId = ?").setParameter(0, batchId).executeUpdate();
but the problem is that the delete cascades do not occur with this method. Is there an efficient way to delete my objects and still have the cascade occur, or is there something I'm doing wrong?
Thanks for the help!
Update
In response to davidfrancis, here's a paraphrase of the SQL that hibernate generates. It's crazy and thousands of lines long, believe it or not!! Note, my domain object, Statement, contains set of Invoice, which contains set of Transaction. I am lazy loading all of my collections, btw. First there's a lot of data retrieval, which, I believe happens with the HQL select query:
org.hibernate.hql.ast.QueryTranslatorImpl - HQL: from com.myapp.domain.cc.Statement where batchId = ?
org.hibernate.hql.ast.QueryTranslatorImpl - SQL: select statement0_.id as id2_, statement0_.batchId as batchId2_ from statement statement0_ where batchId=?
org.hibernate.loader.Loader - result row: EntityKey[com.myapp.domain.cc.Statement#393]
org.hibernate.loader.Loader - result row: EntityKey[com.myapp.domain.cc.Statement#394]
...
org.hibernate.SQL - select invoices0_.statementId as stateme12_2_1_, invoices0_.id as id1_, invoices0_.id as id3_0_, invoices0_.statementId as stateme12_3_0_ from invoice invoices0_ where invoices0_.statementId=?
org.hibernate.loader.Loader - result row: EntityKey[com.myapp.domain.cc.Invoice#48987]
org.hibernate.loader.Loader - found row of collection: [com.myapp.domain.cc.Statement.invoices#393]
org.hibernate.loader.Loader - result row: EntityKey[com.myapp.domain.cc.Invoice#48988]
org.hibernate.loader.Loader - found row of collection: [com.myapp.domain.cc.Statement.invoices#393]
...
org.hibernate.SQL - select transactio0_.invoiceId as invoiceId3_1_, transactio0_.id as id1_, transactio0_.id as id4_0_, transactio0_.invoiceId as invoiceId4_0_ from transactions transactio0_ where transactio0_.invoiceId=?
org.hibernate.loader.Loader - result row: EntityKey[com.myapp.domain.cc.Transaction#306534]
org.hibernate.loader.Loader - found row of collection: [com.myapp.domain.cc.Invoice.transactions#48996]
...
Now, the deletion:
org.hibernate.persister.entity.AbstractEntityPersister - Static SQL for entity: com.aa.itfs.mcla.domain.cc.Transaction
org.hibernate.persister.entity.AbstractEntityPersister - Version select: select id from transactions where id =?
org.hibernate.persister.entity.AbstractEntityPersister - Snapshot select: select transactio_.id, transactio_.ccNumber as ccNumber4_, transactio_.approvalCode as approval3_4_, transactio_.saleAmount as saleAmount4_, transactio_.installmentNumber as installm5_4_, transactio_.numberOfInstallments as numberOf6_4_ from transactions transactio_ where transactio_.id=?
org.hibernate.persister.entity.AbstractEntityPersister - Insert 0: insert into transactions (ccNumber, approvalCode, saleAmount, installmentNumber, numberOfInstallments, invoiceId, id) values (?, ?, ?, ?, ?, ?, ?)
org.hibernate.persister.entity.AbstractEntityPersister - Update 0: update transactions set ccNumber=?, approvalCode=?, saleAmount=?, installmentNumber=?, numberOfInstallments=? where id=?
org.hibernate.persister.entity.AbstractEntityPersister - Delete 0: delete from transactions where id=?
org.hibernate.persister.entity.AbstractEntityPersister - Identity insert: insert into transactions (ccNumber, approvalCode, saleAmount, installmentNumber, numberOfInstallments, invoiceId) values (?, ?, ?, ?, ?, ?)
org.hibernate.persister.entity.AbstractEntityPersister - Static SQL for entity: com.myapp.domain.cc.Statement
org.hibernate.persister.entity.AbstractEntityPersister - Version select: select id from statement where id =?
org.hibernate.persister.entity.AbstractEntityPersister - Snapshot select: select statement_.id, statement_.statementNumber as statemen2_2_, statement_.filename as filename2_, statement_.statementType as statemen4_2_ from statement statement_ where statement_.id=?
org.hibernate.persister.entity.AbstractEntityPersister - Insert 0: insert into statement (statementNumber, filename, statementType, batchId, id) values (?, ?, ?, ?, ?)
org.hibernate.persister.entity.AbstractEntityPersister - Update 0: update statement set statementNumber=?, filename=?, statementType=? where id=?
org.hibernate.persister.entity.AbstractEntityPersister - Delete 0: delete from statement where id=?
org.hibernate.persister.entity.AbstractEntityPersister - Identity insert: insert into statement (statementNumber, filename, statementType, batchId) values (?, ?, ?, ?)
org.hibernate.persister.collection.AbstractCollectionPersister - Static SQL for collection: com.myapp.domain.cc.Statement.invoices
org.hibernate.persister.collection.AbstractCollectionPersister - Row insert: update invoice set statementId=? where id=?
org.hibernate.persister.collection.AbstractCollectionPersister - Row delete: update invoice set statementId=null where statementId=? and id=?
org.hibernate.persister.collection.AbstractCollectionPersister - One-shot delete: update invoice set statementId=null where statementId=?
org.hibernate.persister.collection.AbstractCollectionPersister - Static SQL for collection: com.myapp.domain.cc.Invoice.transactions
org.hibernate.persister.collection.AbstractCollectionPersister - Row insert: update transactions set invoiceId=? where id=?
org.hibernate.persister.collection.AbstractCollectionPersister - Row delete: update transactions set invoiceId=null where invoiceId=? and id=?
org.hibernate.persister.collection.AbstractCollectionPersister - One-shot delete: update transactions set invoiceId=null where invoiceId=?
Random selects ....
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Invoice [NONE]: select invoice0_.id as id3_0_, invoice0_.invoiceNumber as invoiceN2_3_0_, invoice0_.merchantNumber as merchant3_3_0_, invoice0_.installmentNumber as installm4_3_0_, invoice0_.saleDate as saleDate3_0_, invoice0_.paymentDate as paymentD6_3_0_, invoice0_.amount as amount3_0_, invoice0_.amountType as amountType3_0_, invoice0_.saleType as saleType3_0_, invoice0_.ccType as ccType3_0_, invoice0_.statementType as stateme11_3_0_, invoice0_.statementId as stateme12_3_0_ from invoice invoice0_ where invoice0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Invoice [READ]: select invoice0_.id as id3_0_, invoice0_.invoiceNumber as invoiceN2_3_0_, invoice0_.merchantNumber as merchant3_3_0_, invoice0_.installmentNumber as installm4_3_0_, invoice0_.saleDate as saleDate3_0_, invoice0_.paymentDate as paymentD6_3_0_, invoice0_.amount as amount3_0_, invoice0_.amountType as amountType3_0_, invoice0_.saleType as saleType3_0_, invoice0_.ccType as ccType3_0_, invoice0_.statementType as stateme11_3_0_, invoice0_.statementId as stateme12_3_0_ from invoice invoice0_ where invoice0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Invoice [UPGRADE]: select invoice0_.id as id3_0_, invoice0_.invoiceNumber as invoiceN2_3_0_, invoice0_.merchantNumber as merchant3_3_0_, invoice0_.installmentNumber as installm4_3_0_, invoice0_.saleDate as saleDate3_0_, invoice0_.paymentDate as paymentD6_3_0_, invoice0_.amount as amount3_0_, invoice0_.amountType as amountType3_0_, invoice0_.saleType as saleType3_0_, invoice0_.ccType as ccType3_0_, invoice0_.statementType as stateme11_3_0_, invoice0_.statementId as stateme12_3_0_ from invoice invoice0_ with (updlock, rowlock) where invoice0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Invoice [UPGRADE_NOWAIT]: select invoice0_.id as id3_0_, invoice0_.invoiceNumber as invoiceN2_3_0_, invoice0_.merchantNumber as merchant3_3_0_, invoice0_.installmentNumber as installm4_3_0_, invoice0_.saleDate as saleDate3_0_, invoice0_.paymentDate as paymentD6_3_0_, invoice0_.amount as amount3_0_, invoice0_.amountType as amountType3_0_, invoice0_.saleType as saleType3_0_, invoice0_.ccType as ccType3_0_, invoice0_.statementType as stateme11_3_0_, invoice0_.statementId as stateme12_3_0_ from invoice invoice0_ with (updlock, rowlock) where invoice0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Invoice [FORCE]: select invoice0_.id as id3_0_, invoice0_.invoiceNumber as invoiceN2_3_0_, invoice0_.merchantNumber as merchant3_3_0_, invoice0_.installmentNumber as installm4_3_0_, invoice0_.saleDate as saleDate3_0_, invoice0_.paymentDate as paymentD6_3_0_, invoice0_.amount as amount3_0_, invoice0_.amountType as amountType3_0_, invoice0_.saleType as saleType3_0_, invoice0_.ccType as ccType3_0_, invoice0_.statementType as stateme11_3_0_, invoice0_.statementId as stateme12_3_0_ from invoice invoice0_ where invoice0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Invoice [PESSIMISTIC_READ]: select invoice0_.id as id3_0_, invoice0_.invoiceNumber as invoiceN2_3_0_, invoice0_.merchantNumber as merchant3_3_0_, invoice0_.installmentNumber as installm4_3_0_, invoice0_.saleDate as saleDate3_0_, invoice0_.paymentDate as paymentD6_3_0_, invoice0_.amount as amount3_0_, invoice0_.amountType as amountType3_0_, invoice0_.saleType as saleType3_0_, invoice0_.ccType as ccType3_0_, invoice0_.statementType as stateme11_3_0_, invoice0_.statementId as stateme12_3_0_ from invoice invoice0_ with (holdlock, rowlock) where invoice0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Invoice [PESSIMISTIC_WRITE]: select invoice0_.id as id3_0_, invoice0_.invoiceNumber as invoiceN2_3_0_, invoice0_.merchantNumber as merchant3_3_0_, invoice0_.installmentNumber as installm4_3_0_, invoice0_.saleDate as saleDate3_0_, invoice0_.paymentDate as paymentD6_3_0_, invoice0_.amount as amount3_0_, invoice0_.amountType as amountType3_0_, invoice0_.saleType as saleType3_0_, invoice0_.ccType as ccType3_0_, invoice0_.statementType as stateme11_3_0_, invoice0_.statementId as stateme12_3_0_ from invoice invoice0_ with (updlock, rowlock) where invoice0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Invoice [PESSIMISTIC_FORCE_INCREMENT]: select invoice0_.id as id3_0_, invoice0_.invoiceNumber as invoiceN2_3_0_, invoice0_.merchantNumber as merchant3_3_0_, invoice0_.installmentNumber as installm4_3_0_, invoice0_.saleDate as saleDate3_0_, invoice0_.paymentDate as paymentD6_3_0_, invoice0_.amount as amount3_0_, invoice0_.amountType as amountType3_0_, invoice0_.saleType as saleType3_0_, invoice0_.ccType as ccType3_0_, invoice0_.statementType as stateme11_3_0_, invoice0_.statementId as stateme12_3_0_ from invoice invoice0_ where invoice0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Invoice [OPTIMISTIC]: select invoice0_.id as id3_0_, invoice0_.invoiceNumber as invoiceN2_3_0_, invoice0_.merchantNumber as merchant3_3_0_, invoice0_.installmentNumber as installm4_3_0_, invoice0_.saleDate as saleDate3_0_, invoice0_.paymentDate as paymentD6_3_0_, invoice0_.amount as amount3_0_, invoice0_.amountType as amountType3_0_, invoice0_.saleType as saleType3_0_, invoice0_.ccType as ccType3_0_, invoice0_.statementType as stateme11_3_0_, invoice0_.statementId as stateme12_3_0_ from invoice invoice0_ where invoice0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Invoice [OPTIMISTIC_FORCE_INCREMENT]: select invoice0_.id as id3_0_, invoice0_.invoiceNumber as invoiceN2_3_0_, invoice0_.merchantNumber as merchant3_3_0_, invoice0_.installmentNumber as installm4_3_0_, invoice0_.saleDate as saleDate3_0_, invoice0_.paymentDate as paymentD6_3_0_, invoice0_.amount as amount3_0_, invoice0_.amountType as amountType3_0_, invoice0_.saleType as saleType3_0_, invoice0_.ccType as ccType3_0_, invoice0_.statementType as stateme11_3_0_, invoice0_.statementId as stateme12_3_0_ from invoice invoice0_ where invoice0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for action ACTION_MERGE on entity com.myapp.domain.cc.Invoice: select invoice0_.id as id3_0_, invoice0_.invoiceNumber as invoiceN2_3_0_, invoice0_.merchantNumber as merchant3_3_0_, invoice0_.installmentNumber as installm4_3_0_, invoice0_.saleDate as saleDate3_0_, invoice0_.paymentDate as paymentD6_3_0_, invoice0_.amount as amount3_0_, invoice0_.amountType as amountType3_0_, invoice0_.saleType as saleType3_0_, invoice0_.ccType as ccType3_0_, invoice0_.statementType as stateme11_3_0_, invoice0_.statementId as stateme12_3_0_ from invoice invoice0_ where invoice0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for action ACTION_REFRESH on entity com.myapp.domain.cc.Invoice: select invoice0_.id as id3_0_, invoice0_.invoiceNumber as invoiceN2_3_0_, invoice0_.merchantNumber as merchant3_3_0_, invoice0_.installmentNumber as installm4_3_0_, invoice0_.saleDate as saleDate3_0_, invoice0_.paymentDate as paymentD6_3_0_, invoice0_.amount as amount3_0_, invoice0_.amountType as amountType3_0_, invoice0_.saleType as saleType3_0_, invoice0_.ccType as ccType3_0_, invoice0_.statementType as stateme11_3_0_, invoice0_.statementId as stateme12_3_0_ from invoice invoice0_ where invoice0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Transaction [NONE]: select transactio0_.id as id4_0_, transactio0_.ccNumber as ccNumber4_0_, transactio0_.approvalCode as approval3_4_0_, transactio0_.saleAmount as saleAmount4_0_, transactio0_.installmentNumber as installm5_4_0_, transactio0_.numberOfInstallments as numberOf6_4_0_, transactio0_.invoiceId as invoiceId4_0_ from transactions transactio0_ where transactio0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Transaction [READ]: select transactio0_.id as id4_0_, transactio0_.ccNumber as ccNumber4_0_, transactio0_.approvalCode as approval3_4_0_, transactio0_.saleAmount as saleAmount4_0_, transactio0_.installmentNumber as installm5_4_0_, transactio0_.numberOfInstallments as numberOf6_4_0_, transactio0_.invoiceId as invoiceId4_0_ from transactions transactio0_ where transactio0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Transaction [UPGRADE]: select transactio0_.id as id4_0_, transactio0_.ccNumber as ccNumber4_0_, transactio0_.approvalCode as approval3_4_0_, transactio0_.saleAmount as saleAmount4_0_, transactio0_.installmentNumber as installm5_4_0_, transactio0_.numberOfInstallments as numberOf6_4_0_, transactio0_.invoiceId as invoiceId4_0_ from transactions transactio0_ with (updlock, rowlock) where transactio0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Transaction [UPGRADE_NOWAIT]: select transactio0_.id as id4_0_, transactio0_.ccNumber as ccNumber4_0_, transactio0_.approvalCode as approval3_4_0_, transactio0_.saleAmount as saleAmount4_0_, transactio0_.installmentNumber as installm5_4_0_, transactio0_.numberOfInstallments as numberOf6_4_0_, transactio0_.invoiceId as invoiceId4_0_ from transactions transactio0_ with (updlock, rowlock) where transactio0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Transaction [FORCE]: select transactio0_.id as id4_0_, transactio0_.ccNumber as ccNumber4_0_, transactio0_.approvalCode as approval3_4_0_, transactio0_.saleAmount as saleAmount4_0_, transactio0_.installmentNumber as installm5_4_0_, transactio0_.numberOfInstallments as numberOf6_4_0_, transactio0_.invoiceId as invoiceId4_0_ from transactions transactio0_ where transactio0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Transaction [PESSIMISTIC_READ]: select transactio0_.id as id4_0_, transactio0_.ccNumber as ccNumber4_0_, transactio0_.approvalCode as approval3_4_0_, transactio0_.saleAmount as saleAmount4_0_, transactio0_.installmentNumber as installm5_4_0_, transactio0_.numberOfInstallments as numberOf6_4_0_, transactio0_.invoiceId as invoiceId4_0_ from transactions transactio0_ with (holdlock, rowlock) where transactio0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Transaction [PESSIMISTIC_WRITE]: select transactio0_.id as id4_0_, transactio0_.ccNumber as ccNumber4_0_, transactio0_.approvalCode as approval3_4_0_, transactio0_.saleAmount as saleAmount4_0_, transactio0_.installmentNumber as installm5_4_0_, transactio0_.numberOfInstallments as numberOf6_4_0_, transactio0_.invoiceId as invoiceId4_0_ from transactions transactio0_ with (updlock, rowlock) where transactio0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Transaction [PESSIMISTIC_FORCE_INCREMENT]: select transactio0_.id as id4_0_, transactio0_.ccNumber as ccNumber4_0_, transactio0_.approvalCode as approval3_4_0_, transactio0_.saleAmount as saleAmount4_0_, transactio0_.installmentNumber as installm5_4_0_, transactio0_.numberOfInstallments as numberOf6_4_0_, transactio0_.invoiceId as invoiceId4_0_ from transactions transactio0_ where transactio0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Transaction [OPTIMISTIC]: select transactio0_.id as id4_0_, transactio0_.ccNumber as ccNumber4_0_, transactio0_.approvalCode as approval3_4_0_, transactio0_.saleAmount as saleAmount4_0_, transactio0_.installmentNumber as installm5_4_0_, transactio0_.numberOfInstallments as numberOf6_4_0_, transactio0_.invoiceId as invoiceId4_0_ from transactions transactio0_ where transactio0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for entity com.myapp.domain.cc.Transaction [OPTIMISTIC_FORCE_INCREMENT]: select transactio0_.id as id4_0_, transactio0_.ccNumber as ccNumber4_0_, transactio0_.approvalCode as approval3_4_0_, transactio0_.saleAmount as saleAmount4_0_, transactio0_.installmentNumber as installm5_4_0_, transactio0_.numberOfInstallments as numberOf6_4_0_, transactio0_.invoiceId as invoiceId4_0_ from transactions transactio0_ where transactio0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for action ACTION_MERGE on entity com.myapp.domain.cc.Transaction: select transactio0_.id as id4_0_, transactio0_.ccNumber as ccNumber4_0_, transactio0_.approvalCode as approval3_4_0_, transactio0_.saleAmount as saleAmount4_0_, transactio0_.installmentNumber as installm5_4_0_, transactio0_.numberOfInstallments as numberOf6_4_0_, transactio0_.invoiceId as invoiceId4_0_ from transactions transactio0_ where transactio0_.id=?
org.hibernate.loader.entity.EntityLoader - Static select for action ACTION_REFRESH on entity com.myapp.domain.cc.Transaction: select transactio0_.id as id4_0_, transactio0_.ccNumber as ccNumber4_0_, transactio0_.approvalCode as approval3_4_0_, transactio0_.saleAmount as saleAmount4_0_, transactio0_.installmentNumber as installm5_4_0_, transactio0_.numberOfInstallments as numberOf6_4_0_, transactio0_.invoiceId as invoiceId4_0_ from transactions transactio0_ where transactio0_.id=?
...
Sorry for the mess! Ask and you shall receive :)
Answer
The fastest way is to do the deletes manually. You will have to use separate delete statements for each of the children rather than rely on the cascade. You will also need to delete the children first to avoid temporarily violating foreign key relationships.
Alternatively you can speed it up by using proxy fetching. It's likely that most of the time is spent fetching data that is not needed. When you use proxy fetching Hibernate will only fetch the ids and create proxy objects that will instantiate themselves the first time they are used. However since you immediately delete them they shouldn't need to be instantiated.