openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Pawel Veselov <>
Subject Extra table reference in UPDATE causes huge performance impact.
Date Mon, 22 Jul 2019 13:22:52 GMT

Below are the JPQL and generated native SQL query that I have a problem
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

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?

update E_CustomObjectNotUnique cd2 set cd2.numValue = cd2.numValue  +  1
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 ( = ) )
AND ( cd2.indexName = :indexName)

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

Thank you.

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message