I am having some performance issues due to some HQL queries resulting in large Cartesian Products. To get around this I'm trying to lazy fetch the collections although I cant find a nice way of applying a where clause to resulting collections.
Simplified version of the original HQL query
Code:
SELECT TPR
FROM TacticalPriceRule
INNER JOIN FETCH TPR.tacticalPriceRuleDurations TPRD
LEFT JOIN FETCH TPR.tacticalPriceRuleCountryOfOrigins as TPRCOO
LEFT JOIN FETCH TPR.tacticalPriceRuleType as TPRT
LEFT JOIN FETCH TPR.tacticalPriceRuleCars as TPRC
WHERE
TPRD.duration.min <= :duration and TPRD.duration.max >= :duration
.... more conditions on other joins
This results in nearly nearly 1 million rows for a few tiny tables. Not ideal.
If I attempt to do this using lazy initialisation, any 'where' or 'with' clauses are ignored and the entire sets matching the tactical price rule is contained within the collection.
Same applies for subselect fetch types.
Thanks,
Dan
Login
FAQ
Search