— Disclaimer —
This optimization requires more manual work and is error prone but isn’t this with most (big) performance improvements?
For it to really work you have to structure your code accordingly and flush explicitly.
—
Recently in our performance measurements of a medium sized Grails project we noticed a strange behavior: every time we executed the same query the time it took increased. It started with 40ms and every time it took 1 ms more. The query was simple like Child.findAllByParent(parent)
The first thought: indexes! We looked at the database (a postgresql db) and we had indexes on the parent column.
Next: maybe the session cache got too large. But session.flush() and session.clear() did not solve that problem.
Another post suggested using a HQL query. Changing to
Child.executeQuery("select new Child(c.name, c.parent) from Child c where parent=:parent", [parent: parent])
had no effect.
Finally after countless more attempts we tried:
session.setFlushMode(FlushMode.COMMIT)
And not even the query executed in constant time it was also 10x faster?!
Hmmm…why?
The default flush mode in Grails is set to AUTO
Which means that before every query made the session is flushed. Every query regardless of the classes effected. The problem is known for hibernate but after 4! years it is still unresolved.
So my question here is: why did Grails chose AUTO as default?
Schneide Blog 
Safety first! If you don’t flush before you query, any changes you’ve made _in that session_ are not materialized in the DB, and your query will not reflect recent changes. Of course, you’d hope that Hibernate would be smart enough to figure out if your query would touch any of the changed (and unflushed objects) and do it automagically… I guess this is the point of your article.
Yes, you are right! For it to really work you have to structure your code accordingly and flush explicitly. Which means more manual work. Methods like get(id) use the session cache but queries do not.
Nice finding. You can change the default flush mode by setting it in DataSource.groovy:
hibernate { flush.mode='commit' }We are using “manual” for big websites and flush manually when something has to be updated: domainobject.save(flush:true).
Here is another Hibernate query performance related thing:
https://forum.hibernate.org/viewtopic.php?p=2404651
http://docs.jboss.org/hibernate/entitymanager/3.5/reference/en/html/objectstate.html#d0e1215
It’s not possible to set the “org.hibernate.readOnly” query hint for Grails Hibernate queries.