Extra table reference in UPDATE causes huge performance impact.

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Extra table reference in UPDATE causes huge performance impact.

pveselov
Hello.

Below are the JPQL and generated native SQL query that I have a problem
with.
The problem is that the SQL version has an extra table reference in the
inner query. That table, unconstrained, has a number of entries, causing
number of processed records jump from ~4000 to ~30,000,000, causing large
delays.

You can see in the SQL, there is a "crud_non_unique t3" (from
E_CustomObjectNonUnique) reference, that is unconstrained (and doesn't
really belong there). I believe it's added because OpenJPA converts a join
into inner query, but doesn't remove the use of the table, so it appears in
the query, albeit unconstrained.

Any idea whether this is fixed post 2.4.2? Anything I can do to work around
this issue?

JPQL:
update E_CustomObjectNotUnique cd2 set cd2.numValue = cd2.numValue  +  1
where
cd2.object in (
  select fd.customData from
    E_CampaignDevice cd,
    E_FontanaDevice fd
    join cd.device sd where
       ( ( cd.campaign = :campaign ) AND ( cd.enabled = false )  )
       AND ( fd.retired = false )  AND ( sd.id = fd.id ) )
AND ( cd2.indexName = :indexName)

SQL:
UPDATE crud_non_unique
SET _number = (_number + 0)
WHERE id IN (SELECT DISTINCT t0.id
             FROM crud_non_unique t0
             WHERE (t0.object_ref IN (SELECT t5.id
                                      FROM vehicle_campaigns t1
                                               INNER JOIN vehicles t2 ON
t1.vehicle_id = t2.id AND t1.vehicle_tenancy = t2.tenancy
                                               CROSS JOIN faw_devices t4
                                               LEFT OUTER JOIN crud t5 ON
t4.CUSTOMDATA_ID = t5.id,
                                           crud_non_unique t3
                                      WHERE (t1.campaign = ? AND t1.enabled
= ?  AND t4.retired = ? AND t2.id = t4.id)) AND
                    t0.index_name = ?))




Thank you.