0

When trying to optimize MySQ slow queries generated by Hibernate 4.2 in a legacy project, I found out that the code below generates nearly 500 SQL queries (with many duplicates) :

class MyDAO {
    public List<Message> findMessages() {
        Session session = MyHibernateUtils.openSession();

        String queryStr = "SELECT DISTINCT m FROM Message m "
                + " LEFT JOIN fetch m.types types "
                + " LEFT JOIN fetch m.mainType mainType "
                + " LEFT JOIN fetch m.place place "
                + " LEFT JOIN fetch m.building building "
                + " LEFT JOIN fetch m.city city "
                + " LEFT JOIN fetch m.kind kind "
                + " LEFT JOIN fetch m.domain domain "
                + " LEFT JOIN fetch m.action action "
                + " LEFT JOIN fetch m.customParameterA customParameterA "
                + " LEFT JOIN fetch m.customParameterB customParameterB "
                + " LEFT JOIN fetch m.scheduleEvents scheduleEvents "
                + " LEFT JOIN fetch m.comments comments "
                + " LEFT JOIN fetch m.messageLastActivities messageLastActivities "
                + " LEFT JOIN fetch m.customListA customListA "
                + " LEFT JOIN fetch m.childEvents childEvents "
                + " LEFT JOIN fetch m.parentEvent parentEvent "
                + " WHERE ...";

        List<Message> messages;
        try {
            session.getTransaction().begin();

            Query query = session.createQuery(queryStr);
            query.setTimeout(10);

            messages = query.list();
            session.getTransaction().commit();
        } catch (RuntimeException e) {
            session.getTransaction().rollback();
            throw e;
        } finally {
            session.close();
        }

        return messages;
    }
}

How can I avoid having so many SQL queries ?

I don't know if it helps but there are many onyToMany and manyToMany relationships between the entities.

Thank for your help.

Romain Ciaccafava
  • 114
  • 8
  • ORM frameworks most off the time works that way... they need to fire a extra `SELECT * FROM table WHERE id = 1`to find the related entiy – Raymond Nijland Oct 06 '17 at 15:34
  • 1
    @RaymondNijland No they don't. Well, in the worst case scenario they do that, but that's avoidable most of the time and certainly with Hibernate. Only a very naive ORM would do that, as it would kill performance and make the framework pretty much worthless. – Kayaman Oct 06 '17 at 15:39
  • As for the OP, enable SQL logging for Hibernate (look around, I don't remember how it's done off the top of my head) and see *what* queries are executed. That'll allow you to pin point the cause, which might be just a single N+1 query that you need to fix. – Kayaman Oct 06 '17 at 15:40
  • true @Kayaman .. – Raymond Nijland Oct 06 '17 at 15:59
  • @Kayaman yep they do and raymond is right. Eagar fetching usually requires additional actions (like adding annotations or joins) – Antoniossss Oct 06 '17 at 16:02
  • Double check if those extra queries comes from code you have shown us. – Antoniossss Oct 06 '17 at 16:04
  • @Antoniossss double checked, it is. – Romain Ciaccafava Oct 06 '17 at 16:13
  • I think u are wrong, but ok, wrap messages = query.list(); with some START-END log messages, turn on SQL logging and show us the output. – Antoniossss Oct 06 '17 at 16:17
  • @Antoniossss Eager fetching requires joins and more complex logic, but provides better performance. He's already using it through `LEFT JOIN FETCH` (but perhaps missing a lazily initialized collection). – Kayaman Oct 08 '17 at 09:16
  • @Antoniossss thank you for your help :). That's what I did, trust me I'm not wrong about it. Sorry I can't show you the output, this is a proprietary software I'm not allowed to do this. – Romain Ciaccafava Oct 10 '17 at 08:09
  • You could always anonymise logs, nothing wrong about that. But its your choice. As for now, without seeing actual mapping, I see no reason why hibernate would generate more than 1 query, especially that you dont event want to show us what kind of queries it is generating. – Antoniossss Oct 10 '17 at 08:13
  • @RomainCiaccafava and as for proprietary software, you have already give away part of your domain model, I dont see why prepared statements should be more protected than that. – Antoniossss Oct 10 '17 at 08:17

2 Answers2

2

You should check the queries hibernate is generating, to see which table is accessed frequently.

You have to join fetch entities related by your related entities as well, See here:

Hibernate is doing multiple select requests instead one (using join fetch)

I personally prefer lazy loading with an annotated @BatchSize() to keep the lazy-query-count small. Just using a Batch-Size of 2 will cut your query count in half then.

Also have a look at the @Cache Annotation which can reduce your query count in a significant way. (Just thinking of all the almost static stuff like city/building/type/domain and the like)

dognose
  • 20,360
  • 9
  • 61
  • 107
0

Depending on your relationship design, default value of Fetch in @OneToMany and @ManyToMany is LAZY, that means for loading related record in child entity (when you call getter method) hibernate executes one more query to load that record (for example: select * from foo where id = ?) , so if loaded entity (main entity) contains many child entities such as ManyToMany or OneToMany you will see many queries in console. To void these queries, you can set Fetch to EAGER but this is not recommended on optimization. 

@Entity
public class MainEntity {

@ManyToMany(Fetch = FetchType.EAGER)
public List<Foo> foos;

}
Moodi
  • 125
  • 9