I have a JPA hierarchy, where one object contains a set of abstract type implemented by a different number of subclasses. The problem is that when Hibernate tries to load these classes, it generates a request that usually receives the main object, but (lazily) loads these additional objects using the union (in the subquery). Sort of:
select ... from ( select ... from subtable1 union select ... from subtable2 ) where parent.id = ?
This is an extremely inefficient query (with 10k rows in the parent table and 1 of each subclass for all rows that receive the entire hierarchy for 100 or so records in the parent class, it takes several minutes, a pending request takes up to 50 minutes).
I don’t understand why they didn’t just leave the joins from the parent table ( select ... from parent p left join subtable1 ... left join subtable2 ... ), as this will lead to the fact that all the necessary data will be transferred for a fraction seconds. Is there any way to avoid this stupid connection request?
So far, the only viable solution I've seen is to change the inheritance type from TABLE_PER_CLASS to SINGLE_TABLE, but the tradeoffs for this are less ideal (including the inability to use non-null for columns, maintenance problems because extended classes will belong to different commands that can even work with various versions of software, etc.).
Just in case, this will help anyone, I will insert the real query generated by Hibernate with these relationships (JPQL is a comment at the beginning), this particular query took 34 seconds to execute itself (0.6 seconds when rewriting as left joins):
SELECT DISTINCT personjpa0_.person_id AS person1_902_0_, addressofr1_.aor_id AS aor1_899_1_, extensions2_.id AS id903_2_, commstypej3_.comms_type AS comms1_908_3_, credential4_.credentials_id AS credenti1_905_4_, credential5_.credentials_type AS credenti1_904_5_, servicepro6_.service_profile_id AS service1_901_6_, extensions7_.id AS id907_7_, personjpa0_.display_name AS display2_902_0_, personjpa0_.first_name AS first3_902_0_, personjpa0_.initials AS initials902_0_, personjpa0_.last_name AS last5_902_0_, personjpa0_.title AS title902_0_, personjpa0_.user_name AS user7_902_0_, addressofr1_.comms_type AS comms9_899_1_, addressofr1_.display_form AS display2_899_1_, addressofr1_.domain AS domain899_1_, addressofr1_.label AS label899_1_, addressofr1_.parameters AS parameters899_1_, addressofr1_.person_id AS person10_899_1_, addressofr1_.preference AS preference899_1_, addressofr1_.send_html AS send7_899_1_, addressofr1_.service_profile_id AS service11_899_1_, addressofr1_.user_part AS user8_899_1_, addressofr1_.person_id AS person10_0__, addressofr1_.aor_id AS aor1_0__, extensions2_.aor_id AS aor2_903_2_, extensions2_.resolution_status AS resolution1_910_2_, extensions2_.certificate_serial_number AS certific1_914_2_, extensions2_.device_type_name AS device2_914_2_, extensions2_.provisioned AS provisio3_914_2_, extensions2_.provisioning_token AS provisio4_914_2_, extensions2_.provisioning_unique_id AS provisio5_914_2_, extensions2_.sip_password AS sip6_914_2_, extensions2_.username AS username914_2_, extensions2_.voicemail_long_timeout AS voicemail8_914_2_, extensions2_.voicemail_short_timeout AS voicemail9_914_2_, extensions2_.clazz_ AS clazz_2_, extensions2_.aor_id AS aor2_1__, extensions2_.id AS id1__, credential4_.algorithm_name AS algorithm3_905_4_, credential4_.password AS password905_4_, credential4_.person_id AS person4_905_4_, credential4_.realm_id AS realm5_905_4_, credential4_.credentials_type AS credenti6_905_4_, credential4_.person_id AS person4_2__, credential4_.credentials_id AS credenti1_2__, servicepro6_.name AS name901_6_, servicepro6_.person_id AS person3_901_6_, servicepro6_.person_id AS person3_3__, servicepro6_.service_profile_id AS service1_3__, extensions7_.service_profile_id AS service2_907_7_, extensions7_.seqprofile_id AS seqprofile1_911_7_, extensions7_.certificate_port AS certific1_915_7_, extensions7_.email_aor_id AS email23_915_7_, extensions7_.emergency_numbers AS emergency2_915_7_, extensions7_.from_domain AS from3_915_7_, extensions7_.outbound_domain AS outbound4_915_7_, extensions7_.pbx_aor_id AS pbx24_915_7_, extensions7_.provisioning_delivery AS provisio5_915_7_, extensions7_.provisioning_host AS provisio6_915_7_, extensions7_.provisioning_port AS provisio7_915_7_, extensions7_.server_access_call_control_port AS server8_915_7_, extensions7_.server_access_host AS server9_915_7_, extensions7_.server_access_number AS server10_915_7_, extensions7_.server_access_secure_mode AS server11_915_7_, extensions7_.sip_domain AS sip12_915_7_, extensions7_.sip_enabled AS sip13_915_7_, extensions7_.sip_expires AS sip14_915_7_, extensions7_.sip_proxy AS sip15_915_7_, extensions7_.sip_realm AS sip16_915_7_, extensions7_.sip_transport AS sip17_915_7_, extensions7_.voicemail_long_timeout AS voicemail18_915_7_, extensions7_.voicemail_message_server AS voicemail19_915_7_, extensions7_.voicemail_number AS voicemail20_915_7_, extensions7_.voicemail_short_timeout AS voicemail21_915_7_, extensions7_.voicemail_userid AS voicemail22_915_7_, extensions7_.clazz_ AS clazz_7_, extensions7_.service_profile_id AS service2_4__, extensions7_.id AS id4__ FROM cdm.cdm_person AS personjpa0_ LEFT OUTER JOIN cdm.cdm_address_of_record AS addressofr1_ ON personjpa0_.person_id = addressofr1_.person_id LEFT OUTER JOIN (SELECT NULL AS device_type_name, id, NULL AS provisioning_unique_id, aor_id, NULL AS voicemail_long_timeout, NULL AS username, NULL AS certificate_serial_number, NULL AS provisioning_token, resolution_status, NULL AS voicemail_short_timeout, NULL AS provisioned, NULL AS sip_password, 1 AS clazz_ FROM cdm.sb_aor_details UNION SELECT device_type_name, id, provisioning_unique_id, aor_id, voicemail_long_timeout, username, certificate_serial_number, provisioning_token, NULL AS resolution_status, voicemail_short_timeout, provisioned, sip_password, 2 AS clazz_ FROM cdm.fmc_remote_number_address_of_record_extension) AS extensions2_ ON addressofr1_.aor_id = extensions2_.aor_id LEFT OUTER JOIN cdm.cdm_comms_type AS commstypej3_ ON addressofr1_.comms_type = commstypej3_.comms_type LEFT OUTER JOIN cdm.cdm_credentials AS credential4_ ON personjpa0_.person_id = credential4_.person_id LEFT OUTER JOIN cdm.cdm_credentials_type AS credential5_ ON credential4_.credentials_type = credential5_.credentials_type LEFT OUTER JOIN cdm.cdm_service_profile AS servicepro6_ ON personjpa0_.person_id = servicepro6_.person_id LEFT OUTER JOIN (SELECT NULL AS server_access_host, NULL AS sip_domain, NULL AS voicemail_message_server, NULL AS outbound_domain, NULL AS emergency_numbers, NULL AS provisioning_delivery, id, NULL AS sip_expires, NULL AS pbx_aor_id, NULL AS server_access_call_control_port, NULL AS provisioning_port, NULL AS email_aor_id, NULL AS voicemail_number, NULL AS voicemail_long_timeout, NULL AS certificate_port, NULL AS sip_enabled, NULL AS sip_transport, NULL AS server_access_number, service_profile_id, NULL AS server_access_secure_mode, NULL AS voicemail_userid, seqprofile_id, NULL AS sip_realm, NULL AS voicemail_short_timeout, NULL AS provisioning_host, NULL AS from_domain, NULL AS sip_proxy, 1 AS clazz_ FROM cdm.sb_service_profile_details UNION SELECT server_access_host, sip_domain, voicemail_message_server, outbound_domain, emergency_numbers, provisioning_delivery, id, sip_expires, pbx_aor_id, server_access_call_control_port, provisioning_port, email_aor_id, voicemail_number, voicemail_long_timeout, certificate_port, sip_enabled, sip_transport, server_access_number, service_profile_id, server_access_secure_mode, voicemail_userid, NULL AS seqprofile_id, sip_realm, voicemail_short_timeout, provisioning_host, from_domain, sip_proxy, 2 AS clazz_ FROM cdm.fmc_service_profile_extension) AS extensions7_ ON servicepro6_.service_profile_id = extensions7_.service_profile_id WHERE personjpa0_.person_id IN (1, 2, [continuous ids], 199, 200);