0

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:

  1. Is this retrofit a validated optimization and why such a re-write an improvement?

  2. 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?

J.E.Y
  • 1,055
  • 13
  • 31
  • how many rows do you expect from your first query ? how big the tables are ? did you configure proper indexes on the tables ? – b.GHILAS Nov 06 '21 at 17:47
  • Thanks. The resultset is about 50 columns and 500K rows. I don't know if the tables are indexed. – J.E.Y Nov 08 '21 at 13:34

0 Answers0