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:
我正在使用Hibernate删除一个具有两个级联级别的对象，我的问题是，当我查询对象然后删除时，它非常慢，我有兴趣看看是否有更快的方法。我的代码，大约需要15-30秒来删除15个Statement对象，如下所示：
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:
更新响应davidfrancis，这里是hibernate生成的SQL的解释。这很疯狂，成千上万行，信不信由你！注意，我的域对象Statement包含Invoice集，其中包含Transaction集。我懒得加载我的所有藏品，顺便说一下。首先有很多数据检索，我相信HQL选择查询会发生这种情况：
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 :)
对不起！问你应接受 ：）
1 个解决方案
#1
7
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.
最快的方法是手动删除。您必须为每个子项使用单独的delete语句，而不是依赖于级联。您还需要先删除子项，以避免暂时违反外键关系。
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.
或者，您可以使用代理提取来加快速度。很可能大部分时间花在获取不需要的数据上。当您使用代理提取时，Hibernate将仅获取ID并创建将在第一次使用时自我实例化的代理对象。但是，由于您立即删除它们，因此不需要实例化它们。
#1
7
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.
最快的方法是手动删除。您必须为每个子项使用单独的delete语句，而不是依赖于级联。您还需要先删除子项，以避免暂时违反外键关系。
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.
或者，您可以使用代理提取来加快速度。很可能大部分时间花在获取不需要的数据上。当您使用代理提取时，Hibernate将仅获取ID并创建将在第一次使用时自我实例化的代理对象。但是，由于您立即删除它们，因此不需要实例化它们。