Saturday, June 30, 2012

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.

7 comments:

  1. Replies
    1. My ex-boyfriend dumped me one week ago after I accused him of seeing someone else and insulting him. I want him back in my life but he refuse to have any contact with me. I was so confuse and don't know what to do, so I reach to the Internet for help and I saw a testimony of how a spell caster help them to get their ex back so I contact the spell caster and explain my problem to him and he cast a spell for me and assure me of 2 days that my ex will return to me and to my greatest surprise the third day my ex came knocking on my door and beg for forgiveness. I am so happy that my love is back again and not only that, we are about to get married. Once again thank you Dr Ojoka. You are truly talented and gifted. Email; dr.ojokspelltemple@gmail.com or Whats App +2348144172934 is the only answer. He can be of great help and I will not stop publishing him because he is a wonderful man 







































      My ex-boyfriend dumped me one week ago after I accused him of seeing someone else and insulting him. I want him back in my life but he refuse to have any contact with me. I was so confuse and don't know what to do, so I reach to the Internet for help and I saw a testimony of how a spell caster help them to get their ex back so I contact the spell caster and explain my problem to him and he cast a spell for me and assure me of 2 days that my ex will return to me and to my greatest surprise the third day my ex came knocking on my door and beg for forgiveness. I am so happy that my love is back again and not only that, we are about to get married. Once again thank you Dr Ojoka. You are truly talented and gifted. Email; dr.ojokspelltemple@gmail.com or Whats App +2348144172934 is the only answer. He can be of great help and I will not stop publishing him because he is a wonderful man 

      Delete
  2. Do you know how to get the current number of selects when using H2 or Postgres?

    ReplyDelete
    Replies
    1. Sorry, I don't. I am only really familiar with MySQL which is why I used it to illustrate the example. If you find out, please fill us in. For a real testing scenario H2 is probably required.

      Delete
  3. Hello, i am from USA, I want to share this great testimony about how Dr.Agbazara helped me bring back my ex lover, During my search for solution i came in contact with Dr.Agbazara details and through his help my lover came back to me within 48 hours. So with these i am so bold to advise anyone seeking for a way to get there lover back to contact Dr.Agbazara on WhatsApp: { +234 810 410 2662 } or via email at: { agbazara@gmail. com } I am so happy at least myself and my lover are back to each other again and going to spend the New Year celebration together Thank to Dr.Agbazara once again....

    ReplyDelete