Re: [eclipselink-users] Inefficient Query Criteria Delete for Super Clas

[andrei ilitchev <andrei.ilitchev@xxxxxxxxxx>]

Hi Rama,

If entity is mapped to two (or more) tables, that's the only way to implement bulk delete (without storing the pks of the objects to be deleted) because selection criteria may rely upon data from both tables.

If selection criteria for your bulk delete does not rely on any secondary table fields, simply run it against the base class instead.

Thanks,
Andrei

On 6/15/2015 10:56 AM, Rama Rao wrote:

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

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-users