This post is related to another one that I posted - since I didn't get response from that one, I guess that I didn't ask the right question. This one is a different question though, so I don't believe I've created a duplication. So please kindly bear with me.
For the original LEFT OUTER JOIN query below, it took at least one hour to complete:
select this_.*,trade2_.*
FROM
MEASURE this_
LEFT OUTER JOIN TRADE trade2_
ON this_.TRADE_ID=trade2_.ID
AND this_.BUSINESS_DATE = trade2_.BUSINESS_DATE
WHERE this_.TRADE_ID is not null
and this_.BOOK_ID=:1
and this_.BUSINESS_DATE in (:2)
and this_.EVENT_CONTEXT=:3
and this_.TRADE_ID=:4
After some optimization the query looked like:
select this_.*,trade2_.*
FROM
(SELECT * FROM MEASURE this_
WHERE this_.TRADE_ID IS NOT NULL
AND this_.BOOK_ID = :1
AND this_.BUSINESS_DATE IN (:2)
AND this_.EVENT_CONTEXT = :3
)this_
LEFT OUTER JOIN
(SELECT *
FROM TRADE trade2_
WHERE trade2_.BUSINESS_DATE IN (:2)
AND trade2_.BOOK_ID =:1
) tradebody2_
ON this_.TRADE_ID=trade2_.ID
AND this_.BUSINESS_DATE = trade2_.BUSINESS_DATE
WHERE this_.TRADE_ID is not null
and this_.BOOK_ID=:1
and this_.BUSINESS_DATE in (:2)
and this_.EVENT_CONTEXT=:3
and this_.TRADE_ID=:4
It showed that we use two subqueries to replace two tables, the test indicated a much faster turn-out. There are two questions in here:
Is this retrofit a validated optimization and why such a re-write an improvement?
Even the query is admittedly optimized, this is not something I can utilize - I need to use a Hibernate Criteria to represent the logic (we have an interface protocol to conform to), and from what I read, it's almost not viable to put a subquery as the FROM Clause in the "Criteria" context.
So I wonder, is there another to write the query for optimization, and at the same time, I can translate it into a Hibernate Criteria?