Skip to main content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
[eclipselink-users] Inefficient Query Criteria Delete for Super Class

Consider the following Model
IPSubnet is a sub class of NetworkAddressBlock

We are trying to Bulk Delete IPSubnet with the following code

    cb = (CriteriaBuilderImpl) em.getCriteriaBuilder();

    CriteriaDelete<IPSubnetDAO> subnetDelete =            cb.createCriteriaDelete(IPSubnetDAO.class);

                    Root subnetRoot = subnetDelete.from(IPSubnetDAO.class);

                    subnetDelete.where(cb.greaterThanOrEqualTo(subnetRoot.get(IPSubnetDAO._numericLowerAddress.getFieldName()),

                                                               net.getNumericLowerAddress()),

                                       cb.lessThanOrEqualTo(subnetRoot.get(IPSubnetDAO._numericUpperAddress.getFieldName()),

                                                            net.getNumericLowerAddress()),

                                       cb.or(cb.equal(subnetRoot.get(IPSubnetDAO._rootParentBlock.getFieldName()).get(IPSubnetDAO._ipAddressDomain.getFieldName()),

                                                      net.getIpAddressDomain()),

                                             cb.equal(subnetRoot.get(IPSubnetDAO._ipAddressDomain.getFieldName()),

                                                      net.getIpAddressDomain()))); 


The following queries were generated

DELETE FROM IPSubnet WHERE EXISTS(SELECT t0.ENTITYID FROM IPSubnet t3, NetworkAddressBlock t2, IPSubnet t1, NetworkAddressBlock t0 WHERE (((((t1.NUMERICLOWERADDRESS >= ?) AND (t1.NUMERICUPPERADDRESS <= ?)) AND ((t3.IPADDRESSDOMAIN = ?) OR (t1.IPADDRESSDOMAIN = ?))) AND ((t1.ENTITYID = t0.ENTITYID) AND (t0.ENTITYCLASS = ?))) AND ((t2.ENTITYID = t1.ROOTPARENTBLOCK) AND ((t3.ENTITYID = t2.ENTITYID) AND (t2.ENTITYCLASS = ?)))) AND t1.ENTITYID = IPSubnet.ENTITYID)

                bind => [369098752, 385875967, 150000, 150000, IPSubnetDAO, IPSubnetDAO]

 

DELETE FROM NetworkAddressBlock WHERE (ENTITYCLASS = ?) AND NOT EXISTS(SELECT t0.ENTITYID FROM NetworkAddressBlock t0, IPSubnet t1 WHERE (t1.ENTITYID = t0.ENTITYID) AND t0.ENTITYID = NetworkAddressBlock.ENTITYID)

                bind => [IPSubnetDAO]


The second query is very inefficient and has bad explain plan (involves Full Table Scan). My question is why is NOT Eclipselink applying the same criteria that it applied to child table to the parent table as well? Why is it trying to generate a different query that looks at all NetworkAddressBlock entries that does NOT exist in child table?


We have huge number of records in this table and is taking lot of time for the second query.


Is there a way to customize this behavior and generate the same query for parent table as well?


Thanks,

Rama


Back to the top