Hi,

I'm getting a really inefficeint SQL query generated off my HQL. I am obviously doing something wrong, can someone help?

I have not executed the query to completion, it takes too long. There are two tables, SubscriptionNotification and subscribedStructures. The generated SQL seems to perform joins to the same table 10 or so times, on the same foriegn key.


HQL:
from org.sdmx.registryapi.model.SubscriptionNotification as model WHERE
model.subscribedStructures.subscriptionType=? AND
( model.subscribedStructures.maintainableId=? OR model.subscribedStructures.maintainableId IS NULL ) AND
( model.subscribedStructures.agencyId=? OR model.subscribedStructures.agencyId IS NULL ) AND
( model.subscribedStructures.version=? OR model.subscribedStructures.version IS NULL ) AND
( model.subscribedStructures.orgSchemeId=? OR model.subscribedStructures.orgSchemeId IS NULL ) AND
( model.subscribedStructures.orgSchemeAgency=? OR model.subscribedStructures.orgSchemeAgency IS NULL ) AND
( model.subscribedStructures.orgSchemeVersion=? OR model.subscribedStructures.orgSchemeVersion IS NULL ) AND
( model.subscribedStructures.dataProviderId=? OR model.subscribedStructures.dataProviderId IS NULL )
group by model.urn


SQL:
select
subscripti0_.urn as urn0_,
subscripti0_.mail_to as mail2_0_,
subscripti0_.post_to as post3_0_,
subscripti0_.valid_from as valid4_0_,
subscripti0_.valid_to as valid5_0_,
subscripti0_.xml as xml0_ from
subscription_notification subscripti0_,
subscribed_structure subscribed1_,
subscribed_structure subscribed2_,
subscribed_structure subscribed3_,
subscribed_structure subscribed4_,
subscribed_structure subscribed5_,
subscribed_structure subscribed6_,
subscribed_structure subscribed7_,
subscribed_structure subscribed8_,
subscribed_structure subscribed9_,
subscribed_structure subscribed10_,
subscribed_structure subscribed11_,
subscribed_structure subscribed12_,
subscribed_structure subscribed13_,
subscribed_structure subscribed14_,
subscribed_structure subscribed15_
where
subscripti0_.urn=subscribed1_.notification_urn and
subscripti0_.urn=subscribed2_.notification_urn and
subscripti0_.urn=subscribed3_.notification_urn and
subscripti0_.urn=subscribed4_.notification_urn and
subscripti0_.urn=subscribed5_.notification_urn and
subscripti0_.urn=subscribed6_.notification_urn and
subscripti0_.urn=subscribed7_.notification_urn and
subscripti0_.urn=subscribed8_.notification_urn and
subscripti0_.urn=subscribed9_.notification_urn and
subscripti0_.urn=subscribed10_.notification_urn and
subscripti0_.urn=subscribed11_.notification_urn and
subscripti0_.urn=subscribed12_.notification_urn and
subscripti0_.urn=subscribed13_.notification_urn and
subscripti0_.urn=subscribed14_.notification_urn and
subscripti0_.urn=subscribed15_.notification_urn and
subscribed1_.subscription_type=? and
(subscribed2_.maintainable_id=? or subscribed3_.maintainable_id is null) and
(subscribed4_.agency_id=? or subscribed5_.agency_id is null) and
(subscribed6_.version=? or subscribed7_.version is null) and
(subscribed8_.org_scheme_id=? or subscribed9_.org_scheme_id is null) and
(subscribed10_.org_scheme_agency_id=? or subscribed11_.org_scheme_agency_id is null) and
(subscribed12_.org_scheme_version=? or subscribed13_.org_scheme_version is null) and
(subscribed14_.identifiable_id=? or subscribed15_.identifiable_id is null)
group by subscripti0_.urn

x

Try to add an explicit join to 'model.subscribedStructures' and then replace all 'model.subscribedStructures' with the alias you give it.

Eg.


Don't know if you need a LEFT JOIN or only a JOIN.

Thanks for your qucik reply!

That actually speeds things up enormously, thanks for your help!

Just one quick point,

In the HQL I have 'from xxx.yy.SubscriptionNotification' - which is the object type I want returned.

After executing the query I get a mixed list returned of SubscriptionNotification and SubscribedDataflow.

SubscriptionNotification actually contains a list of SubscribedDataflow objects and this is correctly mapped. So I am not sure why I also get the SubscribedDataflow object returned inside the SubscriptionNotification and outside it.

In fact the SubscribedDataflow object inside the SubscriptionNotification points to the same memory location as the one outside it.

Any ideas why the query (as above but with the join modification) returns a mixed list.

Ah... yes, you need to specify a 'select' clause as well. Eg.

Excellent! Thanks a lot.