GORM - False Sense of Simplicity

While maintaining some code recently, I came across what seemed to be some particularly evil code.  It was a service method that used a Grails Criteria Query to fish out some objects from the DB.  These were then converted to Data Transfer Objects (DTO) to be serialized for use in a GWT client.  It went something like :

def myObjects = Domain.findAllBySomething(thing)
....
....
....
myObjects.each{obj ->
  def dto = new Dto()
  dto.property = obj.innerDomain.property
  dto.propertyTwo = obj.innerDomain.anotherDomain.property
  dtos << dto
}

It seems fairly innocent but try telling that the database.  Each call on any of the properties within obj that are themselves domains resulted in another database call.  Creating the DTO above resulted in about 3 queries just to set two properties.  This might not sound too bad, but imagine if myObjects contains 1,000 or so objects.... not the best strategy.  The actual code I was maintaining was typically returning about 1,000 results and this resulted in about 13,000 queries running on the database.  This was a user driven event so it could be happening quite a lot.  This was obviously terrible for performance.  Of course there is an argument to use paging and caching of results etc. but that's a different blog post.  Long story short, a colleague (who I won't name for social reasons) and I were able to get the 13,000 queries down to 25 for the entire service method by just configuring the query correctly.

The main point here is the difference between eager and lazy fetching.  A long time ago, Grails' default fetching strategy was eager.  This basically meant that when you queried for domains, using dynamic finders or criteria, your object model would return fully populated.  Unfortunately this could also result in you returning the entire database in one query.  Not good.

Around about Grails 1.0.4,  the default fetching strategy was changed to lazy.  This meant that your object model returned partially populated, that is, proxy objects with only their id's, were returned for properties that were themselves domains.  When you called for a property on one of these proxies, Grails would request it from the database.  All this happened pretty transparently to the user.

Grails provides many ways of forcing earger fetching.  It can be done in the domain mappings and in the domain itself by setting the fetch mode.  It can also be done at a query level which I am more in favour of.  Setting on the domain itself is too much of a commitment in my opinion as you can never imagine all use cases at time of coding.  I will illustrate setting it in dynamic finders with the following domains :

class Customer {

  String name
  Account account

  static constraints = {
    account unique: true, nullable: false, blank: false
    name nullable: false, blank: false
  }
}

class Account {
  static belongsTo = [owner: Customer]

  Long accountNumber

  static constraints = {
    accountNumber min: 0l, nullable: false, unique: true
  }
}

class BankingTransaction {

  Long transactionNumber
  BigDecimal amount
  Account fromAccount
  Account toAccount

  static constraints = {
    transactionNumber min: 0l, unique: true, nullable: false
    fromAccount nullable: false, validator: {Account thisFromAccount, BankingTransaction thisTransaction ->
      if (thisFromAccount?.accountNumber == thisTransaction?.toAccount?.accountNumber) {
        return false//cannot have a transaction to the same account... probably
      }
    }
    toAccount nullable: false
    amount nullable: false
  }

  BankingTransactionDTO toBankingTransactionDTO() {
    def dto = new BankingTransactionDTO()
    dto.with {//probably better ways of doing this but this illustrated the example
      transactionNumber = this.transactionNumber
      amount = this.amount
      fromCustomersName = fromAccount.owner.name
      toCustomersName = toAccount.owner.name
    }
    return dto
  }

}

So to illustrate the lazy and eager fetching I wrote some tests that were bootstrapped on a MySQL database.  First we need to set up some test data and an additional Grooy Sql connection to run some status queries on our DB.

class BankingTransactionTests {

  Sql sql
  SessionFactory sessionFactory
  BankingTransactionService bankingTransactionService

  @Before
  void setUp() {
    BankingTransaction.withNewSession {session ->
      100.times {num ->
        new Customer(name: "Customer ${num}", account: new Account(accountNumber: num)).save(failOnError: true)
      }
    }

    BankingTransaction.withNewSession {session ->
      final allCustomers = Customer.list()
      def generousCustomer = allCustomers[0]
      allCustomers[1..-1].eachWithIndex {customer, index ->
        new BankingTransaction(fromAccount: generousCustomer.account, toAccount: customer.account, transactionNumber: index, amount: index * 100).save(failOnError: true)
      }
    }
    sql = new Sql(sessionFactory.currentSession.connection())
    sql.metaClass.getNumberOfSelects = {->
      return delegate.rows('SHOW STATUS LIKE "Com_select"')[0].Value as Long
    }
  }

....
}

I won't go into the details, it is just an integration test, the code is on GitHub anyway for the forever curious.  So first off lets do a simple test that queries for all BankingTransactions.

  @Test
  void testSimpleQuery() {
    //get the current number of selects in this session
    def numberOfQueriesBeforeTest = sql.numberOfSelects

    //carry out the query
    final transaction = BankingTransaction.list()//1 query

    //now calculate the number of queries
    def numberOfQueriesAfterTest = sql.numberOfSelects
    final numberOfSelects = numberOfQueriesAfterTest - numberOfQueriesBeforeTest

    //is it what we expect??
    assert numberOfSelects == 1
  }

We run the test, and huzzah, test passes. As we would expect BankingTransaction.list() is a simple select query.

OK, now lets access some properties on the first BankingTransaction

  @Test
  void testSimplePropertyAccess() {
    //get the current number of selects in this session
    def numberOfQueriesBeforeTest = sql.numberOfSelects

    //carry out the query
    final transaction = BankingTransaction.list()[0]
    def fromAccountNumber = transaction.fromAccount.accountNumber
    def fromAccountCustomerName = transaction.fromAccount.owner.name

    //now calculate the number of queries
    def numberOfQueriesAfterTest = sql.numberOfSelects
    final numberOfSelects = numberOfQueriesAfterTest - numberOfQueriesBeforeTest

    //is it what we expect??
    assert numberOfSelects == 1
  }

Uh oh, test fails with :

Assertion failed: 

assert numberOfSelects == 1
       |               |
       3               false

Looking closely at the test we can see the three queries, the first is the the call the list(), the second is the call to fromAccount and the third, the call to fromAccount.owner.

OK, so, lets fix the query by setting the fetch mode to join in the query.

  @Test
  void testSimplePropertyAccess() {
    //get the current number of selects in this session
    def numberOfQueriesBeforeTest = sql.numberOfSelects

    def queryParams = [fetch: [fromAccount: 'join', 'fromAccount.owner': 'join']]
    //carry out the query
    final transaction = BankingTransaction.list(queryParams)[0]
    def fromAccountNumber = transaction.fromAccount.accountNumber
    def fromAccountCustomerName = transaction.fromAccount.owner.name

    //now calculate the number of queries
    def numberOfQueriesAfterTest = sql.numberOfSelects
    final numberOfSelects = numberOfQueriesAfterTest - numberOfQueriesBeforeTest

    //is it what we expect??
    assert numberOfSelects == 1
  }

Test passes! Sweet! This is because we told GORM to fetch the associated domains using a join. I suppose we should look at a more real work example. Suppose we have a BankingTransactionService with the following method :

class BankingTransactionService {

  Collection retrieveTransactions() {
    def transactions = BankingTransaction.list()
    return transactions.collect {it.toBankingTransactionDTO()}
  }

}

With the following test :

  @Test
  void testBankingService() {
    //get the current number of selects in this session
    def numberOfQueriesBeforeTest = sql.numberOfSelects

    //carry out the query
    def dtos = bankingTransactionService.retrieveTransactions()

    //now calculate the number of queries
    def numberOfQueriesAfterTest = sql.numberOfSelects
    final numberOfSelects = numberOfQueriesAfterTest - numberOfQueriesBeforeTest

    //is it what we expect??
    assert numberOfSelects == 1
  }

We run the test, and eh... not looking good :

Assertion failed: 

assert numberOfSelects == 1
       |               |
       201             false

Hmmm 201 queries, this will not please the performance junkies at all. What is happening in toBankingTransactionDTO() is that as the objects are created a query happens each time a property that is a domain is accessed. These slowly build up in the first level cache reducing the amount of queries, but I think we can do better.

Let's go all out and reduce the number of queries to 1 for this service method.  It's gonna be a long road.... not really :

  Collection retrieveTransactions() {
    final queryParams = [fetch: [
            fromAccount: 'join',
            toAccount: 'join',
            'toAccount.owner': 'join',
            'fromAccount.owner': 'join']
    ]
    def transactions = BankingTransaction.list(queryParams)
    return transactions.collect {it.toBankingTransactionDTO()}
  }

Test passes. There we go, 201 queries to 1. Not really that much work, just don't take the simplicity provided by GORM for granted or you will have some angry DBA's on your tail... you have been warned!

Slán

Code on GitHub.