0

Suppose I have a Hibernate-generated large query that comes out with CROSS JOINs which is impacting performance:

SELECT studyt0_.abbreviation                                AS col_0_0_,
       userst2_.user_name                                   AS col_1_0_,
       recallst3_.id                                        AS col_2_0_,
       recallst3_.recall_date                               AS col_3_0_,
       recallst3_.created_date                              AS col_4_0_,
       (SELECT lookupt8_.description
        FROM   PUBLIC.answers_t answerst5_
               CROSS JOIN PUBLIC.activity_questions_t activityqu6_
               CROSS JOIN PUBLIC.activities_t activities7_
               CROSS JOIN PUBLIC.lookup_t lookupt8_
        WHERE  lookupt8_.id = activities7_.activity_category_id
               AND activities7_.id = activityqu6_.activity_id
               AND activityqu6_.id = answerst5_.activity_question_id
               AND activityqu6_.question_id = 1
               AND answerst5_.event_id = eventst4_.id)      AS col_5_0_,
       (SELECT activities11_.activity_title
        FROM   PUBLIC.answers_t answerst9_
               CROSS JOIN PUBLIC.activity_questions_t activityqu10_
               CROSS JOIN PUBLIC.activities_t activities11_
        etc

This is due to implicit joins we have in our HQL, as I understand. 

(select l.description from AnswersT ans, ActivityQuestionsT aq, ActivitiesT a, LookupT l " + 
            "where l.id=a.lookupT.id and a.id=aq.activitiesT.id and aq.id=ans.activityQuestionsT.id and aq.questionsT.id=1 and ans.eventsT.id=e.id) as category, " +

Question: Is there a way to quickly get rid of the CROSS JOINs and replace them with INNER JOINs without rewriting the entire query, which is very large? Is there a config workaround of some kind?

gene b.
  • 10,512
  • 21
  • 115
  • 227
  • If you want inner joins then by definition you need to provide the `ON` clauses. If you're asking whether Hibernate can 'guess' them for you, then the answer is that it's not that powerful, I'm afraid – crizzis Oct 23 '19 at 19:57
  • 1
    Also, did you measure the actual performance penalty of using cross join vs inner joins in your query? See [this question](https://stackoverflow.com/questions/670980/performance-of-inner-join-compared-to-cross-join) for a discussion on cross join performance. In essence, decent query optimizers don't care which join syntax you use – crizzis Oct 23 '19 at 20:01
  • Yes you're right. After removing the cross joins the performance didn't improve. – gene b. Oct 23 '19 at 22:58

1 Answers1

1

I see here 2 things that can be optimized.

1) The approach (select A from ...) as nameA, (select B from ...) as nameB, ... leads to a huge cross join: Every value in the 1st column will be combined with every value in the 2nd column etc. To me it looks like a bug. Normally there should be only one from clause, something like select A as nameA, select B as nameB, ... from ....

2) Independent on this, check your where clause. The current condition

where l.id=a.lookupT.id and a.id=aq.activitiesT.id and aq.id=ans.activityQuestionsT.id
  and aq.questionsT.id=1 and ans.eventsT.id=e.id

can be replaced with a simpler one:

where ans.activityQuestionsT.questionsT.id = 1
mentallurg
  • 4,967
  • 5
  • 28
  • 36
  • It has to be like that because the nested `(select A from..)` are for separate **columns**. The report needs to present some data as columns. That's why there are Inner selects. – gene b. Oct 23 '19 at 23:34
  • This would be a strange report because it will combine all possible values. If `select` for the first column finds 2 values A and B, and if `select` for the 2nd colum finds 3 values 1000, 2000 and 5000, then your report will contain 6 rows (not 2, not 3): One row `A 1000`, next row `A 2000`, then `A 5000`, then `B 1000`, 'B 2000', `B 5000`. Another example: If you have 6 columns and each has 10 values, your report will contain 10 x 10 x 10 x 10 x 10 x 10 = 1 000 000 rows. Are you sure that you report should contain all possible independent combinations of all possible values? – mentallurg Oct 24 '19 at 05:37