Diagnosing and Fixing GORM N+1 Queries in Grails Applications

Recognizable Condition

When iterating over a collection of domain instances, the Hibernate log shows many identical SELECT statements for the same association (e.g., SELECT ... FROM book WHERE author_id = ?) – one query per row instead of a single joined fetch.

Cause / Diagnostic Table

Symptom	Likely Cause
Repeated SELECT for same association while looping over a collection	Default lazy fetching on many‑to‑one or one‑to‑many associations without a fetch plan
High query count relative to collection size	Missing batch size or join‑fetch hint

Ordered Checks

1. Enable Hibernate statistics

   Edit grails-app/conf/application.yml (or application.groovy) and add:

   hibernate:
     generate_statistics: true
   

   Restart the application (requires file‑system write permission).

   Run the suspect code (e.g., via a unit test or grails console) and watch the console for the line Hibernate: select .... If the number of executed SQL statements exceeds the size of the iterated collection, you have an N+1 problem.

   Risk: Enabling statistics adds a small runtime overhead; disable it after diagnosis.

2. Inspect the domain mapping

   Open the domain class that owns the association (e.g., Author with a Set<Book> books property). Look for:

   • @BatchSize annotation
   • @Fetch(FetchType.SUBSELECT) or @Fetch(FetchType.JOIN)
   • Explicit eager mapping (static mapping = { books fetch: 'join' })

   If none of these are present, the association uses the default lazy select, which causes N+1.

Fixes Tied to Findings

• Add a batch size – suitable when the association is reasonably sized and you want to keep lazy loading:

  class Author {
      static hasMany = [books: Book]
      static mapping = {
          books batchSize: 20
      }
  }
  

  Or, for Grails 3.x where @BatchSize may not be honored, add to grails-app/conf/hibernate.cfg.xml:

  <property name=\"hibernate.default_batch_fetch_size\">20</property>
  

• Use join fetch in HQL/Criteria – when you control the query:

  Author.executeQuery(\"select a from Author a left join fetch a.books where a.id = :id\", [id: 1])
  

• Consider eager fetching for small, static tables – only if the associated table is tiny and rarely changes:

  static mapping = {
      books fetch: 'join'
  }
  

  Limitation: Eager loading can increase memory usage and cause unnecessary data transfer for large datasets.

Escalation Criteria

• If adding batch size or join fetch does not reduce the query count (still > collection size), enable org.hibernate.type.descriptor.sql.BasicBinder trace to see parameter values and verify that the same association is being fetched repeatedly.
• When the N+1 pattern appears across many services, consider configuring a global default batch size in hibernate.cfg.xml or reviewing the second‑level cache strategy.
• If performance remains unacceptable after these changes, profile with a tool such as VisualVM or Java Flight Recorder to identify whether the issue is query execution time versus network latency.

Verification

1. After applying a fix, repeat the test from Check 1 and confirm that the total number of SQL statements approximates the collection size (e.g., 1‑2 queries for a collection of 20 books).
2. Check the log level: set org.hibernate.SQL=debug in logback.groovy or log4j.properties and verify that the expected join‑fetch or batch‑select statements appear.
3. Optional: run grails console and execute:

   import grails.console.HibernateUtil
   boolean statsEnabled = HibernateUtil.sessionFactory.getStatistics().isStatisticsEnabled()
   println \"Statistics enabled: $statsEnabled\"
   

   Ensure it returns true only while statistics are intentionally enabled.

Limitations

• Grails 3.x GORM may ignore @BatchSize on domain fields; rely on XML configuration instead.
• Eager fetching can lead to large result sets and increased heap consumption; test with realistic data volumes.
• Batch size tuning is workload‑dependent; start with a value like 10‑20 and adjust based on observed query counts.