How did you optimize slow SQL queries? How did you solve the N+1 problem in Hibernate?

The following approaches are usually used to optimize slow SQL queries:

• Analyzing the execution plan of the query (EXPLAIN) to identify bottlenecks.
• Adding or adjusting indexes to speed up searches.
• Rewriting queries to reduce the number of joins or subqueries.
• Using pagination and selecting only necessary fields.
• Caching query results if the data does not change frequently.

To solve the N+1 problem in Hibernate, the following are used:

• Using eager loading (fetch join) with HQL or Criteria API to load related entities immediately.
• Configuring @Fetch(FetchMode.JOIN) annotations or @EntityGraph for load optimization.
• Using batch fetching — loading related collections in batches.

Example with fetch join:

String hql = "SELECT p FROM Parent p JOIN FETCH p.children WHERE p.id = :id";
Parent parent = session.createQuery(hql, Parent.class)
                       .setParameter("id", parentId)
                       .uniqueResult();

This allows loading the parent and its children with a single query, avoiding N additional queries for each child entity.