how to optimize an HQL query that join two big tables

Question

i have a HQL query like this one :

Select foo
From Foo foo, Bar bar
Where foo.number = bar.number

but it takes hibernate 4, 5 seconds to execute because the two tables are very big ones. and i did a tracing with Hibernate stat and trace log and found that this HQL query execute 500 Select! that why it takes lot of time because i have 3 manyToOne in the first table and 7 ManyToOne & OneToMany in the other one. is there a way to optimize this HQL query without using or converting it to SQL query?

in case of using native SQL query, i need to remap the results from objects to a foo entity object witch i don't prefer.

How many rows are you retrieving? Are you applying any filtering condition? Otherwise I'll be slow. — The Impaler, Jun 06 '18 at 19:09
You are fetching all the data from your two BIG tables (possibly more data because of possible additional relationships). Definitely it will be slow. — naren, Jun 07 '18 at 05:32
yes, i did a tracing with Hibernate stat and trace log and found that this HQL query executes 500 Select! that why it takes a lot of time because i have 3 manyToOne in the first table and 7 ManyToOne & OneToMany in the other one. do you know how to do all this select in a single one? or any other solution that makes the HQL execute in a reasonable time less than 1 sec? i did try adding an Inner Fetch Join to get a single parent object and child and not all the child object but it still executes the 500 Select! — xfocus99, Jun 10 '18 at 09:48

score 1 · Answer 1 · answered Jun 20 '18 at 07:12

You have to use following annotations:

 @OneToMany(fetch = FetchType.LAZY, mappedBy = "your_mapping_name")
        @Cascade(CascadeType.ALL)
        @Fetch(FetchMode.SELECT)
            @BatchSize(size = 10)

for more info you can review: https://www.mkyong.com/hibernate/hibernate-fetching-strategies-examples/

score 0 · Answer 2 · answered Jun 19 '18 at 17:11

Without the entities code is harder to find the exactly answer for that, but most of the times the problem is the EAGER relationships.

i have 3 manyToOne in the first table and 7 ManyToOne & OneToMany in the other one.

The first step is be sure that all ToOne mappings are LAZY and not EAGER. Every ToOne (@ManyToOne and @OneToOne) relationship is EAGER by default. So, change the relationship to LAZY:

@ManyToOne(fetch = FetchType.LAZY)

Or:

@OneToOne(fetch = FetchType.LAZY)

Be also sure if the ToMany relationships are not EAGER (they are LAZY by default).

how to optimize an HQL query that join two big tables

2 Answers2