Lately I have been working on a custom written web solution using Grails MVC framework, that's stacked on top of Java / Groovy, and is parallel to RoR. Since I've started working with Grails and it's object-relational mapper GORM that's stacked on top of Hibernate, as someone that comes with no in-depth Hibernate knowledge I came up with following problem:
I had to create many-to-many mapping between entities A and B, let's called them Developer and Project, and wanted to lazy load developers when needed but, using sql JOIN statement, rather than reading one-by-one which was database-intensive. Examine following definitions:
Obviously, there is many to many relationship defined between Developer and Project domain definition, and by Grails official documentation, many to many associations need to have owning end of definition, Project in this case. Owning end means that all saves, and updates to Project will cascade to developer.
Database schema is created using following statements (taken directly from mysql log)
Consider following code and log output when inserting values, if Grails is configured to log sql using dataSource.logSql setting:
It simply inserts values into mapped domain object tables, and mapper table. Since repeated rows are ignored in log, there isn't 3 statements for 2nd and 3rd row - there actually present in real mysql log:
And now, what happens with GORM mapping by default if we try to pull all developers that worked on a project. See piece of code below
- and sql log that follows it
Obviously, there is n+1 query executed for project with n developers, which is database-intensive even for projects with small number of developers, since main thread will be constantly put in I/O wait, waiting for database reply, and database will process larger number of queries, so MySQL server CPU is unnecessary utilized. Simple idea for anyone with some knowledge of SQL would be to perform a JOIN instead of row-by-row querying.
Techniques for loading more than one row at a time in GORM, by Grails documentation are
- Setting lazy loading to false (pulling all date at once), since GORM mapping is lazy loading associations by default
- Specifying 'join' fetch
Trying to set lazy loading to false on Project domain, simple does not work, and gives us same sql log:
If setting fetching associations using JOIN statement, only one developer is pulled out, since reading project statement is merged with reading developers statement.
sql log (directly from MySQL log file):
Obviously, when reading entity by findBy, it's translated to 'limit 1' sql statement, and thus limits reading of project developers to only one. Even if reading statement changed to reading project directly, so there's no merging of two Hibernate statements, this approach won't make things better, since every developer is pulled out of database in it's own query, though it will give correct (read ALL) results
MySQL LOG file:
Still, large number of queries, for simple result. Analyzing all of above examples implies that mapper table 'projects_developers' is actually what's treated as 'developers' association in Project domain, in the key to achieving the goal is to set join behavior on OWNED end of an association, that is on Developer domain class:
And following SQL log
As you can see only 2 sql queries are executed - one to pull out project, and second to pull out all of the project's developers, and no limit is implied.
It is lengthy post, but hope that this will save someone's time, as I spent quite some time understanding GORM and it's gotchas. For any furhter tweaking like CASCADE constraints on MySQL, pagination parameters, etc, please refer to official GORM documentation.