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.
